Using re-linq to extend LINQ and modify resulting T-SQL

[Mikael Ørseng]

Hello,

I discovered the (wonderful) world of remotion linq only a couple of weeks ago when searching for possible solutions on how to support SQLserver temporal table queries.

I'm working with EF.CORE against SQLserver 2017, and simply want to write LINQ queries like this:

dbContext.MyEntity.Where(x => x.This == That).ForSystemTime(historicDateTime).OrderBy(o => o.Something);

and then get the ForSystemTime() translated into the resulting T-SQL:

SELECT ... FROM MyEntity FOR SYSTEM_TIME AS OF historicDateTime WHERE ... ORDER BY ...

I've been reading quite a lot of posts in various places the last weeks, amongst others these:

https://www.re-motion.org/blogs/mix/2010/10/28/re-linq-extensibility-custom-query-operators/

https://github.com/aspnet/EntityFrameworkCore/issues/5845

Also read a lot of posts here in this forum on similar topics with different variations. From what I read, what I want to implement should be quite easy, and hence the bigger is my frustration when I can't reach to the finish line ;)

Since I'm not implementing a new SQL function or the like, but really want support for a brand new SQL clause, I've choosen to go down path #3 in this post:

https://groups.google.com/forum/#!topic/re-motion-users/VBlXrOHOsRg

Based mainly on Fabian's post from 2010 (above) I didn't have any trouble neither to extend LINQ itself or create the support classes for expression parsing:

public static IQueryable<T> ForSystemTime<T>(this IQueryable<T> source, DateTimeOffset systemTime) { ... }
class ForSystemTimeResultOperator : SequenceTypePreservingResultOperatorBase { ... }
public class ForSystemTimeExpressionNode : ResultOperatorExpressionNodeBase { ... }

Then it comes to registering the expression node, and here I'm really, really stuck. I can't for my bare life or anything else get this to work. As I understand the last part of Fabian's post describing the registration is outdated, and I have instead found this post describing to to create a customized query parser:

https://www.re-motion.org/jira/browse/RM-3721

So far so good; I have used this code sample to create the query parser and register my expression node with it, but I'm unable to insert the customized query parser into the services collection. Here is my last attempt (on which I have iterated numerous different approaches, all unsiccessful) on creating my DB context:

public static MyDbContext GetDbContext()
{
   var services = new ServiceCollection();
   services.AddEntityFrameworkSqlServer();
   services.AddScoped<IQueryParser, MyCustomizedQueryParser>();

   var provider = services.BuildServiceProvider();

   var config = new DbContextOptionsBuilder<MyDbContext>();
   config.UseInternalServiceProvider(provider);
   config.UseSqlServer(connection);

   var db = new MyDbContext(config.Options);

   return db;
}

However, my customized query parser is totally ignored, not matter what approach I try.

