Additional table expression

[Toni Wenzel]

Sometimes I want to add an additional table expression to my query in order to get the "WITH (READUNCOMMITED)" T-SQL option.

I thought something like this:

From a In Provider.Get(Of Address)().ReadUncommited()

Where a.ID = 1 OrElse a.ID = 5

Select a

The result SQL should look like:

SELECT * FROM [DEMOAddress] AS [t0] WITH (READUNCOMMITTED) WHERE (([t0].[ADID] = 1) OR ([t0].[ADID] = 5))

Does anyone have a clue how to achieve this?

[Michael Ketting]

Hi Toni!!

Don't have a design ready ATM, but we've spitballed those concepts ages ago: https://www.re-motion.org/jira/browse/RMLNQSQL-22

I think the easiest way to do this would be via introducing your own result operators and then extending the SqlTable and handling it in SqlStatementTextGenerator.BuildFromTable() or the respctive ISqlGenerationStage.

The Entity Framework guys also introduced annotions: https://github.com/aspnet/EntityFramework/blob/dev/src/Microsoft.EntityFrameworkCore/Query/ResultOperators/IQueryAnnotation.cs

Best regards, Michael

[Toni Wenzel]

I already know about ResultOperatorHandlers, but can you give me a hint how to create my own result operator and how to apply it to the query?

[Michael Ketting]

Sure thing. Fabian documented it here: https://www.re-motion.org/blogs/mix/2010/10/28/re-linq-extensibility-custom-query-operators/

[Toni Wenzel]

Thanks. I could complete my implementation.

There is just one issue open:

My query looks like:

From a In Provider.Get(Of Address)().ReadUncommitted(Function(s) s)

Where a.ID = 1 OrElse a.ID = 5

Select a

The extension method is:

<Extension>

Public Function ReadUncommitted(Of T)(ByVal source As IQueryable(Of T), ByVal obj As Expressions.Expression(Of Func(Of T, T))) As IQueryable(Of T)

Return CreateQuery(source, Function(s) s.ReadUncommitted(obj))

End Function

Private Function CreateQuery(Of T, TR)(ByVal source As IQueryable(Of T), ByVal expression As Expressions.Expression(Of Func(Of IQueryable(Of T), TR))) As IQueryable(Of T)

Dim newQueryExpression = ReplacingExpressionVisitor.Replace(

expression.Parameters(0),

source.Expression,

expression.Body

)

Return source.Provider.CreateQuery(Of T)(newQueryExpression)

End Function

This creates a (prepared) statement like:

SELECT TABLE-REF(q0) AS value FROM (SELECT TABLE-REF(UnresolvedTableInfoWithAddition(Address)) AS value FROM TABLE(Address)) [q0] WHERE ((TABLE-REF(q0).ID == 1) OrElse (TABLE-REF(q0).ID == 5))

My problem is I don't want to have this TableReference select clause ("q0").

I quess the problem is that I replaced the original source expression with my expression and so introduces a new query source.

How can I omit that? 

I just want to get my result operator called without changing or introducing any query source. Like you mentioned, I just want to annotate tables.

[Toni Wenzel]

I've change the extension method to do it like in EntityFramework:

<Extension>

Public Function ReadUncommitted(Of T)(ByVal source As IQueryable(Of T)) As IQueryable(Of T)

Return source.Provider.CreateQuery(Of T)(

Expression.Call(

Nothing,

ReadUncommittedMethodInfo.MakeGenericMethod(GetType(t)), source.Expression)

)

End Function

But the resulting queryModel still have a subquery.

[Toni Wenzel]

I had a look at the QueryAnnotation implementation of the EntityFramework. Thanks for pointing me into this direction.

Now I've implemented a similar approach (Using QueryAnnotationExtractor etc.). 

Thanks, it's working fine now.

[Michael Ketting]

Great to hear you found a way :) sorry I didn't get to this sooner :/ The subselects without actual needing them. Yeah, you might be right about the replaced soruce expressions. Maybe you are missing a QuerySourceMapping. Hard to say without actually setting up to running code.

Best regards, Michael