How to make LINQ query with GroupBy and LEFT JOIN

Question

I am using EF4 and I need to make this query with LINQ, but I don't know how to do it.

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 ProductType with their products in one season. Please note that I need to list ALL ProductType , even if there is no product this season.

Thank you for your help!

Answer 1

Assuming you need a left join, according to 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
            };

Answer 2

:

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 };

, join ... into, , , .