Additional table expression

15 views
Skip to first unread message

Toni Wenzel

unread,
Feb 11, 2016, 2:08:11 AM2/11/16
to re-motion Users
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

unread,
Feb 11, 2016, 2:24:25 AM2/11/16
to re-motion Users
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

unread,
Feb 11, 2016, 2:44:11 AM2/11/16
to re-motion Users
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

unread,
Feb 11, 2016, 4:37:53 AM2/11/16
to re-motion Users

Toni Wenzel

unread,
Feb 12, 2016, 2:02:24 AM2/12/16
to re-motion Users
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

unread,
Feb 12, 2016, 3:22:00 AM2/12/16
to re-motion Users
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

unread,
Feb 15, 2016, 4:27:14 AM2/15/16
to re-motion Users
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

unread,
Feb 15, 2016, 6:04:33 AM2/15/16
to re-motion Users
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
Reply all
Reply to author
Forward
0 new messages