C# LINQ Examples on .NET 10: LeftJoin, CountBy and Pitfalls

ByRajni Kavathiya

Last updated: October 12, 2026 · Tested with .NET 10 (SDK 10.0.401, runtime 10.0.12), C# 14, EF Core 10.0.12 with SQLite, Windows 11

Short answer: LINQ is a set of extension methods in System.Linq for filtering, shaping, sorting, grouping and joining any IEnumerable<T>. Against a database (IQueryable<T>), EF Core turns the same methods into SQL. This post runs the everyday operators on one small data set and shows the real output. It also covers the operators added in .NET 6, 9 and 10 (LeftJoin and RightJoin are new in .NET 10), the exact exceptions LINQ throws, deferred execution, and the EF Core could not be translated error. Every block below was compiled and run on .NET 10, and the output is copied from the console.

The Sample Data

All examples use these two lists. ImplicitUsings (on by default in .NET 6+ templates) already imports System.Linq:

var products = new List<Product>
{
    new(1, "Laptop", "Electronics", 1200m, 5),
    new(2, "Mouse", "Electronics", 25m, 40),
    new(3, "Desk", "Furniture", 300m, 0),
    new(4, "Chair", "Furniture", 150m, 12),
    new(5, "Monitor", "Electronics", 400m, 8),
    new(6, "Notebook", "Stationery", 3m, 200),
};

var orders = new List<Order>
{
    new(101, 1, 2),
    new(102, 2, 5),
    new(103, 2, 1),
    new(104, 5, 1),
    new(105, 9, 3), // product 9 doesn't exist
};

public record Product(int Id, string Name, string Category, decimal Price, int Stock);
public record Order(int Id, int ProductId, int Quantity);

Prices are formatted with :C; I set CultureInfo.CurrentCulture = new("en-US") so the output below doesn’t depend on the machine.

Where, Select, OrderBy, GroupBy and Aggregates

These cover most LINQ in real code. Filter with Where, shape with Select, sort with OrderBy/ThenBy, bucket with GroupBy, then aggregate:

var inStock = products.Where(p => p.Stock > 0);
Console.WriteLine(string.Join(", ", inStock.Select(p => p.Name)));

var labels = products.Select(p => $"{p.Name} ({p.Price:C0})");
Console.WriteLine(string.Join(", ", labels));

var sorted = products.OrderBy(p => p.Category).ThenByDescending(p => p.Price);
Console.WriteLine(string.Join(", ", sorted.Select(p => $"{p.Category}/{p.Name}")));

foreach (var g in products.GroupBy(p => p.Category))
{
    Console.WriteLine($"{g.Key}: {g.Count()} items, stock value {g.Sum(p => p.Price * p.Stock):C0}");
}

Console.WriteLine($"Total stock: {products.Sum(p => p.Stock)}, average price: {products.Average(p => p.Price):C2}");
Console.WriteLine($"Any furniture under $200? {products.Any(p => p.Category == "Furniture" && p.Price < 200)}");
Console.WriteLine($"First over $1,000: {products.FirstOrDefault(p => p.Price > 1000m)?.Name ?? "none"}");
Console.WriteLine($"First over $5,000: {products.FirstOrDefault(p => p.Price > 5000m)?.Name ?? "none"}");
Laptop, Mouse, Chair, Monitor, Notebook
Laptop ($1,200), Mouse ($25), Desk ($300), Chair ($150), Monitor ($400), Notebook ($3)
Electronics/Laptop, Electronics/Monitor, Electronics/Mouse, Furniture/Desk, Furniture/Chair, Stationery/Notebook
Electronics: 3 items, stock value $10,200
Furniture: 2 items, stock value $1,800
Stationery: 1 items, stock value $600
Total stock: 265, average price: $346.33
Any furniture under $200? True
First over $1,000: Laptop
First over $5,000: none

Things to notice. ThenByDescending sorts inside each category, so within Electronics it’s Laptop, Monitor, Mouse. GroupBy keeps the order in which keys first appear. FirstOrDefault returns null for a reference type when nothing matches, so the ?. is needed. Any() stops at the first match, which makes it the right way to ask “is there at least one?” (not Count() > 0).

