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();

🔹 If Toles is null, it defaults to an empty list instead of throwing an error.

2. Filtering Out Null Collections Before SelectMany

var roles = context.Employees
    .Where(e => e.Toles != null)  // Exclude employees with null Toles
    .SelectMany(e => e.Toles!)
    .ToList();

🔹 This prevents SelectMany from attempting to iterate over a null value.

---

Key Takeaways

• SelectMany is essential for flattening one-to-many and many-to-many relationships in EF Core.
• SQL INNER JOIN is the most common equivalent for SelectMany.
• CASE WHEN in SQL is the best approach for categorizing data within queries.
• Enums can be mapped to meaningful string values using SelectMany.
• Always handle null values properly—the ! operator does not prevent runtime exceptions.
• Use ?? or Where(e => e.Collection != null) to avoid null-related crashes.

---

Final Thoughts

EF Core’s SelectMany simplifies working with related collections, making queries more efficient and readable. By understanding its SQL translation and handling null values safely, you can write optimized queries that perform well in both LINQ and SQL.

Next time you're working with nested collections in EF Core, leverage SelectMany to make your queries cleaner and faster! 🚀

---

💡 What are your experiences with SelectMany in EF Core? Let me know in the comments!