VB.NET string operators causes an InvalidOperationException

[manuelxmarquez]

VB.NET string operators causes an InvalidOperationException.

Dim result = context.Items.SingleOrDefault(Function(x) x.Name = itemName)

InvalidOperationException: The LINQ expression 'DbSet .Where(a => Operators.CompareString( Left: a.Name, Right: __$VB$Local_applicationName_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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|8_0(ShapedQueryExpression translated, ref <>c__DisplayClass8_0 )
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor(Expression query)
Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery(Expression query, bool async)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore(IDatabase database, Expression query, IModel model, bool async)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass9_0.b__0()
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore(object cacheKey, Func<Func<QueryContext, TFunc>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery(object cacheKey, Func<Func<QueryContext, TResult>> compiler)
Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute(Expression query)
Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute(Expression expression)
System.Linq.Queryable.SingleOrDefault(IQueryable source, Expression<Func<TSource, bool>> predicate)

Workaround Solution

The following code can be used to fix the issue

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

Public Module SharedTypeExtensions

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

End Module

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

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

And hook it up with the following code

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

Code fix

It would appear that this code is not being called but I have not confirmed and debugged it, just speculation.

efcore/src/EFCore/Query/Internal/QueryOptimizingExpressionVisitor.cs

Lines 113 to 132 in 7cb52b3

	// In VB.NET, comparison operators between strings (equality, greater-than, less-than) yield
	// calls to a VB-specific CompareString method. Normalize that to string.Compare.
	if (visited.Method.Name == "CompareString"
	&& visited.Method.DeclaringType?.Name == "Operators"
	&& visited.Method.DeclaringType?.Namespace == "Microsoft.VisualBasic.CompilerServices"
	&& visited.Object == null
	&& visited.Arguments.Count == 3
	&& visited.Arguments[2] is ConstantExpression textCompareConstantExpression)
	{
	return (bool)textCompareConstantExpression.Value
	? Expression.Call(
	_stringCompareWithComparisonMethod,
	visited.Arguments[0],
	visited.Arguments[1],
	Expression.Constant(StringComparison.OrdinalIgnoreCase))
	: Expression.Call(
	_stringCompareWithoutComparisonMethod,
	visited.Arguments[0],
	visited.Arguments[1]);
	}

Maybe it needs to be moved to ComparisonTranslator.cs which is what my workaround solution does.

Project properties:

<TargetFramework>netcoreapp3.1</TargetFramework>
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.3" />

[smitpatel]

Duplicate of #19592

[manuelxmarquez]

@smitpatel I didn't see the issue as I was searching only open issues. I guess my code may serve as an example to write a translator extension. I suppose this can be closed.