converting ms sql "group by" query to linq to sql

Question

i have two tables, products and categories. how do i convert the following sql query to linq format?

select c.Name, c.DisplayName, count(p.id) from categories as c
LEFT join products as p on p.categoryId = c.id
group by c.Name, c.DisplayName

some categories will have 0 products in them so the LEFT JOIN is important

Answer 1

Hi, if you correctly define the relations in your model then the customers table should have an association with all the products, so you'll be able to access products associated with the customer just using 'c.Products'. Then should be able to write simply something like this:

  var q = 
    from c in categories
    select new { c.Name, c.DisplayName, Count = c.Products.Count() }

T.

Answer 2

dc.categories
.Select(c => new
{
  Name = c.Name,
  DisplayName = c.DisplayName,
  TheCount = c.Products.Count()
}

If you want to do a left join in other cases, check out "group join".

dc.categories
.GroupJoin(dc.products,
  c => c.id,
  p => p.categoryid,
  (c, g) => new { Name = c.Name, TheCount = g.Count() }
);

Answer 3

to show the grouping on basis.

 var q =   from c in db.categories
    group c by new {c.Name , c.DisplayName} into alias
    select new { alias.Name, 
                 alias.DisplayName, 
                 Count = alias.Count(p => p.id) 
                }