LINQ将null计为1-如何避免这种情况? [英] LINQ counts null as 1 - how to avoid that?

问题描述

我正在尝试在LINQ中复制一个简单的COUNT/GROUP BY.它可以在SQL中运行，但我的LINQ不能发挥作用.我不想计算空值，但是由于某种原因，我的LINQ语句做到了.

I am trying do replicate a simple COUNT / GROUP BY in LINQ. It works in SQL but my LINQ is not playing ball. I don't want to count nulls but my LINQ statement does that for some reason.

这是我的SQL:

  SELECT Count(ID),Year(DateCompleted)
    FROM dbo.Requests
    WHERE ISNULL(DateCompleted,'') <> ''
    group by year(datecompleted)

哪个返回

7   2015
102 2016

这是我的LINQ-计数为null.

Here is my LINQ - which counts null.

var test = from r in Model.Requests
           where r.DateCompleted != null
           group r by r.DateCompleted.Year into grp
           select new ChartSimple() { K = grp.Key.ToString(), V =  grp.Count(x => x.DateCompleted != null) };

返回哪个

7   2015
102 2016
10  1

我想念什么?

推荐答案

Request实体上DateCompleted的类型必须为DateTime?或Nullable<DateTime>.这将告诉Entity Framework，它应该期望该列可以为空，从而生成适当的SQL.现在，您的代码似乎可以编译了，但这是因为DateTime的!= opeartor过载，并且null被转换为DateTime类型，无法有效表示.

The type of DateCompleted on Request entity must be DateTime? or Nullable<DateTime> . This will tell Entity Framework that it should expect the column to be nullable and thus generate appropriate SQL. Right now, your code seems to compile, but that is because there is overload of DateTime's != opeartor and null being translated to DateTime type, which it cannot efficiently represent.

这将需要更改查询:

var test = from r in Model.Requests
       where r.DateCompleted != null
       group r by r.DateCompleted.Value.Year into grp
       select new ChartSimple() { K = grp.Key.ToString(), V =  grp.Count() };

计数中的谓词是不必要的.

And the predicate in count is unecessary.

这篇关于LINQ将null计为1-如何避免这种情况?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！