Query Syntax vs Method Syntax

The query syntax is translated by the compiler into the same method calls, so pick whichever reads better. Method syntax is what you’ll see in most codebases. Query syntax is easier to read for joins and let.

var cheapElectronics =
    from p in products
    where p.Category == "Electronics" && p.Price < 500m
    orderby p.Price
    select p.Name;

var same = products
    .Where(p => p.Category == "Electronics" && p.Price < 500m)
    .OrderBy(p => p.Price)
    .Select(p => p.Name);

Console.WriteLine(string.Join(", ", cheapElectronics));
Console.WriteLine(cheapElectronics.SequenceEqual(same));
Mouse, Monitor
True

Newer Operators: MaxBy, DistinctBy, Chunk (.NET 6) and CountBy, AggregateBy, Index (.NET 9)

Many older LINQ tutorials still write these by hand with GroupBy or OrderByDescending(...).First():

// .NET 6
Console.WriteLine($"MaxBy price: {products.MaxBy(p => p.Price)!.Name}");
Console.WriteLine($"DistinctBy category: {string.Join(", ", products.DistinctBy(p => p.Category).Select(p => p.Name))}");
foreach (var page in products.Chunk(4))
{
    Console.WriteLine($"Chunk: {string.Join(", ", page.Select(p => p.Name))}");
}

// .NET 9
foreach (var (category, count) in products.CountBy(p => p.Category))
{
    Console.WriteLine($"CountBy: {category} = {count}");
}
foreach (var (category, value) in products.AggregateBy(p => p.Category, 0m, (sum, p) => sum + p.Price * p.Stock))
{
    Console.WriteLine($"AggregateBy: {category} = {value:C0}");
}
foreach (var (i, p) in products.Take(2).Index())
{
    Console.WriteLine($"Index: {i} {p.Name}");
}
MaxBy price: Laptop
DistinctBy category: Laptop, Desk, Notebook
Chunk: Laptop, Mouse, Desk, Chair
Chunk: Monitor, Notebook
CountBy: Electronics = 3
CountBy: Furniture = 2
CountBy: Stationery = 1
AggregateBy: Electronics = $10,200
AggregateBy: Furniture = $1,800
AggregateBy: Stationery = $600
Index: 0 Laptop
Index: 1 Mouse
  • MaxBy/MinBy return the element, not the value. Max(p => p.Price) would return 1200; MaxBy returns the Laptop.
  • DistinctBy keeps the first element per key.
  • Chunk splits into arrays of at most N items, which is handy for batching API calls or inserts.
  • CountBy and AggregateBy return key/value pairs directly. They replace GroupBy(...).Select(g => new { g.Key, Count = g.Count() }) without building the groups.
  • Index gives you (index, item) tuples, so you no longer need Select((p, i) => ...).

LeftJoin and RightJoin in .NET 10

Before .NET 10, a left outer join in LINQ meant GroupJoin + SelectMany + DefaultIfEmpty, a pattern almost everyone had to look up. .NET 10 adds LeftJoin and RightJoin:

// Inner join: order 105 (unknown product) is dropped
var inner = orders.Join(products, o => o.ProductId, p => p.Id,
    (o, p) => $"{o.Id}:{p.Name}x{o.Quantity}");
Console.WriteLine("Join:      " + string.Join(", ", inner));

// .NET 10: LeftJoin keeps every order; the product is null when there is no match
var left = orders.LeftJoin(products, o => o.ProductId, p => p.Id,
    (o, p) => $"{o.Id}:{p?.Name ?? "?"}x{o.Quantity}");
Console.WriteLine("LeftJoin:  " + string.Join(", ", left));

// .NET 10: RightJoin keeps every product, even those never ordered
var right = orders.RightJoin(products, o => o.ProductId, p => p.Id,
    (o, p) => $"{p.Name}:{o?.Id.ToString() ?? "-"}");
Console.WriteLine("RightJoin: " + string.Join(", ", right));

// Before .NET 10: GroupJoin + SelectMany + DefaultIfEmpty
var oldLeft = orders
    .GroupJoin(products, o => o.ProductId, p => p.Id, (o, ps) => new { o, ps })
    .SelectMany(x => x.ps.DefaultIfEmpty(), (x, p) => $"{x.o.Id}:{p?.Name ?? "?"}x{x.o.Quantity}");
