Dynamic LINQ DateTime Comparison String Building - Linq To Entities

Question

I'm using the dynamic LINQ library by Scott Guthrie together with Entity Framework and C#.

I have to build my where string into a variable based on several factors and then pass the string variable to the where clause. For some reason, this will work:

ContactList = ContactList.Where("DateAdded >= @0", DateTime.Parse("12/1/2012"));

But this will not work

string WhereClause = string.Format("DateAdded >= {0}", DateTime.Parse("12/1/2012"));
ContactList = ContactList.Where(WhereClause);

As mentioned, I need to use it in the version of passing the variable. Anyone know why the second doesn't work?

Thanks in advance!

SQL uses single quotes around DateTime fields - have you tried that? `... string.Format("DateAdded >= '{0}'", ...` — Jon Peterson, Jan 02 '13 at 17:49
Yes, tried that, same result. The solution I found was posted in my answer below. Thanks for the input. — Ricketts, Jan 03 '13 at 19:53

Richard Rout · Answer 1 · 2014-04-21T15:21:50.720

16

I was able to get it working with a slightly different string format using the information here.

Doing this worked fine for me:

ContactList.Where("DateAdded >= DateTime(2013, 06, 18)")

Note this does not work at all with DateTimeOffset columns.

edited Apr 21 '14 at 15:21

answered Jun 18 '13 at 13:42

Richard Rout

1,296
1
15
28

Should be: `DateTime(2013, 06, 18)` ... `Date` isn't a supported function in Linq2Entities. – Aage Mar 07 '14 at 14:49
2

And be careful not to name column DateTime because it will not work. – Makla Dec 21 '17 at 14:57
just to add to @Makla's point - if any column is called DateTime, then no DateTime compression will work, no matter what are your other columns called.. – St3ve Feb 03 '22 at 14:44

score 4 · Accepted Answer · edited May 23 '17 at 12:01

It seems what I was trying to do is not possible with the current DynamicLINQ library. The reason it didn't work was well outlined below by Tilak.

My solution was to modify the DynamicLINQ library to allow the query to be written as a string and passed to the where clause for Date/Time datatypes. The modification was found here by Paul Hatcher: LINQ TO SQL, Dynamic query with DATE type fields

score 1 · Answer 3 · answered Jan 02 '13 at 17:32

1

ObjectQuery.Where overload accepts 2 parameters.

string predicate
params ObjectParameter[] parameters

In your first example, Where builds the query (where clause) using ObjectParameter parameters (using Name, Type and Value of ObjectParameter)

In your second example, whatever is passed is treated as final where clause (no internal conversion based on datatype of passed parameters done).

answered Jan 02 '13 at 17:32

Tilak

30,108
19
83
131

Yes, I see where it is building the query when passing params, however, I can't seem to find a way to replicate what it is building. As I step through, it shows the final output as "Param_0 => (Param_0.DateAdded >= 12/1/2012 12:00:00 AM)". When I pass the string to be like that, it fails. Do you have any ideas on how I can get this to work by passing just the string variable without using params? – Ricketts Jan 02 '13 at 17:45
Explore `System.Data.Entity.dll!ObjectQuery.Where, EntitySqlQueryBuilder.Where` and `System.Data.Entity.dll!System.Data.Objects.Internal.EntitySqlQueryBuilder` in your favorite dissasembler. – Tilak Jan 02 '13 at 17:49

score 1 · Answer 4 · answered Mar 07 '14 at 15:39

Based on Richard Rout's option, with a slight modification:

ContactList.Where("DateAdded >= DateTime(2013, 06, 18)")

This works in my Linq2Entities solution. Note the DateTime instead of Date. Hope this saves someone the headache this problem gave me.

score 0 · Answer 5 · answered Jan 02 '13 at 17:20

0

ContactList.Where probably puts quotes around non-numeric arguments (such as a DateTime). In your second string the date isn't quoted.

answered Jan 02 '13 at 17:20

zmbq

38,013
14
101
171

I've tried it with quotes, when you set string.Format("DateAdded >= \"{0}\"", DateTime.Parse("12/1/2012")) you end up with the error "Operator '>=' incompatible with operand types 'DateTime' and 'String'" – Ricketts Jan 02 '13 at 17:29

score 0 · Answer 6 · answered Jun 21 '21 at 21:06

Had similar issue with dynamic linq core and ef 3.1 with sql server, solved it by explicitly setting up column type:

Fluent way:

modelBuilder.Entity<Contact>().Property(p => p.DateAdded).HasColumnType("datetime")

Attribute way:

public class Contact
{
     [Column(TypeName = "datetime")]
     public DateTime DateAdded { get; set; }
}

Usage:

ContactList = ContactList.Where("DateAdded == @0", "2021-03-25 02:29:00.000");

sql server datetime format: YYYY-MM-DD hh:mm:ss[.nnn]

score 0 · Answer 7 · answered Oct 13 '21 at 14:20

I know this is an old question but if someone is still encountering this issue with ASP.Net Core, I was able to resolve it as follows.

Basically you have to add the Entity class(table column) with the Column attribute and specifying the TypeName. For the above question, it's as follows:

[Column(TypeName = "datetime")]
public DateTime DateAdded { get; set; }

That should fix the datatype conversion errors.

Source : https://github.com/zzzprojects/System.Linq.Dynamic.Core/issues/240

score 0 · Answer 8 · answered Nov 16 '21 at 16:28

Kind of an old question, but in case this is helpful to anyone else, my issue was that it was taking my DateTime and making it a string. So the simple fix was to make it a date again. Something like:

var where = $"{propertyName} >= DateTime.Parse(\"{startDate}\") and {propertyName} <= DateTime.Parse(\"{endDate}\")";
query = query.Where(where);

It's a little more typing than adding the column attribute as The_Outsider said, but just another option.

Dynamic LINQ DateTime Comparison String Building - Linq To Entities

8 Answers8

Linked