Using DateTime in Dynamic LINQ to Entities

Question

I am using LINQ to Entities to retrieve item purchase dates as follows:

where EntityFunctions.TruncateTime(order.PurchaseDate) == myPurchaseDate.date

The key here is that the DB column contains the Date and Time so the time must be stripped for the compare. This code works fine.

Now, I want to do the same thing using dynamic LINQ to Entities. I am using dynamic.cs from the VS2010 code samples folder. When I code:

.where("EntityFunctions.TruncateTime(PurchaseDate) == @0", myPurchaseDate.date);

or any variant of same I get an error message. What do I have to code as the string value to make this work? (Since I can use .StartsWith or .Contains inside the string I am hoping there is some date function dynamic LINQ will recognize).

I know I can create the dynamic LINQ query to be a date range, conceptually:

PurchaseDate >= myPurchaseDate@midnight and PurchaseDate <= myPurchaseDate+23:59:59

In fact, maybe a date range is more efficient from a SQL Server perspective but I would like to know if something like TruncateTime or ToShortDate exists within Dynamic LINQ to Entities.

What version of Entity Framework are you using? – John Hartsock Feb 09 '11 at 20:15 — John Hartsock, Feb 09 '11 at 20:15

score 12 · Answer 1 · answered May 18 '11 at 14:03

I recently started using dynamic linq for a project and also wanted to compare dates without the time component. Microsoft's dynamic linq C# sample code (Dynamic.cs) supports a fixed set of types, and EntityFunctions isn't one of them.

But with a little experimentation, I found that just adding EntityFunctions to the array of predefined types enables the use of TruncateTime and likely other EntityFunctions methods too.

Here's what the Dynamic.cs predefinedTypes array looks like in my project:

static readonly Type[] predefinedTypes = {
    typeof(Object),
    typeof(Boolean),
    typeof(Char),
    typeof(String),
    typeof(SByte),
    typeof(Byte),
    typeof(Int16),
    typeof(UInt16),
    typeof(Int32),
    typeof(UInt32),
    typeof(Int64),
    typeof(UInt64),
    typeof(Single),
    typeof(Double),
    typeof(Decimal),
    typeof(DateTime),
    typeof(TimeSpan),
    typeof(Guid),
    typeof(Math),
    typeof(Convert),
    typeof(System.Data.Objects.EntityFunctions)             // JimM
};

With this modified Dynamic.cs file, I'm able to create dynamic linq queries including expressions like the PurchaseDate example in your question.

Agreed very neat solution, very useful. – James Close Jan 03 '12 at 16:49 — James Close, Jan 03 '12 at 16:49

score 2 · Answer 2 · answered Feb 09 '11 at 20:26

If your queries will often search by just the date aspect, another approach to consider, if available with your SQL Server database, would be to redundantly store a truncated version of the datetime column, in a date column type. See http://msdn.microsoft.com/en-us/library/bb630352.aspx).

All of your queries can then perform better because there are no conversions necessary, are less prone to developer error. And they're easier to query in plain old SQL as well.

Your EF queries would then query on the SQL Server date column

We are currently starting design of the new database and this is something I plan to raise with the database architect. — user610342, Feb 10 '11 at 15:16

score 0 · Answer 3 · answered Feb 09 '11 at 20:15

0

I think you are going to want to go with the date range so that you can take advantage of indexing on the server if you need to. If you base your select statement on a calculated field, it will need to scan the table for each query.

answered Feb 09 '11 at 20:15

Donald Byrd

7,668
4
33
50

Thanks for confirming my hunch. While I haven't done any query plan analysis this makes sense to me. – user610342 Feb 10 '11 at 15:13

score 0 · Answer 4 · answered Feb 09 '11 at 20:19

0

Here is a link that lists EF4 functions that map to sql functions

Conceptual Model Canonical to SQL Server Functions Mapping

answered Feb 09 '11 at 20:19

John Hartsock

85,422
23
131
146

Thank-you. I have seen that list. However, it does not apply to dynamic LINQ to Entities queries. Frankly, I am not sure it even applies to Linq to Entities as I have always had to use the EntityFunctions canonical list (which is different). – user610342 Feb 10 '11 at 15:11

Using DateTime in Dynamic LINQ to Entities

4 Answers4

Linked