Console.WriteLine("Old left:  " + string.Join(", ", oldLeft));
Join:      101:Laptopx2, 102:Mousex5, 103:Mousex1, 104:Monitorx1
LeftJoin:  101:Laptopx2, 102:Mousex5, 103:Mousex1, 104:Monitorx1, 105:?x3
RightJoin: Laptop:101, Mouse:102, Mouse:103, Desk:-, Chair:-, Monitor:104, Notebook:-
Old left:  101:Laptopx2, 102:Mousex5, 103:Mousex1, 104:Monitorx1, 105:?x3

Order 105 points at a product that doesn’t exist. Join drops it; LeftJoin keeps it with a null product, giving the same result as the old three-method pattern. RightJoin keeps every product, including Desk, Chair and Notebook, which nobody ordered. The inner side of the result selector is nullable (p?.Name). Write p.Name instead and, with nullable reference types on, the build reports warning CS8602: Dereference of a possibly null reference. At runtime it would be a NullReferenceException on order 105. EF Core 10 translates LeftJoin to a SQL LEFT JOIN (shown in the EF section below).

Sequence Contains No Elements and Other LINQ Exceptions

These are the messages people search for. Here is each one with what triggers it:

var empty = new List<Product>();
Show("First on empty", () => empty.First());
Show("First no match", () => products.First(p => p.Price > 5000m));
Show("Single, 3 matches", () => products.Single(p => p.Category == "Electronics"));
Show("SingleOrDefault, 3", () => products.SingleOrDefault(p => p.Category == "Electronics"));
Show("Max on empty", () => empty.Max(p => p.Price));
Show("Average on empty", () => empty.Average(p => p.Price));
Show("Max on empty, nullable", () => empty.Max(p => (decimal?)p.Price));
Show("Sum int overflow", () => new[] { int.MaxValue, 1 }.Sum());
Show("ElementAt(10)", () => products.ElementAt(10));

static void Show(string label, Func<object?> action)
{
    try { Console.WriteLine($"{label,-24} {action() ?? "null"}"); }
    catch (Exception ex) { Console.WriteLine($"{label,-24} {ex.GetType().Name}: {ex.Message}"); }
}
First on empty           InvalidOperationException: Sequence contains no elements
First no match           InvalidOperationException: Sequence contains no matching element
Single, 3 matches        InvalidOperationException: Sequence contains more than one matching element
SingleOrDefault, 3       InvalidOperationException: Sequence contains more than one matching element
Max on empty             InvalidOperationException: Sequence contains no elements
Average on empty         InvalidOperationException: Sequence contains no elements
Max on empty, nullable   null
Sum int overflow         OverflowException: Arithmetic operation resulted in an overflow.
ElementAt(10)            ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')
  • Sequence contains no elements: First(), Single(), Last(), Max(), Min() or Average() on an empty sequence. With a predicate, First says no matching element instead. Use FirstOrDefault when “none” is a normal case.
  • Sequence contains more than one matching element: Single and also SingleOrDefault. The OrDefault only covers zero matches, not several. Use Single only when two matches really are a bug, such as a lookup by a unique key.
  • Max/Average on an empty list: cast the selector to a nullable type ((decimal?)p.Price) and you get null instead of an exception. On .NET 6+ you can also use DefaultIfEmpty() or MaxBy.
  • Sum of int is checked: it throws OverflowException instead of wrapping. Sum into long (Sum(x => (long)x)) for big totals.

Deferred Execution and Multiple Enumeration

A LINQ query over a collection is a recipe, not a result. Nothing runs until you enumerate it, and it runs again every time you do. A counter in the predicate makes that visible:

int calls = 0;
var expensive = products.Where(p => { calls++; return p.Price > 100m; });

Console.WriteLine($"After defining the query: {calls} calls");
Console.WriteLine($"Count: {expensive.Count()}, calls so far: {calls}");
Console.WriteLine($"Any: {expensive.Any()}, calls so far: {calls}");
Console.WriteLine($"Names: {string.Join(", ", expensive.Select(p => p.Name))}, calls so far: {calls}");

