LINQ counts null as 1 - how to avoid that?

Question

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.

Here is my SQL:

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

Which returns

7   2015
102 2016

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) };

Which returns

7   2015
102 2016
10  1

What am I missing?

How sure are you that this is about NULL ? When `DateCompleted == null` then `DateCompleted.Year` will throw. — H H, Sep 30 '19 at 07:02
Can you post the declaration of `Request.DateCompleted` ? It should be nullable. — H H, Sep 30 '19 at 07:03
What happens in the SQL result if you change `WHERE ISNULL(DateCompleted,'') <> ''` to `WHERE DateCompleted IS NOT NULL`? — Peter Smith, Sep 30 '19 at 07:04
In your sql you also check that it is not an empty string `<> ''` you dont do that in the linq expression. — Magnus, Sep 30 '19 at 07:09
@PeterSmith - `DateCompleted is not null` yields the same result as `ISNULL(DateCompleted,'') <> ''` — Sha, Sep 30 '19 at 07:22
Wait a second. What type is DateCompleted ? How can you compare it to null, while also accessing Year directly? That shouldn't be possible if it was nullable DateTime. — Euphoric, Sep 30 '19 at 07:32
`DateCompleted != null` will always be true for `DateTime` type, which is obviously is a type of `DateCompleted` property. — Fabio, Sep 30 '19 at 07:34

score 2 · Accepted Answer · answered Sep 30 '19 at 07:43

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.

This will require change to the query :

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.

Thank you - I forgot to make DateTime nullable in my class like so: `public DateTime? DateCompleted { get; set; }` — Sha, Sep 30 '19 at 07:46

score 1 · Answer 2 · answered Sep 30 '19 at 07:54

This is probably because DateCompleted is not of type DateTime?, I think you just need to switch where and group :

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

LINQ counts null as 1 - how to avoid that?

2 Answers2

Linked