LinqToSql.包含和nvarchar vs varchar参数->指数转换计划 [英] LinqToSql .Contains and nvarchar vs varchar parameters -> index conversion plan
问题描述
我只有一个表映射到数据上下文中.这是感兴趣的列上的属性和属性:
I have a just one table mapped in a datacontext. Here's the property and attribute on the column of interest:
[Column(Storage="_CustomerNumber", DbType="VarChar(25)")]
public string CustomerNumber
{
实际上,此列是varchar(25)并具有索引.
This column is, in fact, a varchar(25) and has an index.
我有一些简单的代码:
DataClasses1DataContext myDC = new DataClasses1DataContext();
myDC.Log = Console.Out;
List<string> myList = new List<string>() { "111", "222", "333" };
myDC.Customers
.Where(c => myList.Contains(c.CustomerNumber))
.ToList();
哪个生成以下SQL文本:
Which generates this SQL text:
SELECT [t0].[CustomerNumber], [t0].[CustomerName]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerNumber] IN (@p0, @p1, @p2)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [111]
-- @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [222]
-- @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [333]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
请注意,参数是nvarchar!
Notice that the paramaters are nvarchar!
此查询命中数据库时,它会生成一个可怕的计划,其中涉及先将CustomerNumber上的数百万行索引转换为nvarchar ,再在其中进行搜索.
When this query hits the database, it generates a horrible plan which involves converting the multi-million row index on CustomerNumber to nvarchar before seeking within it.
不允许更改表,但可以更改查询和dbml.在不进行索引转换的情况下我该怎么做才能取出数据?
I'm not allowed to change the table, but I can change the query and the dbml. What can I do to get the data out without getting this index conversion?
推荐答案
这是我现在解决此问题的方法.这会将参数转换为所需的类型,然后运行查询.它生成的SQL与原始生成的SQL相同,只是参数类型不同.
Here's the way I solve this problem now-a-days. This converts the parameters to the desired type and then runs the query. It generates the same sql as originally generated, just with different parameter types.
DbCommand myCommand = myDataContext.GetCommand(query);
foreach (DbParameter dbParameter in myCommand.Parameters)
{
if (dbParameter.DbType == System.Data.DbType.String)
{
dbParameter.DbType = System.Data.DbType.AnsiString;
}
}
myDataContext.Connection.Open();
System.Data.Common.DbDataReader reader = myCommand.ExecuteReader();
List<RecordType> result = myDataContext.Translate<RecordType>(reader).ToList();
myDataContext.Connection.Close();
这篇关于LinqToSql.包含和nvarchar vs varchar参数->指数转换计划的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!