Using grouping to get list of lists not working

Question

User1615115075 posted

Hi,

I've got a basic idea of a list of products, inside of a list of product categories.  So, something like this:

public class Products
{
  public List<ProductGroup> ProductGroups { get; set; }
}

public class ProductGroup
{
  public string ProductGroupName { get; set; }
  public List<Product> Products { get; set;}
}

public class Product
{
  public string ProductName { get; set; }
}

Now, using EF, i want to populate this, but I am having problems with the GroupBy.  This is what I have at the moment, without any grouping:

var query = (from p in _db.Products join pg in _db.ProductGroups on p.ProductGroupId equals pg.ProductGroupId
                         where p.Active.Equals(1)
                         orderby pg.Name ascending, p.Name ascending
                         select new Entities.LabPrices.ProductGroup
                         {
                             ProductGroupName = pg.Name,
                             Products = pg.Products.Select(p => new Entities.LabPrices.Product
                             {
                                 ProductID = p.ProductId,
                                 ProductName = p.Name,
                             })
                         });

The issue i am having is with adding in the grouping.  If I add GroupBy(g => g.ProductGroupName); to the end of the above, I get a compile error saying, can't convert between GenericList and List<Product> group.

So i read another article about putting the group into the statement. like:

var query = (from p in _db.Products join pg in _db.ProductGroups on p.ProductGroupId equals pg.ProductGroupId
                         where p.Active.Equals(1)
                         orderby pg.Name ascending, p.Name ascending
                         group pg by pg.Name into g
                         select new Entities.LabPrices.ProductGroup
                         {
                             ProductGroupName = g.Key,
                             /*Products = pg.Products.Select(p => new Entities.LabPrices.Product
                             {
                                 ProductID = p.ProductId,
                                 ProductName = p.Name,
                             })*/
                         });

But then I can't find a way of populating the sub object of Products using this method.

Would someone be able to offer some advice how I could achieve this?

TIA

Tony

Answer 1

User1559292362 posted

Hi wigwam_uk,

But then I can't find a way of populating the sub object of Products using this method.

Per your code snippet, I create a similar demo for your reference.

var query = (from p in _db.Products
                             join pg in _db.ProductGroups on p.ProductGroupId equals pg.ProductGroupId
                             where p.Active == 1
                             orderby pg.ProductGroupName ascending, p.ProductName ascending
                             group pg by pg.ProductGroupName into gg
                             select new
                             {
                                 ProductGroupName = gg.Key,
                                 Products = gg.Select(c=>c.Products).FirstOrDefault()
                             }).ToList();

In addition, you could use any method to achieve it. like this:

var result = _db.ProductGroups.Include("Products").Where(pg => pg.Products.Any(p => p.Active == 1)).ToList();

Best regards,

Cole Wu

Answer 2

User1615115075 posted

Cole,

Thanks for the response.  We have decided to go in a different direction, so won't be doing the grouping in Linq any more

Thanks thought

Tony

Answer 3

User1559292362 posted

Hi wigwam_uk,

Thanks for the response.  We have decided to go in a different direction, so won't be doing the grouping in Linq any more

If you solve the issue, could you please share your solution on here, It will be very beneficial for other community members who have the similar questions. 

Best regards,

Cole Wu