Convert SQL Distinct, Count & GroupBy to a Linq query with Navigation

Question

I have this query in LINQ, where query is a pre-defined IQueryable from the table LeadSales...

var results = from q in query
    select new LeadBuyersByStateItem {
        ContactID = q.SoldToContactID,
        FirstName = q.Contact.FirstName,
        LastName = q.Contact.LastName
     };

It works fine, but I need to adjust it a little. I also have this in SQL:

SELECT
    SoldToContactID, 
    COUNT (SoldToContactID) AS Count
FROM
    LeadSales
GROUP BY 
    SoldToContactID

Which returns this dataset, as expected:

So now, I need to convert the LINQ query to have the GROUP BY, and include the Count, and of course keep the navigation so I can get to FirstName and LastName.

I'm having trouble with the LINQ syntax...

var results = from q in query
    group q by q.SoldToContactID into g
    select new LeadBuyersByStateItem {
        ContactID = ?.SoldToContactID,
        FirstName = ?.Contact.FirstName,
        LastName = ?.Contact.LastName
        ContactCount = ????
     };

And just FYI...

public class LeadBuyersByStateItem
{
    public int ContactID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int ContactCount { get; set; }
}

octavioccl octavioccl · Accepted Answer · 2017-03-14T19:49:10

I think this is what you are looking for:

var results = from q in query
    group q by new {q.SoldToContactID,
                    FirstName=q.Contact.FirstName,
                    LastName=q.Contact.LastName} into g
    select new LeadBuyersByStateItem {
        ContactID = g.Key.SoldToContactID,
        FirstName = g.Key.FirstName,
        LastName = g.Key.LastName,
        ContactCount = g.Count()
     };

You need to group by several columns using an anonymous type

Convert SQL Distinct, Count & GroupBy to a Linq query with Navigation

1 Answers