Dynamic LINQ date query performance

c# entity-framework entity-framework-core linq

Question

I am using System.Linq.Dynamic.Core library to generate queries in my project. I get passed a date string and I want to do the dynamic equivalent of this:

db.EntityName
    .Where(x => x.StartDate > DateTime.ParseExact("02/19/2018", "MM/dd/yyyy", CultureInfo.InvariantCulture))

I have found that this query below works:

db.EntityName
    .Where($"x => x.StartDate.ToFileTime() > { DateTime.ParseExact("02/19/2018", "MM/dd/yyyy", CultureInfo.InvariantCulture).ToFileTime() }")

Is this the right approach? It performs a conversion on StartDate so I am not sure if that will cause bad performance. Is this Ok or is there a better way to do it?

1
4
2/19/2018 8:23:32 AM

Accepted Answer

This is not a good idea. EF has no idea how to translate ToFileTime() to SQL query, so it will just query whole table and perform your Where in memory on client. The right way is to use parameters:

db.EntityName
    .Where($"x => x.StartDate > @0", DateTime.ParseExact("02/19/2018", "MM/dd/yyyy", CultureInfo.InvariantCulture));

@0 represents first parameter in a list, for which we pass parsed DateTime object.

If you are not sure if something is bad for perfomance - enable EF logging and see which SQL queries are generated.

4
2/19/2018 8:43:15 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow