vb.net - VB.NET 中的 EF Core、LINQ 运算符“=”

Question

我在 EF Core 3.1 中有下一个代码，语言为 VB.NET

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

异常抛出是：

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(). 

我找到了以下解决方案：

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

我的解决方案是否正确，使用 .Equals()？在 C# 语言中，如果它与运算符“==”一起使用

我创建了一个小解决方案，您可以使用它来重现错误。

该解决方案有 4 个项目：

• 支持实体 (C#)
• Sup.DAL (C#)
• Sup.ConsoleApp1 (C#)
• Sup.consoleAppVB (VB.NET)

这是在 Sup.ConsoleAppVB (VB.NET) 中发生的错误

这是 Sup.ConsoleApp1 (C#) 中的结果

附加解决方案下载，其中包括项目和用于创建数据库的 SQL 文件和 1 个 3 行表。

请在 OnConfiguring 上下文中更改 UseSqlServer("...") 的连接字符串

标签： vb.netentity-framework-core

Answer 1

我刚刚遇到了这个问题，但是因为在我知道如何解决这个问题之前我已经开发了自己的 LINQ to SQL 评估器。VB.NET 将字符串的 = 运算符转换为对Microsoft.VisualBasic.CompilerServices.Operators.CompareString(). 因此，当计算表达式树时，必须处理此方法。我怀疑这是因为 VB 处理字符串与 null ( (text = Nothing) = True) 的比较。

我没有下载您的示例，但我在 ASP.NET Core 应用程序中修复了它。

如果您使用的是 LINQ，这将在一个内部处理，ExpressionVisitor但对于 Entity Framework Core 3.1，我发现您可以实现一个IMethodCallTranslator.

但是，实体框架使用SqlExpression表达式，因此ISqlExpressionFactory需要创建它们。幸运的是，依赖注入可用于从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

利用以下扩展方法

Public Module SharedTypeExtensions

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

End Module

您可以在此处看到这是 Entity Framework 用于处理字符串比较的代码https://github.com/dotnet/efcore/blob/3656e9daa9b81398d8c065a702fd5dca91979f49/src/EFCore.Relational/Query/Internal/ComparisonTranslator.cs

所以现在这需要连接起来，并且可以使用以下管道代码

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

现在可以使用DbContextOptionsBuilder.

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

现在您可以在设置您的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)

附加信息

这似乎是实体框架中的一个错误，而不是 VB.NET 中的一个错误，只是不受支持。您可以在 dotnet efcore 存储库中找到此代码。 https://github.com/dotnet/efcore/blob/7cb52b388a2d9fd8f9c2c499ef3ffb9753d9932a/src/EFCore/Query/Internal/QueryOptimizingExpressionVisitor.cs#L113-L132

我在这里提交了一个错误报告 https://github.com/dotnet/efcore/issues/20889

投票，以便开发人员解决问题！

更新 1

看起来这将在 .NET 5 中修复

更新 2

上述解决方案在多次刷新页面后导致问题。我会收到“已创建超过 20 个 IService 实例”的错误

为了解决这个问题，我只是将表达式转换添加到管道的不同部分。

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