Dynamic LINQ Date Query Performance

Question

I use the library System.Linq.Dynamic.Coreto generate queries in my project. A date string is passed to me, and I want to make a dynamic equivalent of this:

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

I found that this request 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 converts to StartDate, so I'm not sure if this will lead to poor performance. Is this good or is there a better way to do this?

Answer 1

This is not a good idea. EF does not know how to translate ToFileTime()into an SQL query, so it will simply query the entire table and execute your Wherein memory on the client. The correct way is to use the parameters:

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

@0 , DateTime.

, - - EF , SQL- .