Casey Crookston Casey Crookston - 14 days ago 9
C# Question

Having trouble with a Group By in LINQ

Here's my LINQ query:

var results = from l in leads
from qr in l.QuoteRevisions
from rp in qr.RevisionProducts
select new QuoteSearchItem
{
ID = l.ID,
....
ProductCodes = l.QuoteRevisions.SelectMany(qr => qr.RevisionProducts)
.Select(p => p.Product.ProductCode).ToList()
};


The object we are trying to fill up looks like this:

public class QuoteSearchItem
{
public LeadID {get; set; }
....
public List<string> ProductCodes { get; set; }
....
}


The results I'm getting are almost accurate. But the problem is, when there is more than one product code, I get identical rows for each lead. So in stead of getting this:

{"LeadID": "12", "ProductCodes": ["Code1", Code2"]}


I get this:

{"LeadID": "12", "ProductCodes": ["Code1", Code2"]}
{"LeadID": "12", "ProductCodes": ["Code1", Code2"]}


So, I need to Group By l.LeadID. But I'm having trouble with that syntax. I tried this:

var results = from l in leads
from qr in l.QuoteRevisions
from rp in qr.RevisionProducts
group l by l.ID into lds
select new QuoteSearchItem
{
ID = lds.ID,
....
ProductCodes = lds.QuoteRevisions.SelectMany(qr => qr.RevisionProducts)
.Select(p => p.Product.ProductCode).ToList()
};


But then "lds" doesn't seen to contain anything. Not sure how to do this.

Thanks!

Answer

You are selecting all revision products and then constructing a list of leads. That is the problem, because now for every revision product you get one element containing all revision products for its lead.

Try removing subsequent from:

var results = from l in leads
    select new QuoteSearchItem
    {
        ID = l.ID,
        ....
        ProductCodes =
            l.QuoteRevisions
                .SelectMany(qr => qr.RevisionProducts)
                .Select(p => p.Product.ProductCode)
                .ToList()
    };