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:
x
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?
Advertisement
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.