3

I have to run a query like the one bellow. It is actually more complex, but here is the part that matters:

var results =
    from b in _context.Bookings
    where b.ScheduleDate.Add(b.StartTime) >= DateTime.UtcNow
    select b;

But it gives the following error:

LINQ to Entities does not recognize the method 'System.DateTime.Add method(System.TimeSpan)', and this method cannot be translated into a store expression.

How can I work around this?

Thanks in advance.

Yuck
  • 49,664
  • 13
  • 105
  • 135
Phillippe Santana
  • 2,906
  • 2
  • 28
  • 29
  • 1
    I don't remember off the top of my head which methods to use and in what classes but there are classes out there which you can use to perform common date manipulations. Search for an "SQL methods" class. I believe it is compatible with EF. – Jeff Mercado May 27 '13 at 23:12

2 Answers2

8

SqlFunctions only works with Microsoft Sql Server.

In pure EF you can write:

DbFunctions.AddMilliseconds(x.Date, DbFunctions.DiffMilliseconds(TimeSpan.Zero, x.Time))

This works on all database adapters

Rafael
  • 2,642
  • 2
  • 24
  • 30
7

Try using the SqlFunctions.DateAdd method in the System.Data.Objects.SqlClient namespace. Documentation here. That will convert into the SQL method DateAdd, documented here. You might also be interested in using DateDiff instead, documented here.

In general, look at SqlFunctions for "common language runtime (CLR) methods that call functions in the database in LINQ to Entities queries." LINQ to Entities cannot convert any method call into SQL, but the functions in that class will work.

Your other option is to execute the LINQ to Entities query (using ToList or something similar) and then perform the logic in memory.

Ben Reich
  • 16,222
  • 2
  • 38
  • 59
  • According to [this post](http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server), casting a DATE and TIME both to DATETIME and then adding them together is the way to go, but none of the methods in **SqlFunctions** class or **EntityFunctions** class will let me do a CAST. And they don't have any methods for adding those types together out of the box, either. So the solution really is to perform the filter in memory OR to go down to using procedures. – Phillippe Santana May 28 '13 at 21:24
  • Or to created a computed column in the database, joining the two fields, and then use this in the application. – Phillippe Santana May 29 '13 at 15:21