Query: Additional function mapping capabilities

[rowanmiller]

This would allow functions in the database to be used in LINQ queries. This includes:

• Collection-valued composable functions, such as TVFs in SQL Server (Ability to map a CLR method returning queryable to TVF #20051)
• Composable functions with table valued parameters (Feature Request: LINQ to table parameter #13239)
  • Support queries with complex expressions in parameters (Add complex expression support for DbFunction parameters #20093)
• Scalar composable functions (addressed by Query: Add support for User Defined Scalar Functions #7368)
• Scalar collection composable functions (DbFunctions: self-bootstrapping functions #9810, Support scalar query roots #20363)
• User-defined aggregate functions (Allow DbFunction to return scalar value from aggregates #11850, Map DbFunction to aggregate #20052)
• Support defining user methods that get translated to LINQ in queryies #31871

We could support a number of patterns for this:

• Map functions to CLR methods (addressed by DBFunctions - Add support for instance methods. #9755)
  • Using fluent API
  • Using an attribute
• Ad-hoc invocation of SQL fragment in LINQ queries
• Map entity types to functions without a stand-in method
  • Support complex entity type to function mapping (Support flexible entity type to function mapping #19970)
  • Map properties to functions, e.g. enable entities to have calculated properties that evaluate to calling a function with the entity key as the argument
  • Map navigations to functions

Once database functions can be mapped to methods, we could accept those methods in other places besides LINQ queries, e.g. to specify default values, in set based updates (once we have the feature), etc.

[divega]

Renaming since scalar functions mapping support has been added in 2.0 preview 2. Some of the remaining scenarios listed in this issue could be easier to tackle based on the work done in #7368.

cc @pmiddleton @anpete

[pmiddleton]

I am going to start looking into TVF next. I had a mostly working version back in January which I am going to revisit.

User-defined aggregate functions have the same syntax as scalar functions so they "should just work". 🙈 I can give it a try this weekend to verify.

[pmiddleton]

I looked at using the existing UDF support for aggregates over the long weekend. Everything does work when the full query can be translated. The main issue is that without group by translation support the aggregate is lost.

Aggregates can still be used in queries without group by, but the usefulness of that will be rather limited.

Once group by translation is added we should be good to go without any modifications needed.

[divega]

@pmiddleton curious if you are still looking at TVFs and other things you were planning to look into. If there is anything we can help with let us know.

[pmiddleton]

@divega - yes I have been working on those items.

I have the following things currently working.

• Bootstrapable Scalar Functions
• Bootstrapable TVFs
• Nestable DbFunction calls on both types of bootstrapped methods (pass a function as a parameter to the bootstrapped function)
• Full query support for TVFs via Cross Join
• TVF integration with Query Types. You can either directly materialize the QT from the TVF, or use it as part of a larger query.

I have started on Outer Apply for TVFs. The Linq syntax is in place and valid Sql is being produce, but it is currently not optimal as it is using a cross apply with a subquery. I need to replace that with an Outer Apply directly to the TVF.

Things are finally settling down at work (we just had the release of a 3 year long project) so I have had more time to work on this at night.

I can create a wip pr tomorrow so the team can see what I have going on. I still need finish the outer apply, clean up the code, and add more unit tests.

I believe that the changes I had to make are going to introduce too many breaking changes for 2.1, but I will let the team make that determination.

Let me know what you guys think once you see it.

[aaronhudon]

@pmiddleton Can you suggest the current workaround in 2.1 on how to call a TVF (with parameters)?

[pmiddleton]

@aaronhudon - Your only options in 2.1 will be running raw sql using either FromSql or Database.Query.

[aaronhudon]

@pmiddleton Thanks. I'm doing this now. Since this causes a subquery like `SELECT * FROM TVF...`` the query plan performance is like 99% worse than selecting directly from the TVF.

[pmiddleton]

@aaronhudon - I'm not understanding where the subquery is coming from. Did you build the whole query yourself or is part of it being generated by EF? If so can you just build the whole query yourself to avoid where this subquery is coming from.

[aaronhudon]

Unfortunately, yes, I'll have to write the entire SQL instead of taking advantage of the nice LINQ syntax for the where clauses (and potentially joins to other entity objects)

The subquery comes from my DbContext exposing an IQueryable<T> method like

internal DbQuery<Foo> FooTvf {get;set;}

public IQueryable<Foo> Get(DateTime param)
{
    return FooTvf.FromSql($"SELECT * FROM tvf_xxxx({param})");
}

Then using this Get method from the dbContext:
dbContext.Get(someDate).Where(x => x.a == blah)

The resulting query looks something like:

SELECT m.a, m.b, m.c, m.d
FROM (
    SELECT * FROM tvf_xxx(param)
) as m
WHERE m.a = [query params inserted here]

[pmiddleton]

How often are you composing on your Get method? If you are just tacking on that same where clause then you can move the whole query into the FromSql call. If you are composing a lot of different queries then you are stuck for now.

[aaronhudon]

I'm stuck for now :( I'll watch this thread (?) for updates on support for TVFs

[pmiddleton]

yes. I don't know if tvf will make 3.0. The team is rewriting the query pipeline so it's going to be based on when that gets to stable point and my free time to work on updating my existing PR.

[aaronhudon]

@pmiddleton Rewrite? :-/ which thread has the query pipeline rewrite discussion?

[MartinJohns]

@pmiddleton Is there any update on this? Could you please link the issue @aaronhudon asked about?

[pmiddleton]

@MartinJohns - See my response on #11129

[jscarle]

Related: #33788