LINQ to SQL will not generate sargable query

Question

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.

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';

The LINQ query is this:

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

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

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());

Answer 1

Linq is meant to make it easier to write queries and does not always generate optimal SQL. Sometimes when high performance is required it is more efficient to write raw SQL directly against the database, the Linq datacontext supports mapping of SQL result to entities just like linq. In your case I would suggest writing:

IEnumerable<Dog> results = db.ExecuteQuery<Dog>(
                           "SELECT * FROM dbo.Dogs where Active = {0}", 
                           'A');