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

[Daniel-Svensson]

Please consider making these methods (or at least most of them) work even at runtime if executed in a where statement for a mock database or in-memory one.

The old approach with throwing as in DbFunctions has forced us to write our own wrapper where we have to duplicate all methods we use such as TruncateTime.

+1 for trying to use BCL class calls instead of methods where possible so we for example might be able to write (DateTime.Now.Year - p.BirthDate.Year) instead of / as alternative to EF.Functions.DiffYear(p.BirthDate, DateTime.Now)

[xrkolovos]

Any news with this feature? We are also forced to write our own wrapper where we have to duplicate all methods we use such as TruncateTime.

[rowanmiller]

It's not something our team will be tackling before the 7.0.0 release (purely due to time constraints). We haven't done any planning of what order we will tackle features after that release yet. We will do that planning (and share it publically) closer to the 7.0.0 release.

[rjperes]

Any news on this?

[rowanmiller]

@rjperes Nothing as yet. We have some higher priority features on the backlog, so this probably wont be in the first round of things we implement.

[rjperes]

Thanks, Rowan! See you in November, hopefully with EF Core 1.1 out! ;-)

[SergeySagan]

I am also very interested in having this implemented.

[joshmouch]

I'd love to see TruncateTime implemented.

[divega]

@anpete @ajcvickers Thinking we may mark this as fixed in preview1. I believe we only have Like() there now but from now one we can track specific functions in individual issues. Thoughts?

[ajcvickers]

@divega Sounds good to me.

[divega]

Closing this as we have added the EF.Functions property of type DbFunctions as part of the implementation of the Like() method. Additional Core, Relational and provider specific functions can be mapped to extension methods for DbFunctions and we will use separate issues for those.

[ryanelian]

Can we have EF.Functions.Contains and EF.Functions.FreeText for performing full-text search against SQL Server?

https://docs.microsoft.com/en-us/sql/t-sql/queries/freetext-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql

[MikeDempseyFL]

I am trying to add an 'int Random(int, int)' function to EF.Functions but it looks like it is trying to evaluate it on the client rather than calling Random(int, int) on the DBS.
I assume this is because there are no objects from the model referenced in a call to Random.

I have added other database specific functions to EF.Functions and they work fine.
Those all take at least one object reference in their arguments.

Is there a way to tell EF to ALWAYS pass an extension function to the database?

Someone above mentioned HostName as a potential extension function for PostgreSql. That would have the same issue so was that done/solved?

(I understand there is a Random() function available in CLR but I need a random number generated for each row in the result set, so it must be executed on the database.)

[rwasef1830]

@MikeDempseyFL You can force EF to always send a function to the database by overriding the IEvaluatableExpressionFilter Entity Framework Core service.

In your filter class inherit from RelationalEvaluatableExpressionFilter and override the IsEvaluatableMethodCall method. It should return true if the method can be evaluated on the client and false if it should always be evaluated on the server.

[pmiddleton]

@MikeDempseyFL Are you implementing a provider or are you just using EF Core in a project?

[MikeDempseyFL]

I am implementing a provider. rwasef1830 pointed me to the solution using IEvaluatableExpressionFilter service. This works well. Thanks Mike

[SunflowerPKU]

@divega I do plan on submitting a pull request shortly. I am taking another look at the code to figure out what I can split out into separate PRs.

Hi @pmiddleton ,

I am a Ph.D. student. I am doing research about helping newcomers participate in OSS projects. I noticed that many projects are using labels such as 'good first issue/bug, help wanted' for issues to recommend that newcomers start from these tasks.

I noticed that you tried to solve this issue, but unfortunately, you had not contributed successfully. I also found there are many newcomers feel difficult when submitting their first pr. Therefore, I want to optimize this mechanism, which needs your help. I have some questions and wish your valuable feedback.

1. What problems did you meet when solving this issue?
2. Do you think it is appropriate to recommend newcomers to solve this issue? and why?
3. How do you think of the existing mechanism that adding certain labels to issues for helping newcomers participate in open source projects?

I am looking forward to hearing from you soon. Thank you very much!

[cyptus]

It is possible to make use of the SQL functions by wrapping it with the DbFunctionAttribute.
For example for the datepart function:
Tricky part here is to tell ef core not to handle the datepart type parameter as a string.

DbContext:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

Query:

repository.GroupBy(x => dbContext.DatePart("week", x.CreatedAt));

some more info: #10404

[onitecsoft]

for EF core 3 is little different solution:

modelBuilder.HasDbFunction(methodInfo, b => b.HasTranslation(e =>
{
	var ea = e.ToArray();
	var args = new[]
	{
		new SqlFragmentExpression((ea[0] as SqlConstantExpression).Value.ToString()),
		ea[1]
	};
	return SqlFunctionExpression.Create(nameof(DatePart), args, typeof(int?), null);
}));

[rangelier]

for EF core 3 is little different solution:

modelBuilder.HasDbFunction(methodInfo, b => b.HasTranslation(e =>
{
	var ea = e.ToArray();
	var args = new[]
	{
		new SqlFragmentExpression((ea[0] as SqlConstantExpression).Value.ToString()),
		ea[1]
	};
	return SqlFunctionExpression.Create(nameof(DatePart), args, typeof(int?), null);
}));

Tried this implementation on EF Core 3.1.3 but it doesn't work:

The LINQ expression 'DbSet<Reading>
    .Where(r => r.StructureId == __structureId_0)
    .Where(r => r.From >= __AddDays_1 && r.From <= __now_2)
    .GroupBy(
        source: r => __context_3.DatePart(
            datePartArg: "weekday", 
            date: (Nullable<DateTime>)r.From.DateTime), 
        keySelector: r => r)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Registered the function like this on the DB context:

var methodInfo = typeof(DataContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
            modelBuilder.HasDbFunction(methodInfo, b => b.HasTranslation(e =>
            {
                var ea = e.ToArray();
                var args = new[]
                {
                    new SqlFragmentExpression((ea[0] as SqlConstantExpression).Value.ToString()),ea[1] };
                return SqlFunctionExpression.Create(nameof(DatePart), args, typeof(int?), null);
            }));

[roji]

@rangelier can you please open a new issue and include a minimal, runnable code sample? The actual LINQ query is missing from the above.

[rangelier]

yes, need to some time to prepare though.

The original query looks like this:

 var weekResult = query.Where(c => c.From >= now.AddDays(-28) && c.From <= now)
                        .GroupBy(c => context.DatePart("weekday", c.From.DateTime))
                        .Select(c => new
                        {
                            Week = c.Key,
                            Total = query.Sum(c => c.Value)
                        });

[Astenna]

anything new on this issue?
tried the same solution as @rangelier mentioned, but it doesn't work.
I'm using EF Core 3.1.9

[cyptus]

this works fine for us, even on v3.1.9
please provide a minimal example that demonstrate your issue with this logic.

[marchy]

Closing this as we have added the EF.Functions property of type DbFunctions as part of the implementation of the Like() method. Additional Core, Relational and provider specific functions can be mapped to extension methods for DbFunctions and we will use separate issues for those.

@divega The repeatedly requested TruncateTime methods have not been implemented in DbFunctions, despite it being mentioned repeatedly above: #2850 (comment), #2850 (comment), #2850 (comment), #2850 (comment)

How can you compare a date to the start/end of the day?
(ie: previously achieved with TruncateTime in EF6)

This issue which explicitly requested the TruncateTime functions got marked as closed/dupe, even though the implementation did NOT resolve it. Can you please re-open so we don't have to re-file another one for the exact same thing?