EF Core, LINQ Operator '=' in VB.NET

Question

I have the next Code in EF Core 3.1 in language VB.NET

Dim supplierID as string="1545464"
Dim results = (From pa In DC.product.AsNoTracking()
                            Where pa.supplierID = supplierID
                            Select pa)

The exception throw is:

The LINQ expression 'DbSet<product>
    .Where(p => Operators.CompareString(
        Left: p.supplierID, 
        Right: __$VB$Local_supplierID_0, 
        TextCompare: False) == 0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). 

I found the following solution:

Dim supplierID as string="1545464"
Dim results = (From pa In DC.product.AsNoTracking()
                            Where pa.supplierID.Equals(supplierID)
                            Select pa)

Is my solution correct, using .Equals()? In C# language if it works with the operator "=="

I have created a small solution with which you can reproduce the error.

The solution has 4 projects:

• Sup.Entities (C#)
• Sup.DAL (C#)
• Sup.ConsoleApp1 (C#)
• Sup.consoleAppVB (VB.NET)

This is the error that occurs in Sup.ConsoleAppVB (VB.NET)

This is the result in Sup.ConsoleApp1 (C#)

Attached solution Download that includes projects and an SQL file to create the database and 1 table with 3 rows.

Please change connectionstring for UseSqlServer("...") in OnConfiguring Context

Answer 1

I just ran into this issue but since I have developed my own LINQ to SQL evaluator before I knew how to solve the problem. VB.NET transforms the = operator for strings into a call to Microsoft.VisualBasic.CompilerServices.Operators.CompareString(). So when the expression tree is evaluated this method must be handled. I suspect the reason for this is because VB handles string comparisons to null ((text = Nothing) = True).

I didn't download your sample but I fixed it in an ASP.NET Core application.

If you were using LINQ, this would be handled inside an ExpressionVisitor but for Entity Framework Core 3.1, I found that you can implement an IMethodCallTranslator.

However, Entity Framework uses SqlExpression expressions so an ISqlExpressionFactory is needed to create them. Fortunately, dependency injection can be used to get an implementation from IServiceCollection.

Public Class VbCompareStringMethodCallTranslator : Implements IMethodCallTranslator

    Private mExpressionFactory As ISqlExpressionFactory

    Public Sub New(expressionFactory As ISqlExpressionFactory)
        Me.mExpressionFactory = expressionFactory
    End Sub

    Public Function Translate(instance As SqlExpression, method As MethodInfo, arguments As IReadOnlyList(Of SqlExpression)) As SqlExpression Implements IMethodCallTranslator.Translate
        If method IsNot Nothing Then
            If method.Name = "CompareString" AndAlso method.DeclaringType?.Name = "Operators" AndAlso
                method.DeclaringType?.Namespace = "Microsoft.VisualBasic.CompilerServices" Then

                Dim left = arguments(0)
                Dim right = arguments(1)

                If method.Name Is NameOf(String.Compare) AndAlso arguments.Count = 2 AndAlso
                        arguments(0).Type.UnwrapNullableType Is arguments(1).Type.UnwrapNullableType Then

                    left = arguments(0)
                    right = arguments(1)

                ElseIf method.Name Is NameOf(String.CompareTo) AndAlso arguments.Count = 1 AndAlso
                        instance IsNot Nothing AndAlso instance.Type.UnwrapNullableType Is arguments(0).Type.UnwrapNullableType Then

                    left = instance
                    right = arguments(0)
                End If

                If left IsNot Nothing AndAlso right IsNot Nothing Then
                    Return Me.mExpressionFactory.[Case]({New CaseWhenClause(Me.mExpressionFactory.Equal(left, right), Me.mExpressionFactory.Constant(0)),
                                                         New CaseWhenClause(Me.mExpressionFactory.GreaterThan(left, right), Me.mExpressionFactory.Constant(1)),
                                                         New CaseWhenClause(Me.mExpressionFactory.LessThan(left, right), Me.mExpressionFactory.Constant(-1))},
                                                         Nothing)
                End If
            End If
        End If

        Return Nothing
    End Function

End Class

Making use of the following extension method

Public Module SharedTypeExtensions

    <Extension()>
    Public Function UnwrapNullableType(type As Type) As Type
        Return If(Nullable.GetUnderlyingType(type), type)
    End Function

End Module

You can see that this is the code used by Entity Framework to handle string comparisons here https://github.com/dotnet/efcore/blob/3656e9daa9b81398d8c065a702fd5dca91979f49/src/EFCore.Relational/Query/Internal/ComparisonTranslator.cs

So now this needs to be hooked up and the following plumbing code can be used

Public Class VbMethodCallTranslatorPlugin : Implements IMethodCallTranslatorPlugin

    Public Sub New(expressionFactory As ISqlExpressionFactory)
        Me.Translators = {New VbCompareStringMethodCallTranslator(expressionFactory)}
    End Sub

    Public ReadOnly Property Translators As IEnumerable(Of IMethodCallTranslator) Implements IMethodCallTranslatorPlugin.Translators

End Class

Public Class VbDbContextOptionsExtension : Implements IDbContextOptionsExtension

    Public Sub ApplyServices(services As IServiceCollection) Implements IDbContextOptionsExtension.ApplyServices
        services.AddSingleton(Of IMethodCallTranslatorPlugin, VbMethodCallTranslatorPlugin)
    End Sub

    Public Sub Validate(options As IDbContextOptions) Implements IDbContextOptionsExtension.Validate
    End Sub

    Public ReadOnly Property Info As DbContextOptionsExtensionInfo Implements IDbContextOptionsExtension.Info
        Get
            Return New VbDbContextOptionsExtensionInfo(Me)
        End Get
    End Property

End Class

Public Class VbDbContextOptionsExtensionInfo : Inherits DbContextOptionsExtensionInfo

    Public Sub New(extension As IDbContextOptionsExtension)
        MyBase.New(extension)
    End Sub

    Public Overrides Function GetServiceProviderHashCode() As Long
        Return Me.Extension.GetHashCode
    End Function

    Public Overrides Sub PopulateDebugInfo(<NotNullAttribute> debugInfo As IDictionary(Of String, String))
        debugInfo("VB:TranslateMethods") = True.ToString
    End Sub

    Public Overrides ReadOnly Property IsDatabaseProvider As Boolean
        Get
            Return False
        End Get
    End Property
    Public Overrides ReadOnly Property LogFragment As String
        Get
            Return "VbMethodSupport=true"
        End Get
    End Property

End Class

Now this can hooked up using the DbContextOptionsBuilder, but the following extension method will make this easier

Public Module VbDbContextOptionsBuilderExtensions

    <Extension>
    Public Function AddVbSupport(optionsBuilder As DbContextOptionsBuilder) As DbContextOptionsBuilder
        Dim builder = CType(optionsBuilder, IDbContextOptionsBuilderInfrastructure)

        Dim extension = If(optionsBuilder.Options.FindExtension(Of VbDbContextOptionsExtension), New VbDbContextOptionsExtension)
        builder.AddOrUpdateExtension(extension)

        Return optionsBuilder
    End Function

End Module

Now you can hook this up while setting up your DbContext

services.AddDbContext(Of ApplicationDbContext)(Sub(options)
                                                    options.UseSqlServer(Me.Configuration.GetConnectionString("ConnectionString"),
                                                                        Sub(dbOptions)
                                                                            dbOptions.MigrationsAssembly("Database.Migrations")
                                                                        End Sub)
                                                    options.AddVbSupport
                                                End Sub)

Additional Info

This appears to be a bug in Entity Framework rather than VB.NET just not being supported. You can find this code in the dotnet efcore repository. https://github.com/dotnet/efcore/blob/7cb52b388a2d9fd8f9c2c499ef3ffb9753d9932a/src/EFCore/Query/Internal/QueryOptimizingExpressionVisitor.cs#L113-L132

I submitted a bug report here https://github.com/dotnet/efcore/issues/20889

Vote it up so the devs will fix the issue!

Update 1

Looks like this will be fixed in .NET 5

Update 2

The above solution was causing issues after refreshing the page a bunch of times. I would get an error something to the effect of "more than 20 IService instances have been created"

In order to fix this I just added the expression transform into a different part of the pipeline.

Imports System.Linq.Expressions
Imports System.Runtime.CompilerServices
Imports Microsoft.EntityFrameworkCore
Imports Microsoft.EntityFrameworkCore.Query

Public Class VbRelationalQueryTranslationPreprocessorFactory : Implements IQueryTranslationPreprocessorFactory

    Private ReadOnly mDependencies As QueryTranslationPreprocessorDependencies
    Private ReadOnly mRelationalDependencies As RelationalQueryTranslationPreprocessorDependencies

    Public Sub New(dependencies As QueryTranslationPreprocessorDependencies, relationalDependencies As RelationalQueryTranslationPreprocessorDependencies)
        Me.mDependencies = dependencies
        Me.mRelationalDependencies = relationalDependencies
    End Sub

    Public Overridable Function Create(queryCompilationContext As QueryCompilationContext) As QueryTranslationPreprocessor Implements IQueryTranslationPreprocessorFactory.Create
        Return New VbRelationalQueryTranslationPreprocessor(Me.mDependencies, Me.mRelationalDependencies, queryCompilationContext)
    End Function

End Class

Public Class VbRelationalQueryTranslationPreprocessor : Inherits RelationalQueryTranslationPreprocessor

    Public Sub New(dependencies As QueryTranslationPreprocessorDependencies, relationalDependencies As RelationalQueryTranslationPreprocessorDependencies, queryCompilationContext As QueryCompilationContext)
        MyBase.New(dependencies, relationalDependencies, queryCompilationContext)
    End Sub

    Public Overrides Function Process(query As Expression) As Expression
        query = New LanguageNormalizingExpressionVisitor().Visit(query)

        Return MyBase.Process(query)
    End Function

End Class

Public Class LanguageNormalizingExpressionVisitor : Inherits ExpressionVisitor

    Protected Overrides Function VisitBinary(node As BinaryExpression) As Expression
        Dim methodCall = TryCast(node.Left, MethodCallExpression)

        If methodCall IsNot Nothing Then
            ' Replace calls to comparestring with a binary equals on the operands
            If methodCall.Method.Name = "CompareString" AndAlso methodCall.Method.DeclaringType?.Name = "Operators" AndAlso methodCall.Method.DeclaringType?.Namespace = "Microsoft.VisualBasic.CompilerServices" Then
                Dim left = Me.Visit(methodCall.Arguments(0))
                Dim right = Me.Visit(methodCall.Arguments(1))

                Return Expression.MakeBinary(node.NodeType, left, right)
            End If
        End If

        Return MyBase.VisitBinary(node)
    End Function

End Class

Public Module VbDbContextOptionsBuilderExtensions

    <Extension>
    Public Function AddVbSupport(optionsBuilder As DbContextOptionsBuilder) As DbContextOptionsBuilder
        optionsBuilder.ReplaceService(Of IQueryTranslationPreprocessorFactory, VbRelationalQueryTranslationPreprocessorFactory)()

        Return optionsBuilder
    End Function

End Module