Mastering `SelectMany` in EF Core: SQL Equivalents and Best Practices

Introduction Entity Framework Core (EF Core) provides powerful LINQ methods for querying relational databases in an expressive and efficient way. One of the most useful methods, SelectMany, allows developers to flatten nested collections and retrieve related data seamlessly. But how does SelectMany translate to SQL? And when should you use it? In this article, we’ll explore SelectMany with real-world examples, its SQL equivalents, and how to handle potential pitfalls such as null values. Understanding SelectMany in EF Core The SelectMany method is used when dealing with one-to-many or many-to-many relationships. It takes a collection and flattens it into a single sequence, which makes it particularly useful when working with related entities in EF Core. Basic Syntax context.Entities .SelectMany(entity => entity.CollectionProperty) .Where(condition) .Select(result => new { result.Property }); Why Use SelectMany? ✔ Flattens nested collections ✔ Reduces manual looping in memory ✔ Improves query readability and performance Real-World Scenarios and SQL Equivalents 1. Retrieving All Orders from Customers Scenario Each Customer has multiple Orders. We need to retrieve a flat list of all orders across all customers. EF Core Query var allOrders = context.Customers .SelectMany(c => c.Orders) .ToList(); SQL Equivalent Using INNER JOIN SELECT o.* FROM Customers c INNER JOIN Orders o ON c.Id = o.CustomerId; ✅ This query efficiently retrieves all orders without manually iterating over customers. 2. Filtering Data with CASE WHEN in SQL Scenario We want to categorize orders as "Expensive" (Price > $100) or "Affordable" (Price ≤ $100). EF Core Query with SelectMany var categorizedOrders = context.Customers .SelectMany(c => c.Orders, (c, order) => new { CustomerName = c.Name, OrderId = order.Id, Price = order.Price, Category = order.Price > 100 ? "Expensive" : "Affordable" }) .ToList(); SQL Equivalent Using CASE WHEN SELECT c.Name AS CustomerName, o.Id AS OrderId, o.Price, CASE WHEN o.Price > 100 THEN 'Expensive' ELSE 'Affordable' END AS Category FROM Customers c INNER JOIN Orders o ON c.Id = o.CustomerId; ✅ This efficiently categorizes each order in SQL before fetching the data. 3. Using SelectMany with Enums in EF Core Scenario Each Employee can have multiple roles, stored as an enum. We need to retrieve all roles in a flat list and categorize them as Technical or Non-Technical. Defining the Enum public enum EmployeeRole { Developer, Manager, HR, Tester } Entity Model public class Employee { public int Id { get; set; } public string Name { get; set; } public List Roles { get; set; } = new(); } EF Core Query with SelectMany and Conditional Mapping var employeeRoles = context.Employees .SelectMany(e => e.Roles, (e, role) => new { EmployeeName = e.Name, Role = role.ToString(), Category = role == EmployeeRole.Developer || role == EmployeeRole.Tester ? "Technical" : "Non-Technical" }) .ToList(); SQL Equivalent Using CASE WHEN SELECT e.Name AS EmployeeName, er.Role AS Role, CASE WHEN er.Role IN ('Developer', 'Tester') THEN 'Technical' ELSE 'Non-Technical' END AS Category FROM Employees e INNER JOIN EmployeeRoles er ON e.Id = er.EmployeeId; ✅ This approach allows us to transform enum values into meaningful categories using CASE WHEN. Handling Nullability with SelectMany Safely A common mistake when using SelectMany is assuming that the related collection is never null. The null-forgiveness operator (!) does not prevent runtime exceptions—it only suppresses compiler warnings. Example of a Potentially Unsafe Query var roles = context.Employees .SelectMany(e => e.Toles!) // Might cause an exception if Toles is null .ToList(); ❌ Why does this throw an exception? The ! operator does not change runtime behavior. If Toles is null, SelectMany will still throw a NullReferenceException because it tries to iterate over null. ✅ Safe Approaches to Avoid Null Exceptions 1. Using ?? to Provide a Default Value var roles = context.Employees .SelectMany(e => e.Toles ?? new List()) // Ensures no null exception .ToList();

Feb 13, 2025 - 08:13

Mastering `SelectMany` in EF Core: SQL Equivalents and Best Practices

