Query: Provide EF.Functions to define methods that correspond to standard SQL operations

[roji]

Not sure whether this has already been discussed (or makes sense).

There are some standard SQL operations that don't correspond to an already-existing .NET method; the example that comes to mind is LIKE. It seems that up to now, each provider invented its own way to allow users to specify these in LINQ to Entities - SqlClient has SqlFunctions and SqlMethods.

It seems to makes sense to pull the standard SQL functions and methods out of these SQL Server-specific classes and place them in the generic EF7 code, this way the same LINQ can be used across different providers...

[ErikEJ]

I think it is already there: https://github.com/aspnet/EntityFramework/blob/dev/src/EntityFramework.Relational/Query/Methods/ContainsTranslator.cs

[divega]

@roji This does make sense in general although it is probably worth digging more into what "standard SQL operations" means 😄

We have talked within the EF team about the need to have a way to represent any standard relational functions as well as provider specific functions that aren't well represented by LINQ operators or by standard method calls in .NET BCL classes. We came up with a pattern we decided to try in EF7 that we believe can be better than what we had in the past. We just haven't found an opportunity to start using it, but would look similar to this:

var legalMinors = context.People.Where(p => 
    EF.Functions.DiffYear(p.BirthDate, DateTime.Now) - 
    DateTime.Now.DayOfYear < p.BirthDate.DayOfYear ? 1 : 0 
    < legalAge);

The idea is to add a new Functions property to the static EF class (the same class where we put the Property method you can use to access shadow state properties in LINQ). Then EF and providers as well can define extension methods for the type of the Functions property.

Here are a few (subject to change) rules for these:

1. In general, EF and providers should use a high bar for adding methods here. If there is a reasonable pattern that can be represented with LINQ operators or standard .NET BCL method calls we should enable the translation of that pattern instead.
2. When we consider adding any new function to the set of "standard SQL functions" (i.e. if we put it in our base relational provider as opposed to a specific relational provider) or even as a standard "EF function" (in EF core, if we ever decide to do that) we should first ponder the burden added on providers to support it. Although it seems nice to have a good set of standard functions that enables customers to write common LINQ query code that works across different providers, it wouldn't be good if a significant number of these functions ended up triggering client evaluation on many providers because they aren't actually supported.
3. We will recommend that provider specific functions use prefixing to avoid collisions when multiple providers are in scope. E.g. define EF.Functions.SqlServerHostName() as opposed to EF.Functions.HostName().

@ErikEJ It is true that String.Contains() and more importantly String.StartsWith() leverages LIKE in a very valuable use case, and that makes having a way to express LIKE directly in LINQ less important. However I don't think it is true that everything that you can express with LIKE is covered in existing .NET BLC methods so we don't discard adding support for it in the future.

[divega]

Clearing up this for triage. I think we can choose having this issue in the backlog and up-for-grabs, or we can wait until the concrete need arises to have specific functions added.

[roji]

Sorry for disappearing for a while.

@ErikEJ, Contains provides only a subset of what can be done with SQL LIKE (consider 'a%b%c')

[roji]

@divega, the approach looks great.

Regarding rule 2, it might be acceptable for some methods to throw NotSupportedException rather than trigger client evaluation which if they aren't supported by a specific provider. This may encourage more unportable code, but would be more fail-fast (i.e. an exception is better than hard-to-detect slowdown due to client evaluation).

Regarding rule 3, it seems cleaner for provider-specific functions to be defined in a provider-specific static class rather than have provider-specific prefixes on the EF.Functions class (i.e. Npgsql.EF.Functions.HostName() rather than EF.Functions.NpgsqlHostName()).

[divega]

@roji Re rule 2 (client evaluation vs. throwing not supported for performance reasons), client evaluation is actually one of the value adds of EF7 and I don't think we can always assume that evaluating something on the client is going to be so expensive than it is better not to do it. It will most likely depend heavily on how many rows will be returned that wouldn't if the function had been evaluated on the server, but in many cases it should be ok.

For lack of a better strategy I think it is desirable to leave the decision to the application developer, e.g. think about having a simple ClientEvaluationEnabled flag or perhaps the opposite StrictServerEvaluationEnabled (and I just made up these names 😄) that would control if doing client evaluation on constructs that are outside the top most projection is ok or whether it should throw. I believe we have an issue tracking this idea but I couldn't find it easily right now (@anpete do you know?).

Besides that, there is a class of functions that only have a real meaning on the server, e.g. the HostName function we mentioned above, so it might make more sense to throw NotSupportedException for those, although throwing always makes simple testability scenarios a pain.

Re rule 3 (having Npgsql.EF.Functions.HostName() rather than EF.Functions.NpgsqlHostName()) that is a very valid point. What I really like about EF.Functions (and makes me still prefer it) is that it represents a single API entry point you need to learn about when using EF so it improves discoverability for all functions. But arguably it does make the names of the individual functions worse. Others in the thread might have thoughts on this.

[ErikEJ]

+1 for the EF.Functions! Great discoverability, and no need to learn a new namespace