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

When you query a parent entity and access a child collection:

• Use Select if you want to keep the grouping by parent
• Use SelectMany if you want all child rows as one flat result

Typical example

Suppose each Customer has an Orders collection.

• Select(c => c.Orders) gives you a sequence of order collections
• SelectMany(c => c.Orders) gives you one sequence of orders

That is the core difference.

Why developers care

This affects:

• the type of result returned
• how easy it is to filter child records
• how SQL-like the query feels
• whether you are working with grouped results or flat rows

In database work, flattened results are very common because SQL queries usually return rows, not nested collections. That is why SelectMany often feels more natural when navigating one-to-many relationships.

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

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

What this returns

This returns a sequence where each item is that customer's order collection.

Conceptually, the type is similar to:

IEnumerable<EntitySet<Order>>

You still have one result per customer.

Using SelectMany

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

What this returns

This returns one flat sequence of Order objects from all customers.

Conceptually, the type is similar to:

IEnumerable<Order>

Practical LINQ to SQL example

Suppose you want all orders placed in 2024.

With SelectMany

var orders2024 = db.Customers
    .SelectMany(c => c.Orders)
    .Where(o => o.OrderDate.Year == 2024);

This is easy because after SelectMany, you are working directly with Order items.

With Select

var groupedOrders = db.Customers
    .Select(c => c.Orders.Where(o => o.OrderDate.Year == 2024));

This returns a sequence of filtered order collections, one collection per customer.

Selecting parent and child together

Sometimes you want the child items flattened, but you also want parent information.

var customerOrderRows = db.Customers
    .SelectMany(c => c.Orders, (customer, order) => new
    {
        CustomerName = customer.Name,
        order.OrderId,
        order.OrderDate,
        order.Total
    });

This produces a flat row-like result, which is often ideal in data access code.

Why this is useful

It resembles a SQL join result:

• one row per order
• customer fields repeated as needed
• easy to filter, sort, and display

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 = "Alice", OrderId = 102 },
  { Customer = "Bob", OrderId = 201 }
]

Compare that with Select

If you wrote this instead:

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

The conceptual result would be more like:

[
  [101, 102],
  [201],
  []
]

That is the difference in shape:

• Select keeps one result per customer
• SelectMany creates one result per order

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
• audit logs
• data exports

Because these usually need flat row-based results.

Select is more common in:

• nested JSON responses
• hierarchical UI data
• parent-detail screens

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.

5. Joining through navigation properties

Instead of writing manual joins, developers often use navigation properties with SelectMany.

var orderDetails = db.Orders
    .SelectMany(o => o.OrderDetails, (order, detail) => new
    {
        order.OrderId,
        detail.ProductId,
        detail.Quantity
    });

This is readable and matches object relationships.

Rule of thumb

Ask yourself:

• Do I need one result per parent? Use Select.
• Do I need one result per child item across all parents? Use SelectMany.

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(c => c.Orders) -> collection of collections
• SelectMany(c => c.Orders) -> flat collection

Always think about the output type.

4. Writing extra loops because of wrong operator choice

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

foreach (var orders in customerOrders)
{
    foreach (var order in orders)
    {
        Console.WriteLine(order.OrderId);
    }
}

This is valid, but if you only need all orders, SelectMany is simpler:

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

foreach (var order in orders)
{
    Console.WriteLine(order.OrderId);
}

5. Confusing projection with filtering

Select and SelectMany shape data. They do not filter by themselves.

Use Where for filtering:

var bigOrders = db.Customers
    .SelectMany(c => c.Orders)
    .Where(o => o.Total > 500);

6. Assuming empty child collections produce nulls in SelectMany

If a customer has no orders, SelectMany does not create a result item with null. It simply contributes zero items.

Select vs SQL thinking

• Select is like choosing columns or creating shaped objects per row.
• SelectMany is closer to expanding one-to-many relationships into row results.

Query syntax comparison

These two query-syntax examples also show the difference.

Select

var query =
    from c in db.Customers
    select c.Orders;

SelectMany

var query =
    from c in db.Customers
    from o in c.Orders
    select o;

The second from clause in query syntax is effectively a flattening operation, which maps to SelectMany.

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

• Do I want one result per customer? -> Select
• Do I want one result per order? -> SelectMany

Edge case

If a parent has no child items:

• Select still returns an empty child collection for that parent
• SelectMany returns no child rows for that parent

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) => new { c.Name, o.OrderId })

Is query syntax using two from clauses the same as SelectMany?

Yes, in many cases a second from clause translates to SelectMany.

Should I always prefer SelectMany in database queries?

No. Use it only when you need flattened results. If you need to preserve grouping by parent, Select is better.

• 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.