CODEWITHSUNDEEP
c#entity-frameworklinq
Stian
Stian

Reputation: 1602

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

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

Upvotes: 0

Views: 1073

Answers (2)

NetMage
NetMage

Reputation: 26917

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();

Upvotes: 2

Caz1224
Caz1224

Reputation: 1569

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

Upvotes: 1

Related Questions