c# - 使用导航将 SQL Distinct、Count 和 GroupBy 转换为 Linq 查询

Question

我在 LINQ 中有这个查询，其中 query 是来自表 LeadSales 的预定义 IQueryable...

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

它工作正常，但我需要稍微调整一下。我在 SQL 中也有这个:

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

按预期返回此数据集:

现在，我需要将 LINQ 查询转换为具有 GROUP BY，并包括计数，当然还要保留导航以便我可以访问 FirstName 和 LastName。

我在使用 LINQ 语法时遇到问题...

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

仅供引用...

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

Answer 1

我想这就是您要找的:

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

您需要使用匿名类型按多个列进行分组