Column expressions
15 views
Skip to first unread message
Toni Wenzel
Feb 4, 2016, 4:43:49 AM2/4/16
to re-motion Users
Hi,
every time one particular column (e.g. User.Image) will be selected I want to create a SELECT clause like "CONVERT(varchar(max), [t0].[Image])".
Or if the SQL data type is "Geography" I want to select those columns like "nullif(Concat(LTRIM(STR({0}.Lat, 20, 8)), ';', LTRIM(STR({0}.Long, 20, 8))), ';')" in order to retrieve values like "47.7964221;9.6251987".
How can I manage that?
Toni Wenzel
Feb 4, 2016, 5:06:43 AM2/4/16
to re-motion Users
I've created a new SqlColumnExpressionExpression : SqlColumnExpression type
and did a little dirty stuff in the Accept method
Protected Overrides Function Accept(visitor As ExpressionVisitor) As Expression
Dim sqlColumnExpressionVisitor As SqlGeneratingExpressionVisitor = TryCast(visitor, SqlGeneratingExpressionVisitor)
If (sqlColumnExpressionVisitor IsNot Nothing) Then
Dim commandBuilder As ISqlCommandBuilder = sqlColumnExpressionVisitor.GetPrivatePropertyValue(Of ISqlCommandBuilder)("CommandBuilder")
commandBuilder.Append(myexpression)
Return Me
End If
Return MyBase.Accept(visitor)
End Function
This SqlColumnExpressionExpression is used in our MappingResolver when creating a new SqlEntityDefinitionExpression.
Do you have a better approach?
Michael Ketting
Feb 4, 2016, 9:20:01 AM2/4/16
to re-motion Users
Hi Toni
Hmmm... sounds like you want to do something similiar to SqlBackend's SqlConvertedBooleanExpression. Since a Boolean is represented differently in SQL depending on where it is used (i.e. in a select clause or inside a condition, etc), we have to wrap boolean expressions and use a ConvertExpression in some places.
The magic for this happens via the ISqlConvertedBooleanExpressionVisitor and SqlConvertedBooleanExpression. You can extend the visitors (the SqlGeneratingOuterSelectExpressionVisitor and possiblity SqlGeneratingExpressionVisitor) by deriving and updating the callsite (DefaultSqlGenerationStage) to handle the new expression type. And you'll need to decide if the special syntax is only warrented on the outer-most projection or also deeper in the tree.
There is also a completely different option: Transformations: https://www.re-motion.org/blogs/mix/2011/04/29/re-linq-customizability-explained/
You can register transformations for different expression types and since you've already provided your own expression type, that could be a way to do this. You'd get the source expression and return an expression tree that generates all the stuff you need via SqlFunctionExpression, SqlLiteralExpression, etc.
Sidenote about a specific extension point to add sql features not natively supported in the backend: Projects have added extensions for date-time operations for instance via this mechanism (AttributeEvaluatingExpressionTransformer ) There's also a sample in the unit tests for the SqlBackend: FullNameEqualsTransformerAttribute. This is of course only feasible, when you can add a specfic method call every time you need the feature in the query.
Can't really say which one's better, depends on the details. Generally, the transformations are for when you just need to turn something simple into a more complex structure with existing syntactic elements.
Best regards, Michael
Hmmm... sounds like you want to do something similiar to SqlBackend's SqlConvertedBooleanExpression. Since a Boolean is represented differently in SQL depending on where it is used (i.e. in a select clause or inside a condition, etc), we have to wrap boolean expressions and use a ConvertExpression in some places.
The magic for this happens via the ISqlConvertedBooleanExpressionVisitor and SqlConvertedBooleanExpression. You can extend the visitors (the SqlGeneratingOuterSelectExpressionVisitor and possiblity SqlGeneratingExpressionVisitor) by deriving and updating the callsite (DefaultSqlGenerationStage) to handle the new expression type. And you'll need to decide if the special syntax is only warrented on the outer-most projection or also deeper in the tree.
There is also a completely different option: Transformations: https://www.re-motion.org/blogs/mix/2011/04/29/re-linq-customizability-explained/
You can register transformations for different expression types and since you've already provided your own expression type, that could be a way to do this. You'd get the source expression and return an expression tree that generates all the stuff you need via SqlFunctionExpression, SqlLiteralExpression, etc.
Sidenote about a specific extension point to add sql features not natively supported in the backend: Projects have added extensions for date-time operations for instance via this mechanism (AttributeEvaluatingExpressionTransformer ) There's also a sample in the unit tests for the SqlBackend: FullNameEqualsTransformerAttribute. This is of course only feasible, when you can add a specfic method call every time you need the feature in the query.
Can't really say which one's better, depends on the details. Generally, the transformations are for when you just need to turn something simple into a more complex structure with existing syntactic elements.
Best regards, Michael
Toni Wenzel
Feb 5, 2016, 2:14:08 AM2/5/16
to re-motion Users
Hi Michael,
I've extend the SqlGeneratingOuterSelectExpressionVisitor with an additional interface which I'm calling in my SqlColumnExpression class.
Thank you for your help!
Reply all
Reply to author
Forward
0 new messages