c# - EnitityFramework 比较字符串的速度非常慢，因为创建了 nvarchar sqlparameter 而不是 varchar

Question

我有这个示例查询:

context.BarcodeTipiDoc.AsQueryable().Where(d => d.Barcode.CompareTo(minBarcode) > 0);

该查询运行速度非常慢，因为 Entity Framework 将“minBarcode”的 SqlParameter 创建为 nvarchar而不是 varchar .

我试图设置列映射:

[Column("Barcode", TypeName = "varchar(21)")]   
public string Barcode { get; set; }

但什么都没有改变。

有没有办法告诉 Entity Framework sqlparameter 的正确类型？

这个查询几乎是即时的:

DECLARE @__minBarcode_0 AS Varchar(21)

SET @__minBarcode_0 = 'aa'

SELECT TOP(100) [d].[Barcode], [d].[contenttype], [d].[idvolume], [d].[path_documento], [d].[Progressivo], [d].[Stato]
    FROM BarcodeTipiDoc AS [d]
    WHERE [d].[Barcode] > @__minBarcode_0

由 Entity Framework 生成的相同查询，由于 nvarchar 需要几分钟:

DECLARE @__minBarcode_0 AS nvarchar(21)

SET @__minBarcode_0 = 'aa'

SELECT TOP(100) [d].[Barcode], [d].[contenttype], [d].[idvolume], [d].[path_documento], [d].[Progressivo], [d].[Stato]
    FROM BarcodeTipiDoc AS [d]
    WHERE [d].[Barcode] > @__minBarcode_0

表架构:

Barcode varchar(21) Unchecked
tipodoc char(4) Unchecked
codutenteinserimento    uniqueidentifier    Checked
dataacquisizione    datetime    Checked
firmato bit Checked
tipodocdescrizione  varchar(50) Checked
Stato   int Unchecked
originedoc  tinyint Checked Unchecked

我不允许更改数据库的任何内容，我只需要通过 Entity Framework 从 LINQ 更改生成的 sql 代码。

如果没有办法，我将被迫编写并将选择作为纯字符串执行。

版本是entity framework 2.2，不过我可以升级。

Answer 1

There is a way to tell to Entity Framework the right type of the sqlparameter?

目前(EF Core 2.x, 3.0)没有这样的方法。 EF Core 尝试从表达式内部的用法推断参数类型。

所以 TypeName = "varchar(21)或 .IsUnicode(false).HasMaxLength(21)列映射是朝着正确方向迈出的一步。

不幸的是，对于像 == 这样的比较运算符，2.x 参数类型推断成功了。 , >等，但对于 string.CompareTo 之类的方法失败, string.Compare等等。

这已在 3.0 中修复，但现在翻译远非最佳( CASE WHEN ... > 0 而不是简单的 > )并且还有许多破坏性更改，因此仅因此升级没有意义并且有风险。

我能提供的是一个基于自定义映射数据库标量方法的解决方案，类似于 Entity Framework Core: Guid Greater Than for Paging .它介绍了几个 string映射到 string 的自定义方法比较运算符:

public static class StringFunctions
{
    public static bool IsGreaterThan(this string left, string right) => string.Compare(left, right) > 0;
    public static bool IsGreaterThanOrEqual(this string left, string right) => string.Compare(left, right) >= 0;
    public static bool IsLessThan(this string left, string right) => string.Compare(left, right) < 0;
    public static bool IsLessThanOrEqual(this string left, string right) => string.Compare(left, right) <= 0;
    public static ModelBuilder RegisterStringFunctions(this ModelBuilder modelBuilder) => modelBuilder
        .RegisterFunction(nameof(IsGreaterThan), ExpressionType.GreaterThan)
        .RegisterFunction(nameof(IsGreaterThanOrEqual), ExpressionType.GreaterThanOrEqual)
        .RegisterFunction(nameof(IsLessThan), ExpressionType.LessThan)
        .RegisterFunction(nameof(IsLessThanOrEqual), ExpressionType.LessThanOrEqual);
    static ModelBuilder RegisterFunction(this ModelBuilder modelBuilder, string name, ExpressionType type)
    {
        var method = typeof(StringFunctions).GetMethod(name, new[] { typeof(string), typeof(string) });
        modelBuilder.HasDbFunction(method).HasTranslation(parameters =>
        {
            var left = parameters.ElementAt(0);
            var right = parameters.ElementAt(1);
            // EF Core 2.x
            return Expression.MakeBinary(type, left, right, false, method);
        });
        return modelBuilder;
    }
}

对于 EF Core 3.0 替换

return Expression.MakeBinary(type, left, right, false, method);

与(加上各自的 using s)

if (right is SqlParameterExpression rightParam)
    right = rightParam.ApplyTypeMapping(left.TypeMapping);
else if (left is SqlParameterExpression leftParam)
    left = leftParam.ApplyTypeMapping(right.TypeMapping);
return new SqlBinaryExpression(type, left, right, typeof(bool), null);

现在你只需要打电话

modelBuilder.RegisterStringFunctions();

在您的 OnModelCreating 内覆盖。

然后在您的查询中，而不是

d => d.Barcode.CompareTo(minBarcode) > 0

用

d => d.Barcode.IsGreaterThan(minBarcode)

它会被翻译成

[d].[Barcode] > @__minBarcode_0

具有正确的数据库参数类型(与 BarCode 列的数据库类型相同)。