c# query categories and count its products via joined product table

Question

I may be way off, but some googling has led me to produce this query:

VMCategory = from pc in _context.ProductCategories
            join pic in _context.ProductsInCategories
            on pc.Id equals pic.ProductCategoryId
            group pic by pc into x
            select new ViewModelProductCategory
            {
                Id = x.Key.Id,
                ParentId = x.Key.ParentId,
                Title = x.Key.Title,
                SortOrder = x.Key.SortOrder,
                NumOfProductsInThisCategory = x.Count(c => [SOMETHING IS MISSING])
            }).
            ToList();

I'm trying to populate the viewmodel list with the category items in the entity model and a count of all the products in each category.

I need this type of result (as items in a list of category objects):

Id = 6                          (from ProductCategory)
ParentId = 4                    (from ProductCategory)
Title = "Example"               (from ProductCategory)
SortOrder = 2                   (from ProductCategory)
NumOfProductsInThisCategory = 7 (count products from ProductsInCategories)

These are my models:

Viewmodel:

public class ViewModelProductCategory
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Title { get; set; }
    public int SortOrder { get; set; }
    public int NumOfProductsInThisCategory { get; set; }
}

Entity models:

public class ProductCategory
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Title { get; set; }
    public int SortOrder { get; set; }
}

public class ProductInCategory
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int ProductCategoryId { get; set; }
    public int SortOrder { get; set; }
}

Possible duplicate of [linq with groupby and count](https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count) — devlin carnate, Dec 19 '17 at 20:44
What happens if you leave it just as `x.Count()`? Can you make a [fiddle](https://dotnetfiddle.net/#)? — Grizzly, Dec 19 '17 at 20:57

score 2 · Accepted Answer · answered Dec 19 '17 at 21:21

2

If you are joining, you can do a GroupJoin and you don't need a GroupBy:

VMCategory = from pc in _context.ProductCategories
            join pic in _context.ProductsInCategorieson pc.Id equals pic.ProductCategoryId into picj
            select new ViewModelProductCategory {
                Id = pc.Id,
                ParentId = pc.ParentId,
                Title = pc.Title,
                SortOrder = pc.SortOrder,
                NumOfProductsInThisCategory = picj.Count()
            }).
            ToList();

answered Dec 19 '17 at 21:21

NetMage

26,163
3
34
55

That helped! :) But now I want to add a list of products to each category. I tried this, but it's no good: (from pc in _context.ProductCategories join pic in _context.ProductsInCategories on pc.Id equals pic.ProductCategoryId into picj join p in _context.Products on pic.ProductId equals p.Id select new ViewModelProductCategory { Id = pc.Id, ParentId = pc.ParentId, Title = pc.Title, SortOrder = pc.SortOrder, NumOfProductsInThisCategory = picj.Count(), Products = new Product { Id = p.Id, Title = p.Title, Info = p.Info, Price = p.Price } }).ToList(); – Stian Dec 20 '17 at 08:46

score 1 · Answer 2 · answered Dec 19 '17 at 21:20

Something that might help is forming a many to one bond with your models. So change your Entity Models to this:

public class ProductCategory
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Title { get; set; }
    public int SortOrder { get; set; }
    public List<ProductInCategory> ProductInCategory { get; set; }
}

public class ProductInCategory
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int SortOrder { get; set; }
    public int ProductCategoryId { get; set; }
}

Then what you can do is this:

var ProductList = _context.ProductCategory.Include(p=> p.ProductInCategory).ToList()

Nice and simple and you have your full list of categories and each of those categories will contain a list of all the products in it. You can throw where clauses on the end of that varing to do counts and the like. Or even an IF loop or Foreach loop to say foreach product category count ProductInCategory.

Heaps you can do once you have a relationship between the tables.

Caz

c# query categories and count its products via joined product table

2 Answers2