Introduction

Entity Framework Core (EF Core) provides powerful LINQ methods for querying relational databases in an expressive and efficient way. One of the most useful methods, SelectMany, allows developers to flatten nested collections and retrieve related data seamlessly.

But how does SelectMany translate to SQL? And when should you use it? In this article, we’ll explore SelectMany with real-world examples, its SQL equivalents, and how to handle potential pitfalls such as null values.

Understanding `SelectMany` in EF Core

The SelectMany method is used when dealing with one-to-many or many-to-many relationships. It takes a collection and flattens it into a single sequence, which makes it particularly useful when working with related entities in EF Core.

Basic Syntax

context.Entities
    .SelectMany(entity => entity.CollectionProperty)
    .Where(condition)
    .Select(result => new { result.Property });

Why Use `SelectMany`?

✔ Flattens nested collections

✔ Reduces manual looping in memory

✔ Improves query readability and performance

Real-World Scenarios and SQL Equivalents

1. Retrieving All Orders from Customers

Scenario

Each Customer has multiple Orders. We need to retrieve a flat list of all orders across all customers.

EF Core Query

var allOrders = context.Customers
    .SelectMany(c => c.Orders)
    .ToList();

SQL Equivalent Using `INNER JOIN`

SELECT o.*
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId;

✅ This query efficiently retrieves all orders without manually iterating over customers.

2. Filtering Data with `CASE WHEN` in SQL

Scenario

We want to categorize orders as "Expensive" (Price > $100) or "Affordable" (Price ≤ $100).

EF Core Query with `SelectMany`

var categorizedOrders = context.Customers
    .SelectMany(c => c.Orders, (c, order) => new
    {
        CustomerName = c.Name,
        OrderId = order.Id,
        Price = order.Price,
        Category = order.Price > 100 ? "Expensive" : "Affordable"
    })
    .ToList();

SQL Equivalent Using `CASE WHEN`

SELECT 
    c.Name AS CustomerName,
    o.Id AS OrderId,
    o.Price,
    CASE 
        WHEN o.Price > 100 THEN 'Expensive'
        ELSE 'Affordable'
    END AS Category
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId;

✅ This efficiently categorizes each order in SQL before fetching the data.

3. Using `SelectMany` with Enums in EF Core

Scenario

Each Employee can have multiple roles, stored as an enum. We need to retrieve all roles in a flat list and categorize them as Technical or Non-Technical.

Defining the Enum

public enum EmployeeRole
{
    Developer,
    Manager,
    HR,
    Tester
}

Entity Model

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<EmployeeRole> Roles { get; set; } = new();
}

EF Core Query with `SelectMany` and Conditional Mapping

var employeeRoles = context.Employees
    .SelectMany(e => e.Roles, (e, role) => new
    {
        EmployeeName = e.Name,
        Role = role.ToString(),
        Category = role == EmployeeRole.Developer || role == EmployeeRole.Tester 
            ? "Technical" 
            : "Non-Technical"
    })
    .ToList();

SQL Equivalent Using `CASE WHEN`

SELECT 
    e.Name AS EmployeeName,
    er.Role AS Role,
    CASE 
        WHEN er.Role IN ('Developer', 'Tester') THEN 'Technical'
        ELSE 'Non-Technical'
    END AS Category
FROM Employees e
INNER JOIN EmployeeRoles er ON e.Id = er.EmployeeId;

✅ This approach allows us to transform enum values into meaningful categories using CASE WHEN.

Handling Nullability with `SelectMany` Safely

A common mistake when using SelectMany is assuming that the related collection is never null. The null-forgiveness operator (!) does not prevent runtime exceptions—it only suppresses compiler warnings.

Example of a Potentially Unsafe Query

var roles = context.Employees
    .SelectMany(e => e.Toles!) // Might cause an exception if Toles is null
    .ToList();

❌ Why does this throw an exception?

The ! operator does not change runtime behavior.
If Toles is null, SelectMany will still throw a NullReferenceException because it tries to iterate over null.

✅ Safe Approaches to Avoid Null Exceptions

1. Using `??` to Provide a Default Value

var roles = context.Employees
    .SelectMany(e => e.Toles ?? new List<Role>()) // Ensures no null exception
    .ToList();