深入理解 LINQ to SQL 生成的 SQL 语句

Ø  简介

在 C# 中与数据交互最常用的语句就是 LINQ 了，而 LINQ to SQL 是最直接与数据库打交道的语句，它可以根据 LINQ 语法生成对应的 SQL 语句，在数据库中去执行。本文主要研究什么样 LINQ 语句会生成什么样 SQL 语句，从而确保编写 LINQ 语句时，便知道该语句会执行什么样的 SQL 语句，得到什么结果，这是很有必要的。因为很多时候会考虑到 SQL 效率问题，和数据是否有误等问题。主要包括：

1.   插入数据

2.   判断记录是否存在

3.   左连接情况下，判断某字段是否为 null 的写法

4.   连接查询关联不同类型（string & int）字段

5.   关联表（一对多关系），一条语句查询主表与子表数据

6.   使用 IQueryable<T> 类型的变量，嵌入 LINQ 查询语法中

7.   常见的查询技巧

8.   使用对象集合进行连接查询

 

1.   插入数据

1)   LINQ 语句

DataContext.CustomerTodos.Add(entity);

DataContext.SaveChanges();

2)   生成 SQL

exec sp_executesql N'INSERT [dbo].[Crm_CustomerTodo]([CustomerId], [ProductId], [CreateTime], [TodoTime], [UpdateTime], [ExpireTime], [TodoType], [State], [Result], [Source], [VisitInfoId], [CppId], [AuditorId], [AuditorTime], [AuditorState], [AuditorDesc], [SalesUserId], [CouponItemNo])

VALUES (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, NULL, @10, NULL, NULL, NULL, NULL, NULL, @11)

SELECT [Id]

FROM [dbo].[Crm_CustomerTodo]

WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 bigint,@1 bigint,@2 datetime2(7),@3 datetime2(7),@4 datetime2(7),@5 datetime2(7),@6 int,@7 int,@8 int,@9 int,@10 int,@11 nvarchar(max) ',@0=80242,@1=0,@2='2018-10-19 13:52:07.8299645',@3='2018-10-19 13:52:07.8299645',@4='2018-10-19 13:52:07.8299645',@5='2018-10-28 23:59:59',@6=6,@7=1,@8=0,@9=1,@10=2,@11=N'4568'

 

2.   判断记录是否存在

1)   LINQ 语句

DataContext.CustomerTodos.Any(o => o.TodoType == todoType && o.CustomerId == model.CustomerId);

2)   生成 SQL

exec sp_executesql N'SELECT

    CASE WHEN ( EXISTS (SELECT

        1 AS [C1]

        FROM [dbo].[Crm_CustomerTodo] AS [Extent1]

        WHERE ([Extent1].[TodoType] = @p__linq__0) AND ([Extent1].[CustomerId] = @p__linq__1)

    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT

        1 AS [C1]

        FROM [dbo].[Crm_CustomerTodo] AS [Extent2]

        WHERE ([Extent2].[TodoType] = @p__linq__0) AND ([Extent2].[CustomerId] = @p__linq__1)

    )) THEN cast(0 as bit) END AS [C1]

    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]',N'@p__linq__0 int,@p__linq__1 bigint',@p__linq__0=6,@p__linq__1=80242

 

3.   左连接情况下，判断某字段是否为 null 的写法

Ø  说明：UserInfo.CustomerId 关联 Customer.Id，为多对一的关系。但是 UserInfo.CustomerId 字段可能为 null。

1)   LINQ 语句

var datas1 = (from t1 in DataContext.UserInfoes

                join t2 in DataContext.Customers on t1.CustomerId equals t2.Id into t12

                from t3 in t12.DefaultIfEmpty()

                where t1.id > 5000

                select new

                {

                    UserId = t1.id,

                    CustomerId1 = t1.CustomerId,

                    CustomerId2 = t3 == null ? 0 : t3.Id

                }).Take(3).ToArray();

2)   生成 SQL

SELECT

    [Limit1].[C1] AS [C1],

    [Limit1].[id] AS [id],

    [Limit1].[CustomerId] AS [CustomerId],

    [Limit1].[C2] AS [C2]

    FROM ( SELECT TOP (3)

        [Extent1].[id] AS [id],

        [Extent1].[CustomerId] AS [CustomerId],

        1 AS [C1],

        CASE WHEN ([Extent2].[Id] IS NULL) THEN cast(0 as bigint) ELSE [Extent2].[Id] END AS [C2]

        FROM  [dbo].[UserInfo] AS [Extent1]

        LEFT OUTER JOIN [dbo].[Customer] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[Id]

        WHERE [Extent1].[id] > 5000

    )  AS [Limit1]

