EF Core 3.1.1避免出现异常“数据类型text和varchar在等于运算符中不兼容".就像LINQPad 6一样 [英] EF Core 3.1.1 Avoiding the exception "The data types text and varchar are incompatible in the equal to operator" just as LINQPad 6 does

问题描述

我正在尝试通过Entity Framework Core 3.1.1运行此LINQ表达式.

I am trying to run this LINQ expression through Entity Framework Core 3.1.1.

 using (Text3Context text3Context = new Text3Context(_constringText3))
            {
                var aus = text3Context.Ausschreibungen.Where(a => a.InhaltKurzText.Contains(value)).Select(c => c.InhaltKurzText).ToList();
            }

不幸的是，它引发了一个异常:当我在LINQPad 6中运行相同的表达式时，数据类型text和varchar在equals运算符中不兼容":

Unfortunately it throws the exception: "The data types text and varchar are incompatible in the equal to operator" however, when I run the same expression in LINQPad 6:

string value = "test";
var aus = Ausschreibungen.Where(a => a.InhaltKurzText.Contains(value)).Select(c => c.InhaltKurzText);
aus.Dump();

它完美无误地工作.

您可以看到sql查询略有不同:

The sql-querys are slightly different as you can see:

Linq/EF Core:

Linq/EF Core:

SELECT [a].[InhaltKurzText]
FROM [dbo].[Ausschreibungen] AS [a]
WHERE (@__value_0 = '''') OR (CHARINDEX(@__value_0, [a].[InhaltKurzText]) > 0)',N'@__value_0 text',@__value_0='test'

LINQPad:

SELECT [a].[InhaltKurzText]
FROM [Ausschreibungen] AS [a]
WHERE ((@__value_0 = N'''') AND @__value_0 IS NOT NULL) OR (CHARINDEX(@__value_0, [a].[InhaltKurzText]) > 0)',N'@__value_0 nvarchar(16)',@__value_0=N'test'

我如何像LINQPad一样处理此查询?

How can I handle this query just as LINQPad does?

"InhaltKurzText"的sql-datatype是"text"，这就是例外的原因，但是，不幸的是，由于更改sql-datatype并不是一种选择，并且解决方法是昂贵的"，我希望能够以与LINQPad相同的方式运行它.

The sql-datatype of "InhaltKurzText" is "text", thats the reason of the exception, however, since changing the sql-datatype is unfortunately not an option, and the workarounds are "expensive" I would like to be able to run it in the same way as LINQPad does.

依赖项Microsoft.Data.Sql.Client的版本为1.0.19.269.1.

The version of the dependency Microsoft.Data.Sql.Client is 1.0.19.269.1.

预先感谢

推荐答案

这似乎是由EF Core 3.x基础结构和/或SqlServer提供程序中的某些更改引起的.LINQPad无关紧要，因为它只是将LINQ查询委托给基础框架(在您的情况下，显然是EF Core 2.x).

This seems to be caused by some change in EF Core 3.x infrastructure and/or SqlServer provider. LINQPad is irrelevant because it simply delegates the LINQ query to the underlying framework (apparently EF Core 2.x in your case).

根据SqlServer

According to SqlServer documentation, text data type is obsolete and represents

服务器代码页中的可变长度非Unicode数据，最大字符串长度为2 ^ 31-1(2,147,483,647).

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647).

因此，即使数据库类型是 text ，您也可以在EF Core中将其映射为 varchar(2 ^ 31-1)

So even though the database type is text, you can map it in EF Core as varchar(2^31-1)

modelBuilder.Entity<Ausschreibungen>().Property(e => e.InhaltKurzText)
    .IsUnicode(false)
    .HasMaxLength((1 << 31) - 1);

有趣的是，生成的SQL与

Interestingly, the generated SQL is exactly the same (according to the EF Core log) as the one generated from

modelBuilder.Entity<Ausschreibungen>().Property(e => e.InhaltKurzText)
    .HasColumnType("text");

但成功运行，而稍后会生成有问题的运行时异常.

but runs successfully, while the later generates the runtime exception in question.

这篇关于EF Core 3.1.1避免出现异常“数据类型text和varchar在等于运算符中不兼容".就像LINQPad 6一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！