calls = 0;
var materialized = expensive.ToList();
Console.WriteLine($"ToList: {calls} calls; materialized.Count afterwards: {materialized.Count}, calls still {calls}");

products.Add(new Product(7, "Webcam", "Electronics", 120m, 9));
Console.WriteLine($"Query sees the new item: {expensive.Count()}, the list doesn't: {materialized.Count}");
After defining the query: 0 calls
Count: 4, calls so far: 6
Any: True, calls so far: 7
Names: Laptop, Desk, Chair, Monitor, calls so far: 13
ToList: 6 calls; materialized.Count afterwards: 4, calls still 6
Query sees the new item: 5, the list doesn't: 4

Defining the query cost 0 calls. Count() ran the predicate over all 6 products. Any() ran it once more (it stopped at the Laptop). Building the names ran all 6 again. So three uses of one query meant 13 predicate calls. After ToList() the results are fixed: no more calls, and the list doesn’t see the Webcam added later, while the query does. If a query is expensive (a database call, a regex, an HTTP request inside Select) and you use the result more than once, materialize it with ToList() or ToArray(). Rider flags this. So does the .NET analyzer once you turn it on with dotnet_diagnostic.CA1851.severity = warning in .editorconfig: on this code it reported warning CA1851: Possible multiple enumerations of ‘IEnumerable’ collection three times.

Collection was modified; enumeration operation may not execute

The deferred query is still reading the list while you change it:

