LINQ计数为空组返回1而不是零

问题描述

  SELECT oy.ownerId，oy.Year，COUNT（doc.Id） as docCount FROM aavabruf.owneryears oy 
 left joind vastdocuments doc 
 on oy.ownerId = doc.Ownerid and oy.Year = doc.Year 
 by oy.ownerid，oy.year 
 order by docCount 
  

它将docCount显示为ZERO，表示没有文档匹配的OwnerId，Year对在bigdocuments表中。

我尝试使用建议的左外连接解决方​​案对LINQ执行相同的操作：

<$来自OwnerYears中的oy的pre> 将新的{oy.OwnerId，oy.Year}的VaStDocuments中的doc加入docs
中的new {doc.OwnerId，doc.Year} from docIfNull in docS.DefaultIfEmpty（）
group oy by new {oy.OwnerId，oy.Year} into g
orderby g.Count（）ascending $ b $ select new {OwnerId = g.Key .OwnerId，Year = g.Key.Year，docCount = g.Count（）}

然而，对于Va中不存在的OwnerId，Year组stDocuments表我得到docCount为一个，而不是零。如果我从docS.DefaultIfEmpty（）中的docIfNull中删除

行的空组将不会显示出来。

如何将计数设置为零，就像在SQL中一样查询？我试过以下内容：

然而，在这种情况下，我得到一个错误：

解决方案 COUNT 函数会忽略 NULL 值，而LINQ Count 函数w / o谓词计算所有内容，包括 null s。

（注意 group docIfNull >所以> >）的谓词版本， c> g 元素将与 docIfNull ）具有相同类型：

<$ p $来自OwnerYears中的oy的$
将新的{oy.OwnerId，oy.Year}的VaStDocuments中的doc加入docs
中的doc {new在docS.DefaultIfEmpty（）
组docIfNull by new {oy.OwnerId，oy.Year} into g
let docCount = g.Count（doc => doc！= null）
orderby d ocCount升序
选择新{OwnerId = g.Key.OwnerId，Year = g.Key.Year，docCount = docCount}

（ let 子句只是重复使用 orderby 和<$ c

然而，在LINQ中，您有另一种选择 - 如果（OwnerId，Year） > OwnerYears 中的组合是唯一的，而不是 left outer join pattern后跟 group by 和 Count 可以使用简单的运算符与常规 Count 调用：

 来自OwnerYears中的oy 
在新的{oy.OwnerId，oy.Year}的VaStDocuments中加入doc等于new {doc.OwnerId，doc.Year}转换为文档
让docCount = docs.Count（）
 orderby docCount升序
选择新的{OwnerId = oy.Owner Id，Year = oy.Year，docCount = docCount} 
  

I've got this SQL query:

SELECT oy.ownerId, oy.Year,  COUNT(doc.Id) as docCount FROM aavabruf.owneryears oy
left join vastdocuments doc
on oy.ownerId = doc.Ownerid and  oy.Year =  doc.Year
group by oy.ownerid, oy.year
order by docCount

It shows docCount as ZERO for the OwnerId, Year pairs that have no document match in the vastdocuments table.

I tried to do the same with LINQ using the suggested left outer join solution:

from oy in OwnerYears
join doc in VaStDocuments on new {oy.OwnerId, oy.Year} equals new {doc.OwnerId , doc.Year} into docS
from docIfNull in docS.DefaultIfEmpty()
group oy by new {oy.OwnerId, oy.Year} into g
orderby g.Count() ascending
select new { OwnerId = g.Key.OwnerId,  Year = g.Key.Year, docCount = g.Count()}

However, for the OwnerId, Year groups that are not present in the VastDocuments table I get docCount as ONE, not ZERO. If I remove the

line the "empty" groups will not be shown at all.

How can i get the Count as zero just as it is in the SQL query? I tried the following:

however in this case I get an error:

解决方案

SQL COUNT function ignores the NULL values, while LINQ Count function w/o predicate counts everything, including nulls.

You can get the same result in LINQ by using the predicate version of Count like this (note the group docIfNull so the g elements will be of the same type as docIfNull):

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docS
from docIfNull in docS.DefaultIfEmpty()
group docIfNull by new { oy.OwnerId, oy.Year } into g
let docCount = g.Count(doc => doc != null)
orderby docCount ascending
select new { OwnerId = g.Key.OwnerId, Year = g.Key.Year, docCount = docCount }

(the let clause is just to reuse the expression in orderby and select).

However in LINQ you have another option - in case the (OwnerId, Year) combination inside OwnerYears is unique as it seems, instead of left outer join pattern followed by group by and Count filtering nulls you could use simple group join operator with regular Count call:

from oy in OwnerYears
join doc in VaStDocuments on new { oy.OwnerId, oy.Year } equals new { doc.OwnerId, doc.Year } into docs
let docCount = docs.Count()
orderby docCount ascending
select new { OwnerId = oy.OwnerId,  Year = oy.Year, docCount = docCount }

这篇关于LINQ计数为空组返回1而不是零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！