Page 50 - MSDN Magazine, September 2019

Figure 8 ExpressionTreeVisitor Replacing Visual Basic Like with DbFunctions.Like
You need to replace the original expression tree, which uses the Visual Basic Like, with one that uses DbFunctions.Like, but with- out changing any other parts of the tree. The common idiom for doing this is to inherit from the .NET ExpressionVisitor class, and override the Visit* base methods of interest. In my case, because I want to replace a method call, I’ll override VisitMethodCall, as shown in Figure 8.
The Like operator’s pattern syntax is different from that of SQL LIKE, so I’ll replace the special characters used in the Visual Basic Like with the corresponding ones used by SQL LIKE. (This map- ping is incomplete—it doesn’t map all the pattern syntax of the Visual Basic Like; and it doesn’t escape SQL LIKE special char- acters, or unescape Visual Basic Like special characters. A full implementation can be found on GitHub at bit.ly/2yku7tx, together with the C# version.)
Note that I can only replace these characters if the pattern is part of the expression tree, and that the expression node is a Constant. If the pattern is another expression type—such as the result of a method call, or the result of a BinaryExpression that concatenates two other strings—then the value of the pattern doesn’t exist until the expression has been evaluated.
I can now replace the expression with the rewritten one, and use the new expression in my query, like so:
Dim expr As Expression(Of Func(Of Person, Boolean)) = Function(x) x.FirstName Like "*e*i*"
Dim visitor As New LikeVisitor
expr = CType(visitor.Visit(expr), Expression(Of Func(Of Person, Boolean)))
Dim personSource As IQueryable(Of Person) = ... Dim qry = personSource.Where(expr)
For Each person In qry
Console.WriteLine($"LastName: {person.LastName}, FirstName: {person.FirstName}") Next
Ideally, this sort of transformation would be done within the LINQ to Entities provider, where the entire expression tree—which might include other expression trees and Queryable method calls—could be rewritten in one go, instead of having to rewrite each expression before passing it into the Queryable methods. But the transfor- mation at its core would be the same—some class or function that visits all the nodes and plugs in a replacement node where needed.
Wrapping Up
Expression trees model various code operations and can be used to expose APIs without requiring developers to learn a new language or vocabulary—the developer can leverage Visual Basic or C# to drive these APIs, while the compiler provides type-checking and syntax correctedness, and the IDE supplies Intellisense. A modified copy of an expression tree can be created, with added, removed, or replaced nodes. Expression trees can also be used for dynamically compiling code at runtime, and even for self-rewriting code; even as Roslyn is the preferred path for dynamic compilation.
Code samples for this article can be found at bit.ly/2yku7tx. n
Zev SpitZ has written a library for rendering expression trees as strings in multiple formats—C#, Visual Basic and factory method calls; and a Visual Studio debugging visualizer for expression trees.
thankS to the following Microsoft technical expert for reviewing this article: Kathleen Dollard
Class LikeVisitor
Inherits ExpressionVisitor
Shared LikeString As MethodInfo = GetType(CompilerServices.LikeOperator).GetMethod("LikeString")
Shared DbFunctionsLike As MethodInfo = GetType(DbFunctions).GetMethod( "Like", {GetType(String), GetType(String)})
Protected Overrides Function VisitMethodCall(
node As MethodCallExpression) As Expression
' Is this node using the LikeString method? If not, leave it alone. If node.Method <> LikeString Then Return MyBase.VisitMethodCall(node)
Dim patternExpression = node.Arguments(1)
If patternExpression.NodeType = ExpressionType.Constant Then
Dim oldPattern =
CType(CType(patternExpression, ConstantExpression).Value, String)
' partial mapping of Visual Basic's Like syntax to SQL LIKE syntax Dim newPattern = oldPattern.Replace("*", "%")
patternExpression = Constant(newPattern) End If
Return [Call](DbFunctionsLike, node.Arguments(0), patternExpression
)
End Function
End Class
you want to query a database for people whose first name contains an “e” and a subsequent “i.” Visual Basic has a Like operator, which returns True if a string matches against a pattern, as shown here:
Dim personSource As IQueryable(Of Person) = ...
Dim qry = personSource.Where(Function(x) x.FirstName Like "*e*i*") For Each person In qry
Console.WriteLine($"LastName: {person.LastName}, FirstName: {person.FirstName}") Next
But if you try this on an Entity Framework 6 DbContext, you’ll get an exception with the following message:
'LINQ to Entities does not recognize the method 'Boolean LikeString(System.String, System.String, Microsoft.VisualBasic. CompareMethod)' method, and this method cannot be translated into a store expression.'
The Visual Basic Like operator resolves to the LikeOperator.Like- String method (in the Microsoft.VisualBasic.CompilerServices namespace), which EF6 can’t translate into a SQL LIKE expres- sion. Thus, the error.
Now EF6 does support similar functionality via the DbFunc- tions.Like method, which EF6 can map to the corresponding LIKE.
More Information
• Expression Trees in the programming guides for Visual Basic (bit. ly/2Msocef) and C# (bit.ly/2Y9q5nj)
• Lambda expressions in the programming guides for Visual Basic (bit.ly/2YsZFs3) and C# (bit.ly/331ZWp5)
• Bart De Smet’s Expression Tree Futures project (bit.ly/2OrUsRw)
• DLR project on GitHub (bit.ly/2yssz0x) has documents describing
the design of expression trees in .NET
• Rendering expression trees as strings, and debugging visualizers
for expression trees—bit.ly/2MsoXnB and bit.ly/2GAp5ha
• “What does Expression.Quote() do that Expression.Constant()
can’t already do?” on StackOverflow (bit.ly/30YT6Pi)
42 msdn magazine
.NET Development