Linq to entities does not know what .Date operator is

Question

I am getting an error saying linq to entities does not know about the .Date operator in the following query:

var data = from p in page_loads
           join f in sites
           on p.site_id equals f.id
           join tr in test_runs
           on p.test_run_id equals tr.id
           where f.id == 17
           group p by  p.created_at.Date into g
           select new { time = g.Average(t => t.time), created_at = g.Key };

Note: created_at is NOT NULL in the database. I am pointing this out because I know that created_at is not nullable and linq to entities would have issues with that. But I don't know how to fix this issue.

Edit:

[EdmScalarProperty(EntityKeyProperty = false, IsNullable = false)]
    [DataMember]
    public DateTime created_at { get; set; }

I just added the definition of just created_at because I thought that's all you probably need to take a look at. — SherCoder, Jun 20 '12 at 19:51

Jon Skeet · Answer 1 · 2012-06-20T20:02:38.543

4

It sounds like you might want SqlFunctions.DatePart:

group p by SqlFunctions.DatePart("day", p.created_at) into g

edited Jun 20 '12 at 20:02

answered Jun 20 '12 at 19:05

Jon Skeet

1,421,763
867
9,128
9,194

I still get this error: `Server Error in '/' Application. The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported` – SherCoder Jun 20 '12 at 19:47
I think my issue is that `DatePart` wants `DateTime?` as its second argument but I am providing it `DateTime`. What do you think Jon? Is there any work around that? – SherCoder Jun 20 '12 at 20:02
@SherCoder: Sorry, edited - I meant to get rid of the `Date` property. Done now. Try again. – Jon Skeet Jun 20 '12 at 20:02
I get this after that change: `The specified method System.Nullabl1[System.Int32] DatePart(System.String, System.Nullable1[System.DateTime]) on the type System.Data.Objects.SqlClient.SqlFunctions cannot be translated into a LINQ to Entities store expression.` – SherCoder Jun 20 '12 at 20:08
Hmm. That's odd. I thought the whole point of `SqlFunctions` was to avoid that problem... Which database are you using, out of interest? – Jon Skeet Jun 20 '12 at 20:11
It is a `MySQL` database. Maybe that's the issue – SherCoder Jun 20 '12 at 20:13
@SherCoder: Yes, almost certainly. `SqlClient` is for SQL Server databases. I wish you'd mentioned this before... you'll need to see if there's an equivalent `SqlFunctions` for MySQL. – Jon Skeet Jun 20 '12 at 20:16
I can't seem to anything related to mysql that would help. But do you think if I first pull all the time and created_at into `var` without getting the average and just the date part and then work with that `var`. wouldn't it be easier that way? – SherCoder Jun 20 '12 at 20:54
@SherCoder: I'm not sure what you mean, but if you're talking about fetching the data and working out the average locally, that would *work* - but it would be potentially much less efficient. – Jon Skeet Jun 20 '12 at 20:58

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

You can group by other fields in the DateTime (creating a new unique anonymous object for each year/month/day combination) and you'll want to use let to simplify the query:

var data = from p in x
       join f in sites
       on p.site_id equals f.id
       join tr in test_runs
       on p.test_run_id equals tr.id           
       where f.id == 17
       let date = p.created_at
       group p by new { y = date.Year, m = date.Month, d = date.Day} into g
       select new { time = g.Average(t => t.time), created_at = g.Key };

If you want the created_at in your final projection to not be an anonymous type, you can convert the group-by anonymous type back into a DateTime by passing the 3 anonymous values to the DateTime constructor.

There are other solutions presented in the answers to another SO question that use EntityFunctions.TruncateTime.

edited May 23 '17 at 12:27

Community

1
1

answered Jun 20 '12 at 23:40

Kit

20,354
4
60
103

That looks like its quite useful, but I don't understand what you mean by `convert the group-by anonymous type back into a DateTime...`. did you mean something like this: `group p by new DateTime { y = date.Year, m = date.Month, d = date.Day} into g`? – SherCoder Jun 21 '12 at 00:24
In your `select` do `created_at = new DateTime(g.Key.Year, g.Key.Month, g.Key.Day)`... but it looks like you could do what you said as well. – Kit Jun 21 '12 at 13:59
why does it give me `NotSupportedException` when I try to do this: ` var mydata = data.ToArray();` – SherCoder Jun 21 '12 at 16:17
Because it's truly not supported (yet): https://connect.microsoft.com/VisualStudio/feedback/details/362794/the-linq-expression-node-type-newarrayinit-is-not-supported-in-linq-to-entities – Kit Jul 03 '12 at 18:27

Linq to entities does not know what .Date operator is

2 Answers2