Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Question: What is the proper way to construct a dynamic query for EF Core using DateTime or Nullable DateTime? #240

Closed
JeremySBrown opened this issue Jan 17, 2019 · 6 comments

Comments

@JeremySBrown
Copy link

With EF 6 and Dynamic Linq I could just do the following predicate:
"PooledDate >= new DateTime(2019,1,1,0,0,0)"

Now with EF Core 2.2 this does not work. It results with SqlException "Conversion failed when converting date and/or time from character string."

Here is the expression that is generated:
(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[TracSeq.Data.Models.PoolData]).Where(Param_0 => (Param_0.PooledDate >= Convert(1/1/2019 12:00:00 AM)))

I've tried string literals, and get the following:

(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[TracSeq.Data.Models.PoolData]).Where(Param_0 => (Param_0.PooledDate >= 1/1/2019 12:00:00 AM))

Any help that could point me in the right direction would be greatly appreciated.

@StefH
Copy link
Collaborator

StefH commented Jan 18, 2019

Same issue as : #183 ?

@JeremySBrown
Copy link
Author

I actually tried what was suggested in #183, but not matter how I constructed the predicate I get the same exception. I'm using EF Core 2.2.1 and tried version 2.1.1 to match the version used in the issue.

I'll see about forking the repo and run the tests for 183.

@MannyL
Copy link

MannyL commented Jan 22, 2019

I'm running into possibly the same issue. In my case, System.Linq.Dynamic converts the DateTime variable to datetime2 format, but the field in the database is just datetime type. The generated SQL looks something like:

WHERE MyDateTimeField >= '2019-01-21T23:47:12.4107294Z'

which will pass if MyDateTimeField is datetime2, but will fail if it's datetime with the "Conversion failed when converting date and/or time from character string" error message referenced above.

@MannyL
Copy link

MannyL commented Jan 22, 2019

If this is the same problem you're running into, you may be able to resolve this by decorating the Entity class with the Column attribute and specifying the TypeName:

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

@JeremySBrown
Copy link
Author

Thanks @MannyL! That solved the issue for me.

@StefH
Copy link
Collaborator

StefH commented Apr 4, 2019

closing

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

3 participants