从哪里产生了不同的SQL（L => l.Side =='A'）与凡（L => l.Side.Equals（'A'） [英] Different SQL produced from Where(l => l.Side == 'A') vs Where(l => l.Side.Equals('A')

问题描述

我一直在尝试与LinqPad查询。我们有一个表拍与列侧CHAR（1）。当我写一个LINQ到SQL查询 Lots.Where（L => l.Side =='A'），它产生下面的SQL

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

然而，使用 Lots.Where（L => l.Side.Equals（'A'）），它产生

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

这将出现在（尽管天真）检查，后者会稍快，因为它并不需要调用 UNICODE 。

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

使用 INT ， SMALLINT 或 VARCHAR 列有产生的SQL没有区别与 = = 或 .Equals ，为什么 CHAR（1）和相应的C＃类型字符不同？

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?

有没有办法来预测一个给定的列类型是否会产生不同的SQL的两种形式平等检查

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

编辑：

我已经检查由MS SQL支持所有类型的，只有 CHAR（1）和的nchar（1）显示此行为。两者都在LinqToSql由 System.Char 键入表示。如果这是一个深思熟虑的决定，那么我会预期相同的行为二（1），这可能是由 System.Byte （反而是 System.Linq.Binary 与长度 1 。

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.

编辑2：如果它是相关的，我使用LINQPad来查看创建的SQL我是假设Linqpad将使用系统的LinqToSQL，但是今天我意识到，这样的假设可能是有缺陷的。

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.

编辑3：我跑了快VS项目来测试系统LinqToSQL，我们得到了相同的结果：

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

代码：

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

输出：

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

这是有趣的是，在 =='A'版本，参数作为传递一个 INT ，而在 .Equals 的版本，它是作为字符传递。

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.

的DBML和表创建脚本是在这个要点。

推荐答案

有一些的documentation 这一点：

不匹配在SQL Server：
固定长度的字符类型。 Transact-SQL的Unicode和非Unicode类别区分，并在每个类别中三种不同类型：固定长度的nchar /字符，可变长度为nvarchar / varchar和更大尺寸的ntext /文本。固定长度字符类型可以被映射到CLR System.Char类型用于检索字符，但他们并不真正对应于转化和行为的类型相同。

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.

而L2S源代码只有一个使用字符串UNICODE地点：

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

看来，对于展现出来的功能的必要前提是 SqlUnary 语法树节点类型转换：

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

的

我不知道你是怎么设法满足则IsNumeric 状态。我认为你有一个类型不匹配存在。是列真正映射为 System.Char ？

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?

等于通话可能不会触发此代码路径。这可能是一个错误L2S

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:

它看起来像这样绕过任何参数转换。它不关心的论点是什么。这可能失败，各种查询（所以它可能是一个错误）。我不知道你写的，会发生什么 l.Side.Equals（1.2米）。我想这字面翻译为SQL。

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.

我现在已经转载了。映射列字符串。这修复生成的SQL。执行计划显示了索引查找可能与正在生成的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.

这篇关于从哪里产生了不同的SQL（L => l.Side =='A'）与凡（L => l.Side.Equals（'A'）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！