Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
roji opened this issue Aug 15, 2015 · 47 comments

Comments

Projects
None yet
@roji
Copy link
Member

commented Aug 15, 2015

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

This comment has been minimized.

@rowanmiller rowanmiller added this to the Discussions milestone Aug 18, 2015

@divega

This comment has been minimized.

Copy link
Member

commented Aug 19, 2015

@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

This comment has been minimized.

Copy link
Member

commented Aug 19, 2015

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.

@divega divega removed this from the Discussions milestone Aug 19, 2015

@divega divega removed their assignment Aug 19, 2015

@rowanmiller rowanmiller added this to the Backlog milestone Aug 21, 2015

@roji

This comment has been minimized.

Copy link
Member Author

commented Aug 24, 2015

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

This comment has been minimized.

Copy link
Member Author

commented Aug 24, 2015

@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

This comment has been minimized.

Copy link
Member

commented Aug 25, 2015

@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

This comment has been minimized.

Copy link
Contributor

commented Aug 25, 2015

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

@rowanmiller rowanmiller changed the title Provide standard LINQ methods that correspond to standard SQL operations Provide standard LINQ methods that correspond to standard SQL operations (something like DbFunctions from EF6) Nov 3, 2015

@Daniel-Svensson

This comment has been minimized.

Copy link

commented Nov 4, 2015

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

This comment has been minimized.

Copy link

commented Dec 18, 2015

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

This comment has been minimized.

Copy link
Member

commented Dec 22, 2015

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

This comment has been minimized.

Copy link

commented Jul 4, 2016

Any news on this?

@rowanmiller

This comment has been minimized.

Copy link
Member

commented Jul 5, 2016

@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

This comment has been minimized.

Copy link

commented Jul 5, 2016

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

@Serjster

This comment has been minimized.

Copy link

commented Nov 7, 2016

I am also very interested in having this implemented.

@joshmouch

This comment has been minimized.

Copy link

commented Nov 8, 2016

I'd love to see TruncateTime implemented.

@pmiddleton

This comment has been minimized.

Copy link
Contributor

commented Feb 24, 2017

@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.

@divega

This comment has been minimized.

Copy link
Member

commented Feb 24, 2017

Great to hear @pmiddleton! Ilooking forward to it!

If the split isn't obvious feel free to send the whole set of changes as a PR and we can help find ways to separate it. FWIW, reading your previous comments it sounds like you could at least do a PR for adding EF.Functions and some simple function (very similar to what @roji did?) and then another one that contains the rest of your changes. Or perhaps we could take @roji's PR and then you could do a PR rebased on that? Whatever works best.

@jemiller0

This comment has been minimized.

Copy link

commented Mar 3, 2017

I would very much like to see standard methods for performing full-text searches for those databases that support them. I.e. it would be nice if the function calls were the same across SQL Server, PostgreSQL, MySQL, etc. and not have one method with one syntax in one provider and another syntax in another. In my case, I would mainly just like a way to do a substring search that is efficient using an index. I don't really even need word breaking and what not. Just an efficient way to do a substring search.

@roji

This comment has been minimized.

Copy link
Member Author

commented Mar 4, 2017

@jemiller0, full-text searching specifically probably deserves its own issue and discussion. The most important question is obviously to what extend the full-text searching capabilities of different databases overlap, similar to the spatial support discussion.

Obviously if you just want to do a substring search, you don't necessary need "full-text search" (String.Substring() should be mapped to SQL for all providers). The interesting questions begin with the more advanced capabilities.

@jemiller0

This comment has been minimized.

Copy link

commented Mar 4, 2017

What I would like is a Contains() search, but, one that operates in an efficient manner. I.e. doesn't have to do a brute force search/table scan. I'm assuming that would need a full-text index. I don't know how that works internally, just that it's functionality that would be useful for the applications I've been developing. I've been assuming I would need to use full-text search for that. I'm actually kind of surprised that SQL databases in general don't have a way of handling this. I.e. an efficient way to do a LIKE '%value%' search. For my purposes, I think I would really even need work breaking and all that. I just need to be able to search for literal text as is. Or, it seems that full-text search is part of the SQL standard.

@roji

This comment has been minimized.

Copy link
Member Author

commented Mar 4, 2017

@jemiller0, I think this really belongs in an issue of its own, where we'd discuss the least common (but still useful) denominator of database full-text search. This issue is already far too overloaded for that.

@roji

This comment has been minimized.

Copy link
Member Author

commented Mar 4, 2017

One last note re full text search - this would probably go beyond simply providing querying capabilities (i.e. providing something like DbFunctions), since the proper indices must be set up for efficient searching.

@jemiller0

This comment has been minimized.

Copy link

commented Mar 5, 2017

@roji No problem. I was just making a comment. Not really asking for anything concrete on this issue specifically. Just saying that I've been hoping for something like that for years. Unfortunately, completely re-writing EF has slowed down developing more advanced features. But hopefully now that EF Core is nearing a usable state, things like that could be tackled.

roji added a commit to roji/EntityFrameworkCore that referenced this issue Mar 8, 2017

Add empty EF.Functions
To allow relational and providers to start defining extension methods on it.

Modify SqlTranslatingExpressionVisitor to recognize the EFFunctions class and
translate methods on it.

Relates to aspnet#2850

roji added a commit to roji/EntityFrameworkCore that referenced this issue Mar 8, 2017

Implement EF.Functions.Like
Both with and without an escape character.

Relates to aspnet#2850

roji added a commit to roji/EntityFrameworkCore that referenced this issue Mar 25, 2017

Add empty EF.Functions
To allow relational and providers to start defining extension methods on it.

Modify SqlTranslatingExpressionVisitor to recognize the EFFunctions class and
translate methods on it.

Relates to aspnet#2850

roji added a commit to roji/EntityFrameworkCore that referenced this issue Mar 25, 2017

Implement EF.Functions.Like
Both with and without an escape character.

Relates to aspnet#2850
@divega

This comment has been minimized.

Copy link
Member

commented May 9, 2017

@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

This comment has been minimized.

Copy link
Member

commented May 9, 2017

@divega Sounds good to me.

@divega divega changed the title Provide standard LINQ methods that correspond to standard SQL operations (something like DbFunctions from EF6) Query: Provide DbFunctions class to define methods that correspond to standard SQL operations May 10, 2017

@divega divega added the closed-fixed label May 10, 2017

@divega divega closed this May 10, 2017

@divega divega modified the milestones: 2.0.0-preview1, Backlog May 10, 2017

@divega

This comment has been minimized.

Copy link
Member

commented May 10, 2017

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 divega changed the title Query: Provide DbFunctions class to define methods that correspond to standard SQL operations Query: Provide EF.Functions to define methods that correspond to standard SQL operations May 10, 2017

@ryanelian

This comment has been minimized.

Copy link

commented May 23, 2017

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

This comment has been minimized.

Copy link

commented Jul 2, 2018

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

This comment has been minimized.

Copy link

commented Jul 2, 2018

@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

This comment has been minimized.

Copy link
Contributor

commented Jul 2, 2018

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

@MikeDempseyFL

This comment has been minimized.

Copy link

commented Jul 2, 2018

@SunflowerPKU

This comment has been minimized.

Copy link

commented May 31, 2019

@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

This comment has been minimized.

Copy link

commented Jun 7, 2019

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.