Document which DbFunction or BCL method translates to each SQL Server or other provider-specific function

[YehudahA]

I want to filter by DateTime property, omitting the time part:

DateTime d = DateTime.Now;
var query = myContext.Sales.Where(x => x.SaleDate.Date == d);

This code can run, but it takes all the rows and filters them in application side, instead of send the query to server.

in EF6 we have DbDunctions.TruncateTime. What about EF7?

[Daniel-Svensson]

Please choose a solution that will work for in memory queries as well (such as supporting DateTime.Date or DateTimeOffset.Date directly). So that we no longer have to write logic that must be different based on if you are executing it against a database or mock/in memory collection.

[rowanmiller]

Already tracked by dotnet/efcore#2850

[marchy]

@rowanmiller This issue did NOT get resolved by dotnet/efcore#2850, as the TruncateTime method that this issue requested – as did various comments within dotnet/efcore#2850, did not make its way way into DbFunctions.

Noted requests for this: dotnet/efcore#2850 (comment), dotnet/efcore#2850 (comment), dotnet/efcore#2850 (comment), dotnet/efcore#2850 (comment)

Can you please re-open this issue, as the requested functionality did NOT get implemented in dotnet/efcore#2850, despite this being marked as a duplicate which it's not.

[ajcvickers]

Note from triage: we should document which BCL methods have the behavior of certain database functions when translated.

@marchy This isn't implemented because EF Core translates DateTime.Date in an equivalent way--see dotnet/efcore#6778. Is there a reason you can't use this, or is it just that you were looking for TruncateTime?

[marchy]

Thank you @ajcvickers, that is indeed the solution we ended up going with after some trial & error and watching what the queries translate.

As you noted the docs would be very helpful to show the migration approach for this, since these two issues are the only ones we could find that made any mention of it.

Kind thanks!

[roji]

Note that we've recently added https://docs.microsoft.com/en-us/ef/core/providers/sql-server/functions, which documents all translations done by the SQL Server provider.