Maybe I'm barking up the completely wrong tree here (or not even a tree...), so now I'm asking for any input that can take me further down the road. I will also appreciate any comments on the initial approach for implementing this (i.e. chosing #3 above) and whether I should have started in a completely different direction.

[Michael Ketting]

Hello Mikael,

welcome to the fascinating world of linq and well done on your research!

You're correct in your conclusion that the actual compositon of the QueryParser isn't actually a Remotion.Linq topic but baked into EntityFramework. As far as I can tell form the source code, the IQueryParser interface isn't resolved via IoC but only instantiated as QueryParser in the EF QueryModelGenerator. I'm not sure why you extended QueryParser but I'm assuming you are trying to get the parser to use your ForSystemTimeExpressionNode? For this, you only need to register/wire-up your ExpressionNode and the match logic in the INodeTypeProvider implementation. EF has the INodeTypeProviderFactory interface for this, injected into the EF QueryModelGenerator and this type and the INodeTypeProviderFactory really resolved via IoC. So, if you hook up a custom instance of the DefaultMethodInfoBasedNodeTypeRegistryFactory and register your ForSystemTimeExpressionNode with the  factory, you should be ready to go.

Hope that helps, Michael

[Mikael Ørseng]

Hello Michael,

thanks for your reply.

Yes, the choice of extending the QueryParser was made solely to inject the ForSystemTimeExpressionNode. I'll throw that code out, and hook up with the DefaultMethodInfoBasedNodeTypeRegistryFactory instead.

Thanks, Mikael

[Mikael Ørseng]

Hello Michael,

From your advice I've come quite a bit further down the road. As it stands now, I'm "almost" there, happily sitting and staring at a "NotImplemented" exception:

System.NotImplementedException
  HResult=0x80004001
  Message=TemporalQuery.ForSystemTimeResultOperator
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.ResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalResultOperatorHandler.HandlerContext.EvalOnClient(Boolean requiresClientResultOperator)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalResultOperatorHandler.HandleResultOperator(EntityQueryModelVisitor entityQueryModelVisitor, ResultOperatorBase resultOperator, QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitResultOperator(ResultOperatorBase resultOperator, QueryModel queryModel, Int32 index)
   at Remotion.Linq.QueryModelVisitorBase.VisitResultOperators(ObservableCollection´1 resultOperators, QueryModel queryModel)
   at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger´1 logger, Type contextType)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_0´1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func´1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func´1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Remotion.Linq.QueryableBase´1.GetEnumerator()
   at TemporalQuery.Program.QueryAgreements() in C:\Users\mio\source\repos\TemporalQuery\TemporalQuery\Program.cs:line 26
   at TemporalQuery.Program.Main(String[] args) in C:\Users\mio\source\repos\TemporalQuery\TemporalQuery\Program.cs:line 11

My expression node provider now runs as expected, and I'm quite happy about the "NotImplemented" exception because I guess this is only a result of the EF back-end for SQLServer not knowing about my extension and how to resolve it into T-SQL. Naturally!

So, from one of Fabian's blogs I have this, and I was confident that just tapping into this chain with my own override on the VisitResultOperator method would by quite easy to accomplish:

4. Add code handling the result operator to your LINQ provider back-end

Now, this is simple again. Using the registered expression node parser, re-linq now detects the extension methods and creates a result operator for it. You can inspect a QueryModel’s result operators via the ResultOperators collection. If you have a QueryModelVisitor, just override the VisitResultOperator method and check the result operator’s type.

But again I'm at a loss on how to inject my own QueryModelVisitor.  I've spent the day trying to inject various classes and interfaces, but none of these are ever invoked:

var services = new ServiceCollection();
services.AddEntityFrameworkSqlServer();

services.AddSingleton<INodeTypeProviderFactory, MethodInfoBasedNodeTypeRegistryFactoryForTemporalQueries>();
//services.AddScoped<IQueryModelVisitor, EntityQueryModelVisitorForTemporalQueries>();
//services.AddScoped<QueryModelVisitorBase, EntityQueryModelVisitorForTemporalQueries>();
//services.AddScoped<EntityQueryModelVisitor, EFEntityQueryModelVisitorForTemporalQueries>();
//services.AddSingleton<EntityQueryModelVisitorFactory, EFEntityQueryModelVisitorForTemporalQueries>();
var provider = services.BuildServiceProvider();

The commented lines represents the different approaches. Now, from this point and with your previous post in mind, I suspect that again EF does not use IoC for this and istead instantiates its own EntityQueryModelVisitor directly.

I'll continue fiddling on this to try find a solution, but in the meantime if you have any pointers to a solution please let me know.

Thanks again,

Mikael

[Mikael Ørseng]

Update:

After som digging I found that I can inject an IResultOperatorHandler to intercept handling of my custom operator:

services.AddScoped<IResultOperatorHandler, ResultOperatorHandlerForTemporalQueries>();

With in place, if I simply return the incomming expression the query now runs (of course without the required T-SQL addition).

I'll continue today looking at how to intercept the T-SQL generation and how to modify it.

Mikael

[Michael Ketting]

Hello Mikael,

I have to appologize, I was logged out of google groups and didn't notice the update.

Regarding the EntityQueryModelVisitor:
That's instantiated via the RelationalQueryModelVisitorFactory (registered as IEntityQueryModelVisitorFactory) as RelationalQueryModelVisitor.

Glad to hear your got the IRelationalResultOperatorHandler :) Did you solve the SQL generation?

Best regards, Michael

[Mikael Ørseng]

Hello Michael,

Please don't, I've also been away for a long time. Short after my last post above I was moved to another project and had to (temporarily) abandon this task.

Funny that you picked up the thread again just now, since I'm back in the original project from this week :)

When I stopped working on this back in late January I had not yet figured out how to tap into the SQL generation for the SQL-Server provider. I'll to try to pick up the loose ends now to get back into the loop where I left off.

If you have any immediate tips on the SQL generation and where to hook up my custom "SQL injector" please let me now.

Kind regards, Mikael

[Michael Ketting]

Hi Mikael,
I guess that would define 'serendipitious' :D
Sorry, no immediate ideas regarding the SQL generation. Would need to open up EF and dig into the sources since that part's pretty much completely done inside EF, including the extension points. Just send me a ping if you get stuck, though :)
Best regards, Michael

[Mikael Ørseng]

Hi Michael,

Just stopping by this time to let you know that we finally have a working solution :)

We've extended (sub-classed) the SqlServerQuerySqlGenerator , and tapped into VisitSelect and VisitTable to inject FOR SYSTEM_TIME clauses into the generated SQL. Also using IQueryAnnotation interface to pass query parameters from our LINQ extension methods down the pipeline to SqlServerQuerySqlGenerator .

Some important details that took us the last steps towards a working solution was found is this article: https://www.shadesofazure.com/sql-generation-ef-core/ . Many thanks to Jesse Barocio for this one.

Thank you very much Michael for your guidance along the way.

Best regards, Mikael

[Michael Ketting]

Cool! Thanks for the update :)

Best regards, Michael