您的位置: 首页  >  技术问答  >  EnitityFramework比较字符串非常慢，因为创建nvarchar sqlparameter而不是varchar

EnitityFramework比较字符串非常慢，因为创建nvarchar sqlparameter而不是varchar

分类: 技术问答 • 2022-07-23 17:59:22
问题描述：

我有这个示例查询：

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

该查询运行非常慢，因为Entity Framework为 minBarcode创建的SqlParameter为 nvarchar 而不是 varchar 。

That query runs very slow because Entity Framework creates SqlParameter for "minBarcode" as nvarchar instead of varchar.

我尝试设置列映射：

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

但没有任何变化。

有是一种告诉Entity Framework正确的sqlparameter类型的方法吗？

There is a way to tell to Entity Framework the right type of the 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而需要花费几分钟：

Same query generated by Entity Framework, takes several minutes because of 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

I不允许更改数据库的任何内容，我只需要通过实体框架更改LINQ生成的sql代码。

I am not allowed to change anything of the database, I just need to change generated sql code from LINQ by entity framework.

如果没有办法，我将被迫

If there are no ways, I'll be forced to write and the execute the select as pure string.

版本是实体框架2.2，但我可以升级。

The version is entity framework 2.2, but I can upgrade.

答


有没有办法告诉Entity Framework正确的sql参数类型？

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

当前（EF Core 2.x，3.0）没有这种方法。 EF Core尝试从表达式内部的用法推断参数类型。

Currently (EF Core 2.x, 3.0) there is no such way. EF Core tries to infer the parameter type from the usage inside expressions.

所以 TypeName = varchar（21）或 .IsUnicode（false）.HasMaxLength （21）列映射朝着正确的方向迈进。

So TypeName = "varchar(21) or .IsUnicode(false).HasMaxLength(21) column mapping is step in the right direction.

不幸的是，对于 == ，> 等，但是对于 string.CompareTo 之类的方法失败， string.Compare 等。

Unfortunately 2.x parameter type inference succeeds for comparison operators like ==, > etc. but fails for methods like string.CompareTo, string.Compare etc.

此问题已在3.0中修复，但现在翻译远非最佳（ 的情况是...> 0 而不是简单的> ），并且还有很多重大更改，因此升级

This has been fixed in 3.0, but now the translation is far from optimal (CASE WHEN ... > 0 rather than simply >) and also there are many breaking changes, so upgrading just because of that doesn't make sense and is risky.

我可以提供的是基于自定义映射数据库标量方法的解决方案，类似于实体框架核心：指导性强于分页。 字符串自定义映射到 string 比较运算符的方法：

What I can offer is a solution based on a custom mapped database scalar methods similar to Entity Framework Core: Guid Greater Than for Paging. It introduces several string custom methods which are mapped to string comparison operators:

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替换

For EF Core 3.0 replace

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

与（加上分别使用的）

with (plus respective usings)

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

。

然后在查询中，而不是

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

使用

d => d.Barcode.IsGreaterThan(minBarcode)

它将被转换为

[d].[Barcode] > @__minBarcode_0

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

with correct db parameter type (same as the db type of BarCode column).

相关推荐