使用GROUP BY和COUNT（DISTINCT）的LINQ to SQL

使用GROUP BY和COUNT（DISTINCT）的LINQ to SQL

尚无直接支持

COUNT(DISTINCT {x}))

，但您可以从

IGrouping<,>

（即

group by

返回什么）中进行模拟。恐怕我只“做”
C＃，所以您必须转换为VB。

 select new {     Foo= grp.Key,     Bar= grp.Select(x => x.SomeField).Distinct().Count() };

这是罗斯文（Northwind）的示例：

    using(var ctx = new DataClasses1DataContext())    {        ctx.Log = Console.Out; // log TSQL to console        var qry = from cust in ctx.Customers       where cust.CustomerID != ""       group cust by cust.Country       into grp       select new       {Country = grp.Key,Count = grp.Select(x => x.City).Distinct().Count()       };        foreach(var row in qry.OrderBy(x=>x.Country))        { Console.WriteLine("{0}: {1}", row.Country, row.Count);        }    }

TSQL不是我们想要的，但是可以完成工作：

SELECT [t1].[Country], (    SELECT COUNT(*)    FROM (        SELECt DISTINCT [t2].[City]        FROM [dbo].[Customers] AS [t2]        WHERe ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1].[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [t2].[Country]))) AND ([t2].[CustomerID] <> @p0)        ) AS [t3]    ) AS [Count]FROM (    SELECt [t0].[Country]    FROM [dbo].[Customers] AS [t0]    WHERe [t0].[CustomerID] <> @p0    GROUP BY [t0].[Country]    ) AS [t1]-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []-- Context: SqlProvider(Sql2008) Model: AttributedmetaModel Build: 3.5.30729.1

但是，通过手动运行结果可以正确验证：

        const string sql = @"SELECt c.Country, COUNT(DISTINCT c.City) AS [Count]FROM Customers cWHERe c.CustomerID != ''GROUP BY c.CountryORDER BY c.Country";        var qry2 = ctx.ExecuteQuery<QueryResult>(sql);        foreach(var row in qry2)        { Console.WriteLine("{0}: {1}", row.Country, row.Count);        }

具有定义：

class QueryResult{    public string Country { get; set; }    public int Count { get; set; }}