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 queries #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.