LINQ counts null as 1 – how to avoid that?

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?

Answer

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.