blocks|key|1649688|text|Linq+to+sql不支持计数(Distinct+...)。因此，您必须将代码中的.NET方法映射到Sql+server函数(因此Count(distinct..))并使用它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1649689|顺便说一句，如果你以既不是VB.NET也不是C#的格式发布从工具包复制来的伪代码，这是没有帮助的。|1649690|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Linq to sql has no support for Count(Distinct ...). You therefore have to map a .NET method in code onto a Sql server function (thus Count(distinct.. )) and use that. 

btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.

blocks|key|1649751|text|这就是执行非重复计数查询的方法。请注意，您必须过滤掉空值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1649752|var+useranswercount+=+(from+a+in+tpoll_answer
where+user_nbr+!=+null+&&+answer_nbr+!=+null
select+user_nbr).Distinct().Count();|code-block|syntax|javascript|1649753|如果您将其与当前的分组代码相结合，我认为您就有了自己的解决方案。|1649754|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This is how you do a distinct count query. Note that you have to filter out the nulls.

<pre><code>var useranswercount = (from a in tpoll_answer
where user_nbr != null &amp;&amp; answer_nbr != null
select user_nbr).Distinct().Count();
</code></pre>

If you combine this with into your current grouping code, I think you'll have your solution.

blocks|key|1435156|text|没有对COUNT(DISTINCT+{x}))的直接支持，但你可以从IGrouping<,>+(即group+by返回的内容)中模拟它；我恐怕我只“做”C#，所以你必须转换成VB……|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1435157|+select+new
+{
+++++Foo=+grp.Key,
+++++Bar=+grp.Select(x+=>+x.SomeField).Distinct().Count()
+};|code-block|syntax|javascript|1435158|下面是一个Northwind示例：|1435159|++++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);
++++++++}
++++}|1435160|TSQL不是我们想要的，但它可以完成我们想要的工作：|1435161|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|1435162|但是，结果是正确的-可以通过手动运行它来验证：|1435163|++++++++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);
++++++++}|1435164|定义如下：|1435165|class+QueryResult
{
++++public+string+Country+{+get;+set;+}
++++public+int+Count+{+get;+set;+}
}|1435166|entityMap^0|3|K|Y|C|1D|8|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|1B|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1C|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1E|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|1F|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1G|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|1H|8|@]|D|@]|E|$I|J]]|$1|10|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|11|$]]

There isn't direct support for <code>COUNT(DISTINCT {x}))</code>, but you can simulate it from an <code>IGrouping&lt;,&gt;</code> (i.e. what <code>group by</code> returns); I'm afraid I only "do" C#, so you'll have to translate to VB...

<pre><code> select new
 {
 Foo= grp.Key,
 Bar= grp.Select(x =&gt; x.SomeField).Distinct().Count()
 };
</code></pre>

Here's a Northwind example:

<pre><code> 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 =&gt; x.City).Distinct().Count()
 };

 foreach(var row in qry.OrderBy(x=&gt;x.Country))
 {
 Console.WriteLine("{0}: {1}", row.Country, row.Count);
 }
 }
</code></pre>

The TSQL isn't quite what we'd like, but it does the job:

<pre><code>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] &lt;&gt; @p0)
 ) AS [t3]
 ) AS [Count]
FROM (
 SELECT [t0].[Country]
 FROM [dbo].[Customers] AS [t0]
 WHERE [t0].[CustomerID] &lt;&gt; @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
</code></pre>

The results, however, are correct- verifyable by running it manually:

<pre><code> 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&lt;QueryResult&gt;(sql);
 foreach(var row in qry2)
 {
 Console.WriteLine("{0}: {1}", row.Country, row.Count);
 }
</code></pre>

With definition:

<pre><code>class QueryResult
{
 public string Country { get; set; }
 public int Count { get; set; }
}
</code></pre>

blocks|key|1649819|text|group+by如何在LINQ中工作的简单明了的示例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1649820|http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx|offset|length|1649821|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1Q|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

simple and clean example of how group by works in LINQ

<a href="http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx" rel="nofollow">http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx</a>

blocks|key|1435245|text|Marc+Gravell引用的Northwind示例可以用group语句直接选择的City列重写：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435246|from+cust+in+ctx.Customers
where+cust.CustomerID+!=+""
group+cust.City+/*here*/+by+cust.Country
into+grp
select+new
{
++++++++Country+=+grp.Key,
++++++++Count+=+grp.Distinct().Count()
};|code-block|syntax|javascript|1435247|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The Northwind example cited by Marc Gravell can be rewritten with the City column selected directly by the group statement: 

<pre><code>from cust in ctx.Customers
where cust.CustomerID != ""
group cust.City /*here*/ by cust.Country
into grp
select new
{
 Country = grp.Key,
 Count = grp.Distinct().Count()
};
</code></pre>

blocks|key|1435299|text|我不会费心在Linq2SQL中做这件事。为您想要并理解的查询创建一个存储过程，然后在框架中创建指向该存储过程的对象，或者直接连接到该存储过程。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435300|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I wouldn't bother doing it in Linq2SQL. Create a stored Procedure for the query you want and understand and then create the object to the stored procedure in the framework or just connect direct to it.

I have to perform the following SQL query:

<pre><code>select answer_nbr, count(distinct user_nbr)
from tpoll_answer
where poll_nbr = 16
group by answer_nbr
</code></pre>

The LINQ to SQL query 

<pre><code>from a in tpoll_answer 
where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct 
</code></pre>

maps to the following SQL query:

<pre><code>select distinct answer_nbr, distinct user_nbr
from tpoll_answer
where poll_nbr = 16
</code></pre>

So far, so good. However the problem raises when trying to GROUP the results, as I'm not being able to find a LINQ to SQL query that maps to the first query I wrote here (thank you <a href="http://www.linqpad.net/" rel="noreferrer">LINQPad</a> for making this process a lot easier). The following is the only one that I've found that gives me the desired result:

<pre><code>from answer in tpoll_answer where answer.poll_nbr = 16 _
group by a_id = answer.answer_nbr into votes = count(answer.user_nbr)
</code></pre>

Which in turns produces the follwing ugly and non-optimized at all SQL query:

<pre><code>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
</code></pre>

Any help will be more than appreciated.

LINQ to SQL using GROUP BY and COUNT(DISTINCT)

我必须执行以下SQL查询：select answer_nbr, count(distinct user_nbr)from tpoll_answerwhere poll_nbr = 16group by answer_nbrLINQ to SQL查询from a in tpoll_answer where a.poll_...

问LINQ to SQL使用GROUP BY和COUNT(DISTINCT)
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问LINQ to SQL使用GROUP BY和COUNT(DISTINCT)EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问LINQ to SQL使用GROUP BY和COUNT(DISTINCT)
EN