How make LINQ query with GroupBy and LEFT JOIN

Question

I'm using EF4 and i need to make this query with LINQ but i don't know how.

If i have 3 tables:

• ProductType
• Product
• Season

ProductType -> one-to-many -> Product -> many-to-one -> Season

i would like to have a list of all the ProductType with their Products for one Season. Please note that i need to list ALL the ProductType even if there's no Product inside for that Season.

thank you for the help!

Answer 1

Try this:

var query = from pt in model.ProductTypes
            join p in model.Product.Where(p => p.SeasonId == seasonId)
               on pt.Id equals p.ProductTypeId into g
            select new { ProductType = pt, Products = g };

I have to admit I'm always somewhat rusty on join ... into but I think this will do what you're after.

Answer 2

Presuming you do want a left join, as per your question, do:

var query = from pt in model.ProductTypes
            select new
            {
                ProductType = pt,
                Products = from p in pt.Products
                           where p.SeasonId == seasonId
                           select p
            };