Linq Joins with SelectMany





5.00/5 (5 votes)
Linq Joins with SelectMany
SelectMany projects each element of a sequence to an
As shown in the above image, customer is having relation with the
IEnumerable
and flattens the resulting sequences into one sequence. In this post, I am going to show how you can use SelectMany
extension method to achieve the join
between related tables easily without writing long queries.
To understand this, consider the example below:

order
table.
Scenario
Find all of the users who have an order
.
To achieve the above requirement, you need to apply an inner join between the tables. The Linq query to do this is:
var CustomerWithOrders = from c in Customers
from o in c.Orders
select new {
c.Name,
p.Description,
o.Price
}
The query above inner join
s the customer
table with the order
table and returns those customer
s having order
s.
The shorter way to achieve the above is to use SelectMany
:
Customers.SelectMany (
c => c.Orders,
(c, o) =>
new
{
Name = c.Name,
Description = o.Description,
Price = o.Price
}
)
Scenario 2
Find all of the users who have an order
and display N/A for the users who do not.
To achieve the above requirement, you need to apply an Outer join
between the tables. The Linq query to do this is:
var CustomerWithOrders = from c in Customers
from o in c.Orders.DefaultIfEmpty()
select new {
Name =c.Name,
Description = ( o.Description ?? "N/A"),
Price = (((decimal?) o.Price) ?? 0)
}
The query above outer join
s the customer
table with the order
table and returns all of the customer
s with or without order
s.
But with the SelectMany
, the query is:
Customers.SelectMany (
c => c.Orders.DefaultIfEmpty (),
(c, o) =>
new
{
Name = c.Name,
Description = (o.Description ?? "N/A"),
Price = ((Decimal?)(o.Price) ?? 0)
}
)
Summary
SelectMany
is a way to flatten several results into a single sequence.