[Proposal] LinQ to entities inline functions

[VBAndCs]

I have this code:

cmbProject.AddItems((
  From b In db.Projects
  Let name = b.Name.ToLower
   Where b.FinishedDate Is Nothing AndAlso (
   name.StartsWith(Text) OrElse
   name.StartsWith("..." & Text) OrElse
   name.Contains(" " & Text) OrElse
   name.Contains(" ..." & Text) OrElse
   name.StartsWith("(" & Text) OrElse
   name.StartsWith("(..." & Text) OrElse
   name.Contains(" (" & Text) OrElse
   name.Contains(" (..." & Text)
  )
  Select b
  Take 20
 ).ToList.Select(Function(r) (CInt(r.ID), r.DisplayName))
)

The part

(
   name.StartsWith(Text) OrElse
   name.StartsWith("..." & Text) OrElse
   name.Contains(" " & Text) OrElse
   name.Contains(" ..." & Text) OrElse
   name.StartsWith("(" & Text) OrElse
   name.StartsWith("(..." & Text) OrElse
   name.Contains(" (" & Text) OrElse
   name.Contains(" (..." & Text)
)

is a criteria that I am using to select some names from the table. In fact I can complicate it more, but I am conceding performance. I can't use Regex (despite it will be compact) because it can't be translated to SQL. Worest, I need to repeat this criteria conditions with many other tables in different LinQ queries (about 10 queries). This is a lot of repeating that will make it hard to modify the criteria afterwards. The obvious solution is to but it in a function like this:

Function Filter(name As String, text As String) As Boolean
   Return name.StartsWith(text) OrElse
   name.StartsWith("..." & text) OrElse
   name.Contains(" " & text) OrElse
   name.Contains(" ..." & text) OrElse
   name.StartsWith("(" & text) OrElse
   name.StartsWith("(..." & text) OrElse
   name.Contains(" (" & text) OrElse
   name.Contains(" (..." & text)
End Function

which will reduce the linq query to:

cmbProject.AddItems((
  From b In db.Projects
  Where b.FinishedDate Is Nothing AndAlso Filter(b.Name.ToLower, Text)
  Take 20
 ).ToList.Select(Function(r) (CInt(r.ID), r.DisplayName))
)

Put this gives rruntime exception, because LimQ to Entities can't convert the Filter function to SQL! I tried to mark the Filter function with the <MethodImpl(MethodImplOptions.AggressiveInlining)> attribute, but still got the same error! So, it is obvious that the Filter function should can be considered as a part of the LinQ statement, and VB should inline it and translate its code to the expression tree. So, my suggestion, is that VB should checl the AggressiveInlining case when it is related to a function used in LinQ, so, the inlining happens at the VB compiler's level (not the JIT level) before generating the expression trees. Ir will be better if we use another attribute for this matter to avoid any confusion, say: <LinQInlined>

There will be rule on the code written in LinQ inlined functions so it can be converted to SQL, such as using only supported .NET methods.

[zspitz]

You can already work around this today. First, write a function with the appropriate behavior:

<Extension> Public Function StartsWithAny(s As String, ParamArray values() As String) As Boolean
    'For the purposes of expression trees, the implementation doesn't matter
End Function

then write your LINQ query to call that function:

Dim qry = 
    From b In db.Projects
    Where b.FinishedDate Is Nothing AndAlso b.Name.StartsWithAny(Text, "..." & Text, "(" & Text, "(..." & Text)
    Take 20

and then passing in the query's Expression into a visitor that would expand the expression tree generated from the following (currently unparseable by Entity Framework):

... b.Name.StartsWithAny(Text, "..." & Text, "(" & Text, "(..." & Text)

into the EF-parseable expression tree corresponding to this code:

... b.Name.StartsWith(Text) OrElse b.Name.StartsWith("..." & Text) OrElse b.Name.StartsWith("(" & Text) OrElse b.Name.StartsWith("(..." & Text)

Pass the resultant Expression into the IQueryable's provider (at the Provider property), calling CreateQuery, and you get back another IQueryable.

This can be done without making changes to the compiler, specifically, without requiring the compiler to parse the expressions within the function into an expression tree.

---

NB. LINQ is usually written in all-caps. If you must have some lower-case letters, at least follow the acronym -- LInQ, for Language Integrated Query.

[VBAndCs]

If I am looking for a workaround, then it easier to write a stored procedure to filter the names directly. It will need some work to use dynam9iic sql to work on different table names, but it will be the best solution for this particular case. I am reporting this as a common situation many ask about frequently in stack over flow, and should be fixed in VB and C#.

[jrmoreno1]

@zspitz : an IQueryable function should already work, no need for intermediate expressions.

[zspitz]

@jrmoreno1 Could you clarify what you mean by an IQueryable function?

Do you mean a function which returns an IQueryable? Because that is precisely how the standard LINQ methods work -- modify the source expression of the IQueryable (usually by wrapping it in a MethodCallExpression representing the method itself), and feed the modified expression back into the provider. I don't see any reason why additional methods couldn't perform similar transformations, using a visitor.

Or do you mean a function used within an expression? But the function itself isn't actually being called within the expression. The only aspect known to the expression is a MethodCallExpression representing the method call, not the internals of the function. Similar transformations are already taking place -- e.g. StartsWith and Contains become variants on SQL LIKE; it's only a matter of "teaching" the provider to recognize and handle certain methods and argument combinations. By the same token, why should I not also handle certain methods and argument combinations, transforming them to other expression nodes, before passing them to the provider?

Unless what's being proposed here is that when the compiler finds a MethodCallExpression within a compiler-generated expression tree, it would look at the Roslyn syntax tree for the method of the MethodCallExpression, convert that syntax tree to an expression tree, and inline this new expression tree into the original call location? Or look at the IL for the method and use that as the source for the inlined expression tree?

[zspitz]

@VBAndCs

If I am looking for a workaround, then it easier to write a stored procedure to filter the names directly. It will need some work to use dynam9iic sql to work on different table names, but it will be the best solution for this particular case.

It's obviously more straightforward to write SQL directly than to create an expression tree that maps to SQL. But you lose out on the benefits of leveraging the compiler, such as type correctedness and Intellisense.

And if you mean to create a .NET function which EF would translate the call within the expression tree to an invocation of the stored procedure, that is precisely what I am proposing, albeit a little earlier: translate the call to a specific function within the expression tree, into an expanded expression tree that EF can handle.

I am reporting this as a common situation many ask about frequently in stack over flow, and should be fixed in VB and C#.

What is common is that people don't understand that when you have a method call in the expression tree, the method is never being called (unless the expression tree is compiled to a delegate and invoked); for all intents and purposes the method doesn't have to do anything -- it could throw a NotImplementedException. The provider translates the method call into something else; it's relatively trivial for us to do the same.

[hartmair]

This issue should be moved to EF repo, right? (AggressiveInlining and IQueryable to SQL is nothing special to VB)

[VBAndCs]

Inlining functions in SQL is a compiler job, as it can be used in LinQ to SQl, EF6, EF Core, or any other tech to come. I reported an error before in EFCore regarding an issue in expression tree, and they pointed me back here, since every compiler generate its expression tree. Besides, what EF can do with the source VB code containing the function to inline? Anyway, there should be a C# version of this proposal as well, since the 2 langs suffer the same issue.