Why does DbFunctions not work with Linq to Entities

Question

var vals = (from r in result
                    group r by DbFunctions.TruncateTime(r.Created) into g
                    select new { count = g.Count(), date = g});

When I try to run the above I get the following error

Results is the result of a DB query

"This function can only be invoked from LINQ to Entities."

The model (result) returns Dates (amongst other things), all I'm trying to do is group by the date (created) and work out how many times it existed.

Created is already DateTime

What am I doing wrong?

@spender, I'm already doing what that posts suggests. I also get a different error message to that post, so the question is different — MyDaftQuestions, Apr 26 '16 at 18:08
@spender That post is talking about using `DbFunctions` in a different version of EF. This question is a bout using `DbFunctions` in Linq-to-objects, which is not possible. — D Stanley, Apr 26 '16 at 18:11
Yep. I was just about to reopen, but you beat me to it. Apologies to you both. — spender, Apr 26 '16 at 18:11
@spender, thank you for reading our feedback and taking action. It's great to know the system works and mods are cool to read, review and take appropriate action — MyDaftQuestions, Apr 26 '16 at 18:12
@spender No worries - I just hate it when someone reverses an action I've made without explaining _why_ :) — D Stanley, Apr 26 '16 at 18:13
Are you in fact using Linq to Entities (ie entity framework) or are you using Linq to Objects (ie IEnumerables)? — Erik Funkenbusch, Apr 26 '16 at 18:13

D Stanley · Accepted Answer · 2016-04-26T18:17:16.693

The purpose of the DbFunctions class is to convert expressions to the proper SQL syntax:

Provides common language runtime (CLR) methods that expose EDM canonical functions for use in DbContext or ObjectContext LINQ to Entities queries.

Since you aren't querying a SQL source, there's no reason to use the DbFunctions methods.

The equivalent Linq-to-object query would be:

var vals = (from r in result
            group r by r.Created.Date into g
            select new { v = g.Count(), d = g});

The issue may be that Created is DateTime?

You can account for that with the conditional operator:

var vals = (from r in result
            group r by r.Created.HasValue ? (DateTime?)r.Created.Value.Date
                                          : (DateTime?)null 
                into g
            select new { v = g.Count(), d = g});

or just

var vals = (from r in result
            group r by r.Created.Value.Date into g
            select new { v = g.Count(), d = g});

if there's no possibility that Created is null (or if you want to throw an exception if it is)

The `TruncateTime` function generates SQL that strips off the time portion of a `DATETIME` expression. The `Date` property of `DateTime` does the same thing for .NET `DateTime` values. — D Stanley, Apr 26 '16 at 18:09
When I try this, it won't compile. The issue may be that Created is DateTime? (accepts nulls) — MyDaftQuestions, Apr 26 '16 at 18:13
This *is* right.. as it's nullable, I need to add `.Value` (as I knew it was populated for this query) — MyDaftQuestions, Apr 26 '16 at 18:17

Why does DbFunctions not work with Linq to Entities

1 Answers1