3)   执行结果

4)   分析

1.   这种写法其实看起来很怪异，为什么要用一个实体去判断是否被关联呢（这是同事善用的而一种写法，哈哈）？

2.   可见，在生成的 SQL 中使用了 CASE 做了一下判断，使用右表的主键 Id，如果为 null，就输出默认值，否则正常输出。

3.   其实，我们还可以使用可空类型接受右表的字段，可以省去 CASE 子句的判断。

 

4.   连接查询关联不同类型（string & int）字段

Ø  说明：在一些非正常数据表的设计中，可能出现外键字段与主键字段类型不一致的情况，强制关联就可能编写语句：

1)   LINQ 语句

var datas = (from t1 in dbContext.Orders

                join t3 in dbContext.Cities on t1.CityId equals t3.Id + string.Empty

                where t1.OrderNum != "O123321"

                orderby t1.Id descending

                select new

                {

                    t1.OrderNum,

                    t3.CityName

                }).Take(10).ToArray();

2)   生成 SQL

exec sp_executesql N'SELECT TOP (10)

    [Project1].[Id1] AS [Id],

    [Project1].[OrderNum] AS [OrderNum],

    [Project1].[CityName] AS [CityName]

    FROM ( SELECT

        [Extent1].[Id] AS [Id],

        [Extent1].[OrderNum] AS [OrderNum],

        [Extent2].[Id] AS [Id1],

        [Extent2].[CityName] AS [CityName]

        FROM  [dbo].[Orders] AS [Extent1]

        INNER JOIN [dbo].[Sys_Cities] AS [Extent2] ON ([Extent1].[CityId] = ( CAST( [Extent2].[Id] AS nvarchar(max)) + CASE WHEN (@p__linq__0 IS NULL) THEN N'''' ELSE @p__linq__0 END)) OR (([Extent1].[CityId] IS NULL) AND ( CAST( [Extent2].[Id] AS nvarchar(max)) + CASE WHEN (@p__linq__0 IS NULL) THEN N'''' ELSE @p__linq__0 END IS NULL))

        WHERE  NOT ((N''O123321'' = [Extent1].[OrderNum]) AND ([Extent1].[OrderNum] IS NOT NULL))

    )  AS [Project1]

    ORDER BY [Project1].[Id] DESC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N''

3)   分析

1.   以上的 LINQ 语句在某些情况下是会抛异常的，System.NotSupportedException:“无法将类型“System.Int32”强制转换为类型“System.Object”。LINQ to Entities 仅支持强制转换 EDM 基元或枚举类型。”，因为本人测试时，相同的环境下，一个项目中可以正常执行，而另一个则会报错，原因不详！

2.   可见，外键字段（CityId）为 varchar 类型，而主键（Id）字段为 int 类型，为了强制关联将 int 类型强制转为 varchar 类型，在生成的代码中也是如此。

3.   这样一来执行的 SQL 语句变得非常复杂，所以在实际的开发中，千万不能这样去设计数据表的结构！这里仅仅是为了演示这种非法设计的导致的后果。

 

5.   关联表（一对多关系），一条语句查询主表与子表数据

Ø  说明：我们经常会接触多表查询，比如一对多、多对多的查询。如果我们需要一条语句查询出主表与子表的数据，比如以下场景 Customer 与 UserInfo 是一对多的关系，我们就可以编写下面的语句：

1)   LINQ 语句

说明：查询客户 Id 为45357、49667的客户与用户记录

var dataList = (from t1 in DataContext.Customers

                where t1.Id == 45357 || t1.Id == 49667

                select new CustomerModel()

                {

                    Id = (int)t1.Id,

                    Name = t1.Name,

                    UserList = (from d1 in DataContext.UserInfoes

                                where d1.CustomerId == t1.Id

                                select new CustomerModel.UserInfoModel()

                                {

                                    Id = d1.id,

                                    UserName = d1.userName

                                }).ToList()

                }).ToList();

