Different SQL produced from Where(l => l.Side == 'A') vs Where(l => l.Side.Equals('A')

Question

I've been experimenting with queries in LinqPad. We have a table Lot with a column Side char(1). When I write a linq to sql query Lots.Where(l => l.Side == 'A'), it produces the following SQL

-- Region Parameters
DECLARE @p0 Int = 65
-- EndRegion
SELECT ..., [t0].[Side], ...
FROM [Lot] AS [t0]
WHERE UNICODE([t0].[Side]) = @p0

However, using Lots.Where(l => l.Side.Equals('A')), it produces

-- Region Parameters
DECLARE @p0 Char(1) = 'A'
-- EndRegion
SELECT ..., [t0].[Side], ...
FROM [Lot] AS [t0]
WHERE [t0].[Side] = @p0

It would appear upon (albeit naïve) inspection, that the latter would be marginally faster, as it doesn't need the call to UNICODE.

Using int, smallint or varchar columns there's no difference between the produced SQL with == or .Equals, why is char(1) and the corresponding C# type char different?

Is there any way to predict whether a given column type will produce differing SQL with the two forms of equality check?

Edit:

I have checked every type supported by MS SQL, and only char(1) and nchar(1) show this behavior. Both are represented in LinqToSql by the System.Char type. If it was a deliberate decision, then I would have expected the same behavior on binary(1), which could be represented by System.Byte (but instead is System.Linq.Binary with a length of 1.

Edit 2: In case it is relevant, I am using LINQPad to view the created SQL. I was assuming Linqpad would use the system's LinqToSQL, but I realized today that that assumption could be flawed.

Edit 3: I ran a quick VS project to test the system LinqToSQL, and we get the same result:

Code:

static void Main(string[] args)
{
    var db = new DataClasses1DataContext {Log = Console.Out};
    Console.Out.WriteLine("l.Side == 'A'");
    Console.Out.WriteLine("=============");
    Console.Out.WriteLine();
    foreach (Lot ll in db.Lots.Where(l => l.Side == 'A'))
    {
        break;
    }
    Console.Out.WriteLine();
    Console.Out.WriteLine("---------------------------------------");
    Console.Out.WriteLine();

    Console.Out.WriteLine("l.Side.Equals('A')");
    Console.Out.WriteLine("==================");
    Console.Out.WriteLine();
    foreach (Lot ll in db.Lots.Where(l => l.Side.Equals('A')))
    {
        break;
    }
    Console.In.Read();
}

Output:

l.Side == 'A'
=============

SELECT ..., [t0].[Side], ...
FROM [dbo].[Lot] AS [t0]
WHERE UNICODE([t0].[Side]) = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [65]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.1532.0


---------------------------------------

l.Side.Equals('A')
==================

SELECT ..., [t0].[Side], ...
FROM [dbo].[Lot] AS [t0]
WHERE [t0].[Side] = @p0
-- @p0: Input Char (Size = 1; Prec = 0; Scale = 0) [A]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.6.1532.0

It is interesting to note that in the == 'A' version, the parameter is passed as an int, whereas in the .Equals version, it is passed as char.

The dbml and table creation script are in this gist.

Answer 1

There is some documentation about this:

Mismatches in SQL Server: Fixed length character types. Transact-SQL distinguishes between Unicode and non-Unicode categories and has three distinct types in each category: fixed length nchar/char, variable length nvarchar/varchar, and larger-sized ntext/text. The fixed length character types could be mapped to the CLR System.Char type for retrieving characters, but they do not really correspond to the same type in conversions and behavior.

And the L2S source code has only one place that uses the string literal "UNICODE":

It appears that a necessary precondition for the function to show up is a SqlUnary syntax tree node with type Convert:

I don't know how you managed to satisfy the IsNumeric condition. I think you have a type mismatch there. Is the column really mapped as System.Char?

The Equals call probably does not trigger this code path. This likely is a L2S bug.

Equals is translated in multiple places in the source code. Here is one of them:

It looks like this bypasses any argument conversions. It does not care what the argument is. This probably fails with a variety of queries (so it's likely a bug). I wonder what happens if you write l.Side.Equals(1.2m). I guess this translates literally to SQL.

---

I have now reproduced it. Map the column to string. This fixes the generated SQL. The execution plan shows that an index seek is possible with the SQL that is being generated.