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
{
This column is, in fact, a varchar(25) and has an index.
I've got some simple code:
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();
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
Notice that the paramaters are 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.
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?
Use varchar unless you deal with a lot of internationalized data, then use nvarchar . Just use nvarchar for everything.
Each character of an nvarchar column requires 2 bytes of storage whereas a varchar column requires 1 byte per character. Potentially, varchar will be quicker but that may well mean that you cannot store the data that you need.
Use nvarchar when the sizes of the column data entries vary considerably. Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With