How to add List to a Key in a Linq to Sql Group By

Question

I would like to retrieve a List of elements as part of the compound key of the group by. But so far I have been unable to write the query to do it.

We are showing the total stock per RawMaterial (bellow more details for the model), and we would like to list alongside the suppliers for each RawMaterial. I tried this query:

var list = this.Stocks.Where(x => x.StockType == StockTypeEnum.RawMaterialEntered)
.GroupBy(i => new { RawMaterialId = i.RawMaterial.Id, RawMaterial = i.RawMaterial.Name, 
                    Suppliers = i.RawMaterial.Suppliers.Select(j=> new { Id= j.Supplier.Id, Name = j.Supplier.Name}) })
.Select(g => new StockSummary
{
   TotalAmount = g.Sum(i => i.Amount),
   ComponentId = g.Key.RawMaterialId,                   
   Component = g.Key.RawMaterial,
   Suppliers = g.Key.Suppliers.Select(h=> new StockItemModel(){ Id = h.Id, Name = h.Name} ).ToList()
})
.OrderByDescending(g => g.Component).ToList();

It builds but when I try to run it, I get this exception:

A first chance exception of type 'System.NotImplementedException' occurred in NHibernate.dll

We tried different approaches, but nothing worked. Is it possible to obtain the desired result with just one query?

I have this model:

public class Stock
{
    public RawMaterial RawMaterial{ get; set; }
    public int Amount { get; set; }
}

public class RawMaterial
{
    public int Id{ get;set; }
    public string Name { get;set; }
    public IList<SupplierInfo> SupplierInfos{ get;set; }
}

public class SupplierInfo
{
    public int Id{ get;set; }   
    public Supplier Supplier { get;set; }
}

public class Supplier
{
    public int Id { get;set; }
    public string Name { get; set; } 
}

Originally we had a grid where we show each the total stock available for a raw material. We retrieve that information with this query:

var list = this.Stocks.Where(x => x.StockType == StockTypeEnum.RawMaterialEntered)
  .GroupBy(i => new { RawMaterialId = i.RawMaterial.Id, RawMaterial = i.RawMaterial.Name })
  .Select(g => new StockSummary
               {
                   TotalAmount = g.Sum(i => i.Amount),
                   ComponentId = g.Key.RawMaterialId,                   
                   Component = g.Key.RawMaterial,                   
               })
               .OrderByDescending(g => g.Component).ToList();

This data is shown in a table similar to this:

And now, as mentioned above, we would like to show a list of the suppliers that provided said components. Is it possible to do it all in one query?

Answer 1

You may try:

var list = this.Stocks
    .Where(x => x.StockType == StockTypeEnum.RawMaterialEntered)
    .GroupBy(i => 
        new
        {
            RawMaterialId = i.RawMaterial.Id,
            RawMaterial = i.RawMaterial.Name
        })
    .Select(g => new StockSummary
    {
        TotalAmount = g.Sum(i => i.Amount),
        ComponentId = g.Key.RawMaterialId,                   
        Component = g.Key.RawMaterial,
        Suppliers = g
            .SelectMany(s => 
                s.RawMaterial.SupplierInfos.Select(j => 
                    new { Id = j.Supplier.Id, Name = j.Supplier.Name }))
            .Distinct()
            .Select(h => new StockItemModel() { Id = h.Id, Name = h.Name })
            .ToList()
    })
    .OrderByDescending(g => g.Component).ToList();

I think it will still match your requirements, and it avoids trying to group by a list. But I am not sure it can be executed by NHibernate.

You may have it easier by avoiding the group by: why not use RawMaterial as root of your query? This requires to add the Stocks collection on RawMaterial (or use sub-queries).

Guessing this.RawMaterials exists and is a IQueryable<RawMaterial>, it would be something like:

var list = this.RawMaterials
    .Where(rm =>
        rm.Stocks.Any(s => s.StockType == StockTypeEnum.RawMaterialEntered))
    .Select(rm => new StockSummary
    {
        TotalAmount = rm.Stocks
            .Where(s => s.StockType == StockTypeEnum.RawMaterialEntered)
            .Sum(s => s.Amount),
        ComponentId = rm.Id,                   
        Component = rm.Name,
        Suppliers = rm.SupplierInfos
            .Select(si =>
                new StockItemModel()
                {
                    Id = si.Supplier.Id,
                    Name = si.Supplier.Name
                })
            .ToList()
    })
    .OrderByDescending(ss => ss.Component).ToList();

I know this kind of queries are handled by EF, but I have not tried such queries with NHibernate. (NHibernate is much better at loading children collection than EF thanks to lazy-loading batching (if enabled). So I do not use with NHibernate the kind of projection you are trying.)

Answer 2

So first of all, your code is incomplete or you did not paste everything here. For example StockType and StockEnumType are missing and I removed those from my answer for simplicity purpose rather than trying to replicate what you did not show. Anyways, it works fine for me if I use string.Join(",", ...) although it does show Id and Name, but I think you shouldn't have no problem adjusting output by modifying your LINQ statement:

        var list = stocks
            .GroupBy(i => new { RawMaterialId = i.RawMaterial.Id, RawMaterial = i.RawMaterial.Name,
                Suppliers = i.RawMaterial.SupplierInfos.Select(j => new { Id = j.Supplier.Id, Name = j.Supplier.Name })
            })
            .Select(g => new
            {
                TotalAmount = g.Sum(i => i.Amount),
                ComponentId = g.Key.RawMaterialId,
                Component = g.Key.RawMaterial,
                Suppliers = string.Join(",", g.Key.Suppliers.Select(h => new { Id = h.Id, Name = h.Name }).ToList())
            })
            .OrderByDescending(g => g.Component).ToList();

And the output is with my test data:

1 Name3 30.00 ( Id = 1, Name = Sup1),( Id = 3, Name = Sup3)

2 Name2 20.00 ( Id = 1, Name = Sup1) ...