Select vs SelectMany in LINQ and LINQ to SQL

By the end of this page, you will understand how Select and SelectMany differ in C# LINQ, why that difference matters in LINQ to SQL, and when to use each one while querying related data such as customers and orders. You will also see how these operators shape query results differently: Select keeps nested collections, while SelectMany flattens them into a single sequence.

Select and SelectMany are both projection operators in LINQ. A projection means transforming data into a new shape.

Select

Select takes each item from a source sequence and converts it into exactly one result value.

If the value you return is itself a collection, then the final result becomes a sequence of collections.

• Input: a sequence of items
• Output: one result per input item
• If each result is a collection, you get nested collections

SelectMany

SelectMany also projects each item, but when each projection returns a collection, it flattens all those inner collections into one sequence.

• Input: a sequence of items
• Each item maps to a collection
• Output: one flat sequence containing all inner items

Why this matters in LINQ to SQL

In LINQ to SQL, entities often have relationships:

• Customer has many Orders
• Order has many OrderDetails
• Department has many Employees

Imagine a school:

• Each classroom contains many students.

If you use Select on classrooms to get students, you get:

• Classroom A -> its student list
• Classroom B -> its student list
• Classroom C -> its student list

So you now have a list of student lists.

If you use SelectMany, you open every classroom and put all students into one hallway line.

So you get:

• one single list of all students from all classrooms

Simple analogy

• Select = keep the boxes
• SelectMany = open the boxes and combine everything inside

In LINQ to SQL:

• parent table rows are the boxes
• related child rows are the contents inside each box

Core syntax

var result1 = source.Select(x => x.SomeValue);
var result2 = source.SelectMany(x => x.SomeCollection);

Example model

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public EntitySet<Order> Orders { get; set; }
}

public class Order
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
    public int CustomerId { get; set; }
}

Using Select

Consider this query:

var query = db.Customers
    .SelectMany(c => c.Orders, (customer, order) => new
    {
        Customer = customer.Name,
        OrderId = order.OrderId
    });

Now imagine the data looks like this:

• Alice has orders 101 and 102
• Bob has order 201
• Carol has no orders

Step by step

1. LINQ starts with db.Customers.
2. It reads the first customer: Alice.
3. c => c.Orders gets Alice's order collection.
4. SelectMany flattens that collection, so orders 101 and 102 become separate result items.
5. The result selector creates:
   • { Customer = "Alice", OrderId = 101 }
   • { Customer = "Alice", OrderId = 102 }
6. LINQ moves to Bob.
7. Bob's order collection contains order 201.
8. That becomes:
   • { Customer = "Bob", OrderId = 201 }
9. LINQ moves to Carol.
10. Carol has no orders, so no result items are produced for her.

Final result

[
  { Customer = "Alice", OrderId = 101 },
  { Customer = , OrderId =  },
  { Customer = , OrderId =  }
]

When to use Select

Use Select when you want to preserve the parent-to-child grouping.

Examples

• Fetch each customer along with their order collection
• Build nested view models
• Group data by parent object for display
• Transform each entity into a custom object

var customerSummaries = db.Customers
    .Select(c => new
    {
        c.Name,
        Orders = c.Orders
    });

When to use SelectMany

Use SelectMany when you need a flat list of child records.

Examples

• Get all orders from all customers
• Filter all order items across the whole database
• Export flat row data to CSV
• Build reports from related records
• Search child entities regardless of parent

var expensiveOrders = db.Customers
    .SelectMany(c => c.Orders)
    .Where(o => o.Total > 1000);

API and reporting scenarios

SelectMany is common in:

• reporting queries
• admin dashboards
• search endpoints

In real projects, developers often choose between Select and SelectMany based on the shape needed by the next step of the code.

Common patterns

1. Flatten child entities for filtering

var lateOrders = db.Customers
    .SelectMany(c => c.Orders)
    .Where(o => o.OrderDate < DateTime.Today.AddDays(-30));

This is common when filtering belongs to the child entity.

2. Project flat DTOs for APIs

var results = db.Customers
    .SelectMany(c => c.Orders, (c, o) => new OrderDto
    {
        CustomerName = c.Name,
        OrderId = o.OrderId,
        Total = o.Total
    });

This creates response-friendly objects directly from the query.

3. Guarding against unnecessary nesting

If you use Select and later need another loop just to reach inner items, SelectMany may be the cleaner choice.

4. Validation and filtering before flattening

var activeOrderRows = db.Customers
    .Where(c => c.IsActive)
    .SelectMany(c => c.Orders)
    .Where(o => o.Total > 0);

This pattern first reduces parent rows, then flattens children.

1. Expecting Select to flatten automatically

Broken expectation:

var orders = db.Customers.Select(c => c.Orders);

Beginners often expect orders to be a sequence of Order, but it is a sequence of order collections.

Fix

var orders = db.Customers.SelectMany(c => c.Orders);

2. Using SelectMany when grouping is needed

Broken idea:

var result = db.Customers.SelectMany(c => c.Orders);

If your UI needs to show each customer with their own order list, this loses that grouping.

Fix

Use Select with a custom projection:

var result = db.Customers.Select(c => new
{
    c.Name,
    Orders = c.Orders
});

3. Forgetting the result shape

The biggest mistake is not checking what type comes out.

Select vs SQL thinking

Quick rules

• Use Select when each input produces one output.
• Use SelectMany when each input produces a collection, and you want one flat sequence.

Common patterns

// Keep nested collections
var customerOrders = db.Customers.Select(c => c.Orders);

// Flatten nested collections
var allOrders = db.Customers.SelectMany(c => c.Orders);

// Flatten and project parent + child data
var rows = db.Customers.SelectMany(c => c.Orders, (c, o) => new
{
    c.Name,
    o.OrderId
});

Query syntax equivalents

// Select
from c in db.Customers
select c.Orders;

// SelectMany
from c in db.Customers
from o in c.Orders
select o;

Result shape reminder

Select(c => c.Orders)      // IEnumerable<Collection<Order>>
SelectMany(c => c.Orders)  // IEnumerable<Order>

Best question to ask yourself

What is the main difference between Select and SelectMany in LINQ?

Select returns one result for each source item. SelectMany flattens collections returned from each source item into one sequence.

When should I use SelectMany in LINQ to SQL?

Use it when navigating one-to-many relationships and you want a flat sequence of child records, such as all orders from all customers.

Does SelectMany work like a SQL join?

Often, yes. It commonly behaves like expanding related rows, especially when used with navigation properties.

Why does Select(c => c.Orders) not return all orders directly?

Because each customer has its own order collection. Select keeps those collections separate instead of flattening them.

Is SelectMany only for lists inside lists?

No. It is for any situation where each source item maps to multiple results and you want one flat output sequence.

Can I include parent and child data with SelectMany?

Yes. Use the overload with a result selector:

.SelectMany(c => c.Orders, (c, o) =>  { c.Name, o.OrderId })

• Where — used alongside Select and SelectMany to filter results.
• Join — another way to combine related data, especially when navigation properties are not used.
• GroupBy — useful when you want to re-group flattened data.
• Navigation properties — central to understanding how LINQ to SQL moves from parent entities to child entities.
• Projection — the broader concept behind Select and SelectMany.
• Anonymous types — commonly used to shape query results.
• Deferred execution — important for understanding when LINQ queries actually run.
• IEnumerable<T> vs IQueryable<T> — helps explain how LINQ behaves in memory versus against a database.