2)   生成 SQL

SELECT

    [Project1].[Id] AS [Id],

    [Project1].[C1] AS [C1],

    [Project1].[C2] AS [C2],

    [Project1].[Name] AS [Name],

    [Project1].[C3] AS [C3],

    [Project1].[Id1] AS [Id1],

    [Project1].[userName] AS [userName]

    FROM ( SELECT

        [Extent1].[Id] AS [Id],

        [Extent1].[Name] AS [Name],

        1 AS [C1],

         CAST( [Extent1].[Id] AS int) AS [C2],

        [Extent2].[id] AS [Id1],

        [Extent2].[userName] AS [userName],

        CASE WHEN ([Extent2].[id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]

        FROM  [dbo].[Customer] AS [Extent1]

        LEFT OUTER JOIN [dbo].[UserInfo] AS [Extent2] ON [Extent2].[CustomerId] = [Extent1].[Id]

        WHERE [Extent1].[Id] IN (45357,49667)

    )  AS [Project1]

    ORDER BY [Project1].[Id] ASC, [Project1].[C3] ASC

3)   执行结果：

1.   数据库

2.   程序

4)   分析

1.   可见，生成的 SQL 语句转换成了左连接，将主表与子表中所需的字段一起查询，在返回的数据集中，应该是 EF 帮我们以主表字段分组，并获取子表结果集的操作。是不是 EF 还是挺强大，帮我们省去了很多代码！？

2.   以上语法虽然看上去不知道 LINQ 是如何处理的，但 LINQ 确实是支持的，而且比较简单好理解。

3.   我们除了使用以上语法，也可以类似的直接写左连接在程序中进行分组，再取明细结果集的方式。（其实与以上执行的 SQL 语句是一样的，这个根据自己的习惯方式决定）

4.   另外还有一种方式，先查询主表，在遍历或包含去查询子表（强烈不推荐该方式）。

 

6.   使用 IQueryable<T> 类型的变量，嵌入 LINQ 查询语法中

1)   LINQ 语句

var allCustomers = (from d1 in DataContext.Customers

                    select new

                    {

                        CustmerId = d1.Id,

                        CityId = d1.CityID,

                        CustomerName = d1.Name

                    });

var datas = (from d1 in allCustomers

                join d3 in DataContext.CustomerVisitInfoes on d1.CustmerId equals d3.CustomerId

                where 1 == 1

                && (d3.VisitTime >= st && d3.VisitTime <= et)

                && cityId == d1.CityId

                select new

                {

                    d1.CustmerId,

                    d1.CustomerName,

                    d3.EmployeeId

                }).ToArray();

2)   生成 SQL

exec sp_executesql N'SELECT

    [Extent2].[SaleUserId] AS [SaleUserId],

    [Extent1].[Id] AS [Id],

    [Extent1].[Name] AS [Name]

    FROM  [dbo].[Customer] AS [Extent1]

    INNER JOIN [dbo].[CustomerVisitInfo] AS [Extent2] ON [Extent1].[Id] = [Extent2].[CustomerId]

    WHERE ([Extent2].[VisitTime] >= @p__linq__0) AND ([Extent2].[VisitTime] <= @p__linq__1) AND (@p__linq__2 = [Extent1].[CityID])',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7),@p__linq__2 bigint',@p__linq__0='2018-07-01 00:00:00',@p__linq__1='2018-07-31 23:59:59',@p__linq__2=73

3)   执行结果

4)   分析

1.   首先，我们定义了一个 IQueryable<T> 类型的变量，然后在使用这个变量作为数据源，进行关联查询。可见，在生成的 SQL 中直接进行了内连接查询，并输出相关字段。

2.   这种写法主要是可以方便多次查询，可以多次利用这个 allCustomers 变量进行查询，从而可以省去相同的代码。当然，这样的 IQueryable<T> 变量也可以写的比较复杂，作用就显得比较明显了。

3.   但是这种嵌套不必嵌套太多层，不然影响可读性，不便于分析和理解。

 

7.   常见的查询技巧

1)   遍历查询 IQueryable<T> 对象

1.   LINQ 语句

var query1 = dbContext.Grades.Select(o => o);

foreach (var item in query1)

{

    Console.WriteLine("获取记录:{0}", item.GradeId);

}

2.   生成 SQL

