LINQ to SQL将不会生成可查询的查询 [英] LINQ to SQL will not generate sargable query

问题描述

我正在使用LINQ To Sql(不是Entity Framework)，System.Data.Linq.DataContext库，命中SQL Server 2005数据库并使用.Net Framework 4.

I'm using LINQ To Sql (not Entity Framework), the System.Data.Linq.DataContext library, hitting a SQL Server 2005 database and using .Net Framework 4.

表dbo.Dogs具有类型为CHAR(1)NULL的活动"列.如果我直接编写SQL，查询将是:

The table dbo.Dogs has a column "Active" of type CHAR(1) NULL. If I was writing straight SQL the query would be:

SELECT * FROM dbo.Dogs where Active = 'A';

LINQ查询是这样的:

The LINQ query is this:

from d in myDataContext.Dogs where d.Active == 'A' select d;

从上面的LINQ查询生成的SQL将Active字段转换为UNICODE.这意味着我无法在dbo.Dogs.Active列上使用索引，从而大大降低了查询速度:

The SQL that gets generated from the above LINQ query converts the Active field to UNICODE. This means I cannot use the index on the dbo.Dogs.Active column, slowing the query significantly:

SELECT [t0].Name, [t0].Active
FROM [dbo].[Dog] AS [t0]
WHERE UNICODE([t0].[Active]) = @p1

我是否可以做些什么来阻止Linq to Sql插入该UNICODE()调用(从而失去对dogs.Active的索引的好处)?我尝试使用EntityFunctions.AsNonUnicode()方法包装参数，但这没有任何好处(它在生成的sql中将CONVERT()插入了NVARCHAR而不是UNICODE())，例如:

Is there anything I can do to stop Linq to Sql from inserting that UNICODE() call (and thus losing the benefit of my index on dogs.Active)? I tried wrapping the parameters using the EntityFunctions.AsNonUnicode() method, but that did no good (it inserted a CONVERT() to NVARCHAR instead of UNICODE() in the generated sql), eg:

...where d.Active.ToString() == EntityFunctions.AsNonUnicode('A'.ToString());

推荐答案

您可以进行一些修改(因为LINQ to SQL和EF通常需要这样做).在dbml中将属性声明为NCHAR.我希望这将消除执行UNICODE转换的需要.我们正在以一种良性的方式欺骗​​L2S.

You can do a little hack (as it is often required with LINQ to SQL and EF). Declare the property as NCHAR in the dbml. I hope that will remove the need to do the UNICODE conversion. We are tricking L2S in a benign way with that.

也许您还需要插入EntityFunctions.AsNonUnicode调用，以使右侧成为非Unicode类型.

Maybe you need to also insert the EntityFunctions.AsNonUnicode call to make the right hand side a non-unicode type.

您也可以尝试将列映射为varchar.

You can also try mapping the column as varchar.

这篇关于LINQ to SQL将不会生成可查询的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！