try
{
    foreach (var p in products.Where(p => p.Stock == 0))
    {
        products.Remove(p);
    }
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

products.RemoveAll(p => p.Stock == 0); // or: foreach over .ToList()
Console.WriteLine($"Out of stock left: {products.Count(p => p.Stock == 0)}");
InvalidOperationException: Collection was modified; enumeration operation may not execute.
Out of stock left: 0

Use List<T>.RemoveAll with a predicate, or loop over a copy (foreach (var p in products.Where(...).ToList())).

LINQ with EF Core: IQueryable, ToQueryString and “Could Not Be Translated”

On a DbSet, the same LINQ methods build an expression tree that EF Core turns into SQL. ToQueryString() shows the SQL without running it. The full program (EF Core 10.0.12, SQLite):

using Microsoft.EntityFrameworkCore;

System.Globalization.CultureInfo.CurrentCulture = new("en-US");
using var db = new ShopContext();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();
db.Products.AddRange(
    new Product { Name = "Laptop", Category = "Electronics", Price = 1200m },
    new Product { Name = "Mouse", Category = "Electronics", Price = 25m },
    new Product { Name = "Desk", Category = "Furniture", Price = 300m });
db.Orders.AddRange(new Order { ProductId = 1, Quantity = 2 }, new Order { ProductId = 99, Quantity = 1 });
db.SaveChanges();

Console.WriteLine("--- 1. IQueryable: filter runs in SQL");
var q = db.Products.Where(p => p.Price > 100m).Select(p => p.Name);
Console.WriteLine(q.ToQueryString());
Console.WriteLine(string.Join(", ", q));

Console.WriteLine("--- 2. Method the provider can't translate");
try
{
    var bad = db.Products.Where(p => Rules.IsPremium(p)).ToList();
}
catch (InvalidOperationException ex)
{
    Console.WriteLine(ex.Message);
}

Console.WriteLine("--- 3. AsEnumerable: works, but every row is loaded");
IEnumerable<Product> all = db.Products.AsEnumerable();
var premium = all.Where(p => Rules.IsPremium(p)).Select(p => p.Name);
Console.WriteLine(db.Products.ToQueryString());
Console.WriteLine(string.Join(", ", premium));

Console.WriteLine("--- 4. .NET 10 LeftJoin translated to SQL");
var lj = db.Orders.LeftJoin(db.Products, o => o.ProductId, p => p.Id,
    (o, p) => new { o.Id, Name = p == null ? "?" : p.Name });
Console.WriteLine(lj.ToQueryString());
Console.WriteLine(string.Join(", ", lj.Select(x => $"{x.Id}:{x.Name}")));

public static class Rules
{
    public static bool IsPremium(Product p) => p.Price > 1000m;
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Category { get; set; } = "";
    public decimal Price { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
}

public class ShopContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    protected override void OnConfiguring(DbContextOptionsBuilder o) => o.UseSqlite("Data Source=shop.db");
}
--- 1. IQueryable: filter runs in SQL
SELECT "p"."Name"
FROM "Products" AS "p"
WHERE ef_compare("p"."Price", '100.0') > 0
Laptop, Desk
--- 2. Method the provider can't translate
The LINQ expression 'DbSet<Product>()
    .Where(p => Rules.IsPremium(p))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
--- 3. AsEnumerable: works, but every row is loaded
SELECT "p"."Id", "p"."Category", "p"."Name", "p"."Price"
FROM "Products" AS "p"
Laptop
--- 4. .NET 10 LeftJoin translated to SQL
SELECT "o"."Id", CASE
    WHEN "p"."Id" IS NULL THEN '?'
    ELSE "p"."Name"
END AS "Name"
FROM "Orders" AS "o"
LEFT JOIN "Products" AS "p" ON "o"."ProductId" = "p"."Id"
1:Laptop, 2:?

What this shows:

  1. Filtering runs in the database. Only the Name column of the matching rows comes back. (SQLite has no decimal type, so EF Core 10 compares decimals with its own ef_compare function.)
  2. Any C# method EF Core doesn’t know fails at runtime with The LINQ expression … could not be translated. It compiles fine, so you only find out when the query runs. Put the condition in the query itself (p.Price > 1000m), or reuse it as an Expression<Func<Product, bool>>.
  3. The “fix” the message suggests, AsEnumerable(), loads every column of every row and filters in memory. On a 3-row table that’s fine; on a large table it isn’t. Use it only after a Where that the database can run.
  4. LeftJoin becomes a real LEFT JOIN in EF Core 10, and the null check in the selector becomes a SQL CASE.

One more compile error I hit while writing this: with IsPremium as a local function in a top-level program, the build fails with CS8110: An expression tree may not contain a reference to a local function. Expression trees can only call ordinary methods, so the method has to live in a class, like Rules above.

LINQ Cheat Sheet

You wantUseWatch out for
FilterWhereRuns again on every enumeration
“Is there any?”Any()Count() > 0 walks everything on a plain IEnumerable
First match or nothingFirstOrDefaultReturns null/0, check it
Exactly oneSingleSingleOrDefault still throws on 2+
Element with max valueMaxBy (.NET 6)Max returns the value, not the element
Count per keyCountBy (.NET 9)Returns KeyValuePairs
Sum per keyAggregateBy (.NET 9)Seed type sets the result type
Batches of NChunk (.NET 6)Each chunk is an array
Left outer joinLeftJoin (.NET 10)Inner value is nullable
Results you reuseToList()A snapshot: later changes aren’t seen
Database queryIQueryable + ToQueryString()Custom methods → could not be translated

FAQ

Is LINQ slower than a for loop? For in-memory collections, a hand-written loop usually allocates less, because LINQ creates an enumerator and calls a delegate per element. How much depends on the operator and the collection, so measure with BenchmarkDotNet before rewriting anything. Use LINQ for clarity and change hot paths only after a profiler points at them. For collection choice itself, see List vs Dictionary vs HashSet performance.

What’s the difference between IEnumerable and IQueryable? IEnumerable<T> runs your lambdas in memory. IQueryable<T> collects them as an expression tree that a provider (EF Core) translates, into SQL for example. Once you call AsEnumerable() or ToList(), everything after runs in memory.

Do I need LeftJoin if I use EF Core navigation properties? Usually not. Include or a projection over a navigation property gives you the outer join already. LeftJoin helps when there’s no navigation property, or for in-memory data.

Related: C# delegates and Func/Action (what the lambdas in LINQ are) · C# extension methods (how LINQ is built) · Nullable reference types

References: LINQ overview (Microsoft Learn) · Enumerable.LeftJoin API · EF Core client vs server evaluation

This article is part of the C# tutorials guide (language features, patterns, performance and testing).

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *