我必须执行以下SQL查询:
select answer_nbr, count(distinct user_nbr) from tpoll_answer where poll_nbr = 16 group by answer_nbr
LINQ to SQL查询
from a in tpoll_answer where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct
映射到以下SQL查询:
select distinct answer_nbr, distinct user_nbr from tpoll_answer where poll_nbr = 16
到目前为止,一切都很好。但是,在尝试对结果进行分组时会出现问题,因为我无法找到映射到我在此处编写的第一个查询的LINQ to SQL查询(感谢LINQPad,使此过程更加容易)。以下是我发现的唯一给我期望结果的结果:
from answer in tpoll_answer where answer.poll_nbr = 16 _ group by a_id = answer.answer_nbr into votes = count(answer.user_nbr)
依次在所有SQL查询中产生以下丑陋且未优化的结果:
SELECT [t1].[answer_nbr] AS [a_id], ( SELECT COUNT(*) FROM ( SELECT CONVERT(Bit,[t2].[user_nbr]) AS [value], [t2].[answer_nbr], [t2].[poll_nbr] FROM [TPOLL_ANSWER] AS [t2] ) AS [t3] WHERE ([t3].[value] = 1) AND ([t1].[answer_nbr] = [t3].[answer_nbr]) AND ([t3].[poll_nbr] = @p0) ) AS [votes] FROM ( SELECT [t0].[answer_nbr] FROM [TPOLL_ANSWER] AS [t0] WHERE [t0].[poll_nbr] = @p0 GROUP BY [t0].[answer_nbr] ) AS [t1] -- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [16] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
任何帮助将不胜感激。
尚无直接支持COUNT(DISTINCT {x})),但您可以从IGrouping<,>(即group by返回什么)中进行模拟。恐怕我只“做” C#,所以您必须转换为VB。
COUNT(DISTINCT {x}))
IGrouping<,>
group by
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 c WHERE c.CustomerID != '' GROUP BY c.Country ORDER 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; } }