SELECT

    [Extent1].[GradeId] AS [GradeId],

    [Extent1].[GradeName] AS [GradeName],

    [Extent1].[Remark] AS [Remark]

    FROM [dbo].[Grade] AS [Extent1]

3.   分析

1)   一次性生成 SQL，再遍历结果集。

 

2)   根据不同条件生成对应 SQL

1.   LINQ 语句

var query2 = dbContext.Grades.Select(o => o);

for (int i = 1; i <= 3; i++)

{

    var entity = query2.Where(o => o.GradeId == i).FirstOrDefault();

    Console.WriteLine("获取记录:{0}", (entity != null ? entity.GradeId : 0));

}

2.   生成 SQL（类似的3条SQL）

exec sp_executesql N'SELECT TOP (1)

    [Extent1].[GradeId] AS [GradeId],

    [Extent1].[GradeName] AS [GradeName],

    [Extent1].[Remark] AS [Remark]

    FROM [dbo].[Grade] AS [Extent1]

    WHERE ([Extent1].[GradeId] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)',N'@p__linq__0 int',@p__linq__0=1

3.   分析

1)   每次遍历，都会生成不同的SQL，并查询数据库。

2)   通常情况下，不建议这样去遍历查询，可以先查询出所需的记录，再进行遍历。

 

3)   根据不同条件生成对应 SQL

1.   LINQ 语句

var query3 = (from t1 in dbContext.Grades

                where t1.GradeName.Contains("年级")

                orderby t1.GradeId descending

                select t1);

for (int i = 1; i <= 3; i++)

{

    var entity = query3.Where(o => o.GradeId == i).FirstOrDefault();

    Console.WriteLine("获取记录:{0}", (entity != null ? entity.GradeId : 0));}

2.   生成 SQL（类似的3条SQL）

exec sp_executesql N'SELECT TOP (1)

    [Project1].[GradeId] AS [GradeId],

    [Project1].[GradeName] AS [GradeName],

    [Project1].[Remark] AS [Remark]

    FROM ( SELECT

        [Extent1].[GradeId] AS [GradeId],

        [Extent1].[GradeName] AS [GradeName],

        [Extent1].[Remark] AS [Remark]

        FROM [dbo].[Grade] AS [Extent1]

        WHERE ([Extent1].[GradeName] LIKE N''%年级%'') AND ([Extent1].[GradeId] = @p__linq__0) AND (@p__linq__0 IS NOT NULL)

    )  AS [Project1]

    ORDER BY [Project1].[GradeId] DESC',N'@p__linq__0 int',@p__linq__0=1

3.   分析

1)   每次遍历，都会生成不同的SQL，并查询数据库。

2)   并将条件以 AND（与）进行追加。

3)   通常情况下，不建议这样去遍历查询，可以先查询出所需的记录，再进行遍历。

 

8.   使用对象集合进行连接查询

1)   LINQ 语句

Class1[] objects = new Class1[]

{

    new Class1 { Id = 1, Name = "Name1" },

    new Class1 { Id = 2, Name = "Name2" },

    new Class1 { Id = 3, Name = "Name3" }

};

var query1 = (from t1 in objects

                join t3 in dbContext.Grades on t1.Id equals t3.GradeId

                where t1.Name.Contains("2") && t3.GradeName.Contains("二")

                select new { t3.GradeId, t1.Name, t3.GradeName });

foreach (var item in query1)

{

    Console.WriteLine("获取记录:{0},{1},{2}", item.GradeId, item.Name, item.GradeName);

}

2)   生成 SQL

SELECT

    [Extent1].[GradeId] AS [GradeId],

    [Extent1].[GradeName] AS [GradeName],

    [Extent1].[Remark] AS [Remark]

    FROM [dbo].[Grade] AS [Extent1]

3)   执行结果

4)   分析

1.   可见，以对象集合关联查询，生成的查询 SQL 中并没有任何 where 条件。

2.   首先查询所有的“右表数据”，再在程序中进行过滤。

3.   注意：这种关联查询并不是一种标准查询，并且对象集合必须为左表（t1的位置）。否则将抛出异常：System.NotSupportedException，无法创建“EFDBFirst6_0.Class1”类型的常量值。此上下文仅支持基元类型或枚举类型。

4.   最后，不推荐使用该方式连接查询，影响正常思维和效率。