How can I use LINQ to select columns from multiple tables with Count and Group By?

Question

This is my SQL query in C# and I want to convert it to a LINQ query.

string sqlProduct = string.Format(@"SELECT protype.idType, TypeName, **COUNT(idPro) as proNumber** 
                                    FROM protype, product 
                                    WHERE protype.idType=product.idType 
                                    **GROUP BY protype.idType, TypeName** 
                                    ORDER BY TypeName");
 DataTable dt = new DataTable();
 dt = Shop_Query.Select_Query(sqlProduct);
 lvMenu.DataSource = dt;
 lvMenu.DataBind();

score 0 · Answer 1 · edited May 23 '17 at 11:57

try this:

var query = dbContext.Protypes.Join(dbContext.Products,
                  protype => protype.idType,
                  product => product.idType,
                  (protype, product) => new { Protype = protype, Product = product })
                  .GroupBy(s => new { s.Protype.idType, Product.TypeName })
                  .Select(s => new
                  {
                     IdType = s.First().Protype.idType,
                     TypeName = s.First().Product.TypeName,
                     Count = s.Count()
                  });

there are lots of assumption in this query.

I've assumed your Linq-to-Sql context to be dbContext
I've assumed your context classes to be named as Products and ProTypes
I've assumed TypeName field belongs to Product table

Now you need to learn about linq GroupBy and Linq Join

How can I use LINQ to select columns from multiple tables with Count and Group By?

1 Answers1