A general F# SQL database erasing type provider, supporting LINQ queries, schema exploration, individuals, CRUD operations and much more besides. - fsprojects/SQLProvider

I wrote some queries in C# using LINQ. After a while, I started using Haskell a little bit, which is a functional programming language (a not so popular one), and for me it seems that both of them ...

Calling DB Functions in EF Core LINQ Queries

Learn about the 2 powerful libraries for dynamic processing of LINQ queries and C# code at runtime. Dynamic LINQ and C# Eval Expression.

# The N+1 Query Problem: Why Your LINQ Query Makes 1001 Database Calls Your query returns 1000 orders. Your database log shows 1001 queries. One to fetch the orders, then one for each order's customer. This is the N+1 problem — and it's probably happening in your codebase right now. ## The Innocent-Looking Code var orders = dbContext.Orders.ToList(); foreach (var order in orders) { Console.WriteLine($"Order {order.Id} by {order.Customer.Name}"); } What you expect: one query fetching orders with customer names. What happens: 1. `SELECT * FROM Orders` — fetches 1000 orders 2. `SELECT * FROM Customers WHERE Id = 1` — for order 1 3. `SELECT * FROM Customers WHERE Id = 2` — for order 2 4. ... 998 more queries ... If each query takes 5ms, you've turned a 10ms operation into a 5-second disaster. ## Why It Happens Entity Framework uses lazy loading by default (or used to — EF Core made it opt-in). When you access `order.Customer`, EF sees the navigation property isn't loaded and fires a query. The insidious part: it works perfectly in development with 10 records. In production with 10,000? Your API times out. ## The Solution: Eager Loading with Include var orders = dbContext.Orders .Include(o => o.Customer) // Load customers with orders .ToList(); foreach (var order in orders) { Console.WriteLine($"Order {order.Id} by {order.Customer.Name}"); } Now EF generates one query with a JOIN. Customer data comes with the order in a single round-trip. ## Nested Includes For deeper relationships: var orders = dbContext.Orders .Include(o => o.Customer) .Include(o => o.OrderItems) .ThenInclude(oi => oi.Product) // Nested: OrderItem's Product .ToList(); `ThenInclude` continues from the last included collection. You can chain them for complex graphs. ## The Projection Alternative `Include` loads entire entities. Often, you don't need all columns: // Instead of Include... var orders = dbContext.Orders .Include(o => o.Customer) .Include(o => o.OrderItems) .ToList(); // ...project to what you actually need var orders = dbContext.Orders .Select(o => new { o.Id, CustomerName = o.Customer.Name, ItemCount = o.OrderItems.Count, Total = o.OrderItems.Sum(oi => oi.Price) }) .ToList(); The projection approach generates one query that fetches exactly the columns needed. No N+1, minimal data transfer. Fun fact: The term "N+1" comes from the mathematical pattern: 1 query for the parent collection, plus N queries for each child. It was popularized by the Ruby on Rails community around 2006, but the problem existed anywhere ORMs did automatic lazy loading. The ActiveRecord pattern (from Martin Fowler's 2002 book) made it endemic. ## AsSplitQuery: When Include Gets Too Wide Wide includes (many navigation properties or deep nesting) produce massive JOINs. EF Core 5+ offers split queries: var orders = dbContext.Orders .Include(o => o.OrderItems) .Include(o => o.ShippingAddress) .Include(o => o.BillingAddress) .AsSplitQuery() // Separate queries instead of mega-JOIN .ToList(); `AsSplitQuery` fires multiple simple queries instead of one giant JOIN. For complex includes with many columns, split queries can actually be faster. Trade-off: multiple round-trips vs one huge result set. Profile to decide. ## Detecting N+1 in the Wild ### Option 1: EF Core Logging optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information); Watch the output. Multiple SELECT statements for navigation properties = N+1. ### Option 2: Simple Query Count int queryCount = 0; optionsBuilder.LogTo(msg => { if (msg.Contains("Executing DbCommand")) queryCount++; }, LogLevel.Information); If `queryCount` >> 1 for what should be one query, investigate. ### Option 3: Third-Party Tools MiniProfiler, Glimpse, or similar tools visualize queries per request. N+1 shows as a fan of identical query patterns. ## Common N+1 Patterns ### The Loop Print // Bad foreach (var order in dbContext.Orders) Console.WriteLine(order.Customer.Name); // N queries // Good foreach (var order in dbContext.Orders.Include(o => o.Customer)) Console.WriteLine(order.Customer.Name); // 1 query ### The Innocent Serialize // Bad — serializer triggers lazy loading return Json(dbContext.Orders.ToList()); // Triggers N+1 during serialization // Good return Json(dbContext.Orders .Select(o => new { o.Id, CustomerName = o.Customer.Name }) .ToList()); ### The Collection Count // Bad var orders = dbContext.Orders.ToList(); var withItems = orders.Where(o => o.OrderItems.Any()); // N queries // Good var orders = dbContext.Orders.Include(o => o.OrderItems).ToList(); var withItems = orders.Where(o => o.OrderItems.Any()); // 0 additional queries ## The Rule 1. **Accessing navigation in loops?** `Include` before iterating 2. **Don't need full entities?** Project to DTOs (avoids N+1 _and_ fetches less data) 3. **Wide includes?** Consider `AsSplitQuery` 4. **Serialize entities?** Project to anonymous/DTO first 5. **Always profile** — EF logging reveals the real query count Next time, we'll look at async LINQ — when to use `ToListAsync`, why you can't make LINQ operators truly async, and the Task patterns that work with database queries. Hope to see you!

Learn advanced LINQ operators in C# with practical examples. Explore GroupBy, SelectMany, Join, Distinct, and aggregation using a simple .NET console app.

Grouping data in Entity Framework Core (EF Core) used to feel a little… clunky. Sometimes, LINQ’s GroupBy() worked beautifully in-memory but got lost in translation when executing SQL queries. You’d w...

Entity Framework Core is not inherently slow. Most performance problems come from misunderstanding how LINQ translates into SQL, how data is materialized, and how queries behave under production scale...

Debug EF Core queries with ease using LINQPad. Visualize LINQ, explore data, and see generated SQL instantly for faster troubleshooting.

Hi, my name is Saif, and today I want to share something every C# developer should have in their toolkit — LINQ.

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations. - dotnet/efcore

Learn how to use LINQ in C# with practical, real-world examples. Understand Where, Select, OrderBy, FirstOrDefault and how to write clean, readable queries in .NET

The article explores the performance implications of using LINQ for collection queries, finding that a conventional foreach() loop outperforms LINQ by 1.75 times in identifying items matching a giv…

🚀 Enhanced Editable Expression in IEnumerable Visualizer! Now with inline Copilot chat, effortlessly generate and refine LINQ queries to filter your data. N...

LINQ Me Up helps you save time by converting SQL into LINQ or LINQ into SQL and generating LINQ from your dataset input. Supports C# and Visual Basic code and Method and Query syntax.