LINQ GroupBy Count

Question

I have a table containing EmployeeID, ProductID and ProductName. The table is called EmployeeProduct, and I would like to make a query (which I will associate with the DataGrid) that will give me the result as follows:

Employee ID| ProductID | Name | Count(ProductID) 1 | 2 | XYZ | 3 1 | 5 | ZXY | 2 2 | 2 | XYZ | 1 

I tried to get it like this on this request, but it will not produce a result ... // UPDATED - now I am trying to do it this way, but I am not getting the result ... //

(Home.xaml.cs)

 public class ProductCount { public int ProductNumber { get; set; } public int EmployeeNumber { get; set; } public int CountProducts { get; set; } } public IQueryable<ProductCount> CountProducts() { var data = from b in _employ.EmployeeProduct group b by new { b.EmployeeID, b.ProductID } int z select new ProductCount { EmployeeNumber = z.Key.EmployeeID, ProductNumber = z.Key.ProductNumber, CountProducts = z.Count()}; return data.AsQueryable(); } 

and then in the code that I would like to bind to my datagrid, but unfortunately it does not cause an error, but if I do this:

 dg.ItemsSource = CountProducts(); 

he shows nothing ...

Answer 1

Here are some ways to do this:

 var employeeProducts = new List<EmployeeProduct>(); employeeProducts.Add(new EmployeeProduct(1, 2, "XYZ")); employeeProducts.Add(new EmployeeProduct(1, 5, "ZXY")); employeeProducts.Add(new EmployeeProduct(2, 2, "XYZ")); var way1 = employeeProducts.Select( ep => new ProductCount { ProductNumber = ep.ProductID, EmployeeNumber = ep.EmployeeID, CountProducts = employeeProducts.Count(epc => epc.ProductID == ep.ProductID) }); var way2 = employeeProducts .GroupBy(ep => ep.ProductID) .SelectMany(epg => epg.Select( ep => new ProductCount { ProductNumber = ep.ProductID, EmployeeNumber = ep.EmployeeID, CountProducts = epg.Count() })); 

Answer 2

I think the query will look something like this.

  EntityQuery<EmployeeProduct> query = from c in _employ.CountProduct() group c by new {c.UserId, c.ProductId, c.Name} into g select new { g.UserId, g.ProductId, g.Name, Count = g.Count() }