CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entities
Zac Blazic
Zac Blazic

Reputation: 607

Alternative to nesting when performing a left join and multiple inner joins

Consider the following fictitious scenario:

Entity relationship diagram

How would I go about getting a list of all the categories (distinct or otherwise, it doesn't matter) for each customer, even if a customer hasn't ordered any products?

Also assume that we don't have navigation properties, so we'll need to use manual joins.

This is my attempt which uses nesting:

var customerCategories = from c in context.Customers
                         join o in context.Orders on c.CustomerId equals o.CustomerId into orders
                         select new
                         {
                             CustomerName = c.Name,
                             Categories = (from o in orders
                                           join p in context.Products on o.ProductId equals p.ProductId
                                           join cat in context.Category on p.CategoryId equals cat.CategoryId
                                           select cat)
                         };

Is there a different (possibly better way) to achieve the same outcome?

Alternative: Multiple Left (Group) Joins

var customerCategories = from customer in context.Customers
                         join o in context.Orders on customer.CustomerId equals o.CustomerId into orders
                         from order in orders.DefaultIfEmpty()
                         join p in context.Products on order.ProductId equals p.ProductId into products
                         from product in products.DefaultIfEmpty()
                         join cat in context.Categories on product.CategoryId equals cat.CategoryId into categories
                         select new
                         {
                             CustomerName = c.Name,
                             Categories = categories
                         };

Upvotes: 3

Views: 330

Answers (2)

phillip
phillip

Reputation: 2738

I recreated your table structure and added some data so that I could get a better idea what you were trying to do. I found a couple of ways to accomplish what you want but I'm just going to add this method. I think it's the most concise and I think it's pretty clear.

Code

var summaries = Customers.GroupJoin(Orders,
    cst => cst.Id,
    ord => ord.CustomerId,
    (cst, ord) => new { Customer = cst, Orders = ord.DefaultIfEmpty() })
    .SelectMany(c => c.Orders.Select(o => new
        {
            CustomerId = c.Customer.Id,
            CustomerName = c.Customer.Name,
            Categories = Categories.Where(cat => cat.Id == c.Customer.Id)
        }));

Output

LINQ Output

Table Structure

Table Structure

Table Data

Table Data

Upvotes: 2

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

If you need all categories couldn't you just:

Categories = (from c in context.Category 
                      select cat)

Upvotes: 0

Related Questions