LinqToSql .Contains and nvarchar vs varchar parameters -> index conversion plan

Question

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?

Answer 1

Here's what I've got as a possible work around. I'm interested in seeing other solutions as well:

List<IQueryable<Customer>> myQueries = 
    myList.Select(s => myDC.Customers.Where(c => c.CustomerNumber == s)).ToList();
IQueryable<Customers> myQuery = myQueries.First();
foreach(IQueryable<Customer> someQuery in myQueries.Skip(1))
{
    myQuery = myQuery.Concat(someQuery);
}
myQuery.ToList();

This generates the following SQL:

SELECT [t4].[CustomerNumber], [t4].[CustomerName]
FROM (
    SELECT [t2].[CustomerNumber], [t2].[CustomerName]
    FROM (
        SELECT [t0].[CustomerNumber], [t0].[CustomerName]
        FROM [dbo].[Customer] AS [t0]
        WHERE [t0].[CustomerNumber] = @p0
        UNION ALL
        SELECT [t1].[CustomerNumber], [t1].[CustomerName]
        FROM [dbo].[Customer] AS [t1]
        WHERE [t1].[CustomerNumber] = @p1
        ) AS [t2]
    UNION ALL
    SELECT [t3].[CustomerNumber], [t3].[CustomerName]
    FROM [dbo].[Customer] AS [t3]
    WHERE [t3].[CustomerNumber] = @p2
    ) AS [t4]
-- @p0: Input VarChar (Size = 3; Prec = 0; Scale = 0) [111]
-- @p1: Input VarChar (Size = 3; Prec = 0; Scale = 0) [222]
-- @p2: Input VarChar (Size = 3; Prec = 0; Scale = 0) [333]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8