Custom function(s) in LINQ to Entities? How to write acceptable code?

Question

Use of my simple function causes app to exit all the nested Using blocks and returns control to very outer End Using statement. But built-in function works fine. How to bypass this strange situation?

Public Shared ReadOnly GlobalSettingsKeyList As New HashSet(Of String)

Public Shared Function IsGlobalSetting(key As String) As Boolean
    Return GlobalSettingsKeyList.Contains(key)
End Function

What doesn't work:

Using db = powerEntities.Open()
    dim userID = 1
    dim dbSettingsFound = (From setting In db.SETTINGS
                           Where setting.idUsers = If(IsGlobalSetting(setting.Name), Nothing, userID)
                           Where setting.Name.Contains(keyToMatch)) _
                        .ToDictionary(Function(x) x.Name, Function(y) y.Value)
End Using

What works fine:

Using db = powerEntities.Open()
    dim userID = 1
    dim dbSettingsFound = (From setting In db.SETTINGS
                           Where setting.idUsers = If(GlobalSettingsKeyList.Contains(setting.Name), Nothing, userID)
                           Where setting.Name.Contains(keyToMatch)) _
                        .ToDictionary(Function(x) x.Name, Function(y) y.Value)
End Using

{"LINQ to Entities does not recognize the method 'Boolean IsGlobalSetting(System.String)' method, and this method cannot be translated into a store expression."}

Try putting a Try/Catch block around it to see if it's throwing an exception — Steven Doggart, Aug 29 '19 at 13:17
{"LINQ to Entities does not recognize the method 'System.String FormatDefaultKey(System.String)' method, and this method cannot be translated into a store expression."} == same situation in other, simple function — Damian K., Aug 29 '19 at 13:47
Within your LINQ to Entities query, you can only use syntax which the EF is capable of converting into a native DB query. While the variety of syntax that it is supports is impressive, it does not support any/all legal .NET code. Just because it compiles as valid VB code doesn't mean that EF will be capable of parsing it and transforming it into a DB query. In other words, in this case, it knows how to convert a `Dictionary.Contains` call into a DB query, but it doesn't know how to parse your `IsGlobalSetting` method. — Steven Doggart, Aug 29 '19 at 13:54
Sad and funny... my function contains only `Return GlobalSettingsKeyList.Contains(key)` — Damian K., Aug 29 '19 at 14:04
Right, but the syntax in the Where clause is, essentially, a lambda expression. It's passed into the LINQ to Entities framework as an `Expression`. Only lambdas can be passed as expressions. Expressions are essentially compiled to and passed as a syntax tree rather than a fully compiled function. As such, the framework is able to parse that tree and analyze the actual code in your where clause at run-time. So, when it looks at your expression, it can see that it contains a call to another method, but it can't deconstruct that method, because that's not inline in the lambda. — Steven Doggart, Aug 29 '19 at 14:33
(I'm going beyond my expertise comfort zone a bit here, so I won't be surprised if my explanation isn't precise in every sense, but I'm pretty sure it's sufficiently accurate for this conversation. However, if anyone who knows more about the inner workings of the framework and compiler, and specifically how it handles lambdas and expressions, wants to speak up, I'd love to hear corrections on anything I stated clumsily) — Steven Doggart, Aug 29 '19 at 14:39
Possible duplicate of [LINQ to Entities does not recognize the method](https://stackoverflow.com/questions/7259567/linq-to-entities-does-not-recognize-the-method) — ItsPete, Aug 29 '19 at 23:41
Following is also a problem `Delegate Function Example(s As String) As Boolean` `Dim IsGlobalSetting As Example = Function(k) Return GlobalSettingsKeyList.Contains(k) End Function` {"The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."} — Damian K., Aug 30 '19 at 04:14

Damian K. · Accepted Answer · 2019-08-30T11:47:34.340

Done! Thanks to Jarekczek's answer here and his LinqExprHelper

So there is a way!

Private Shared Function GetDefaultKey(key As String) As String
        If key.Contains("Station") Then
            Return $"default{key.Substring("Station")}" 'my String Extension
        Else
            Return $"default{key}"
        End If
End Function

Public Shared Function GetDefaultKeyAndCompareSetting(ByVal key As String) As Expression(Of Func(Of Settings, Boolean))
        key = GetDefaultKey(key)
        Return LinqExprHelper.NewExpr(Function(u As Settings) u.Name.Equals(key))
End Function

used like this...

Shared Sub Example(key As String)
...
    Dim masterExpr = LinqExprHelper.NewExpr(Function(u As Settings, ByVal formatCompare As String) (formatCompare))
    Dim isSameAsDefKey = masterExpr.ReplacePar("formatCompare", GetDefaultKeyAndCompareSetting(key).Body)

    Dim result = (From def In db.Settings
                  Where def.idUsers Is Nothing).
                  Where(CType(isSameAsDefKey, Expression(Of Func(Of Settings, Boolean)))).FirstOrDefault
...
End Sub

...works like a charm

Custom function(s) in LINQ to Entities? How to write acceptable code?

1 Answers1