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

weixin_53911379

于 2020-12-24 19:02:50 发布

阅读量265

点赞数

原文链接：https://www.cnblogs.com/abeam/p/9377167.html

版权

本文详细介绍了使用LINQ进行数据操作的各种技巧，包括插入数据、判断记录是否存在、左连接查询、处理不同类型字段的关联、一对多关系查询、使用IQueryable变量以及常见查询策略。通过具体的LINQ语句和生成的SQL，揭示了LINQ在处理复杂查询时的灵活性和实用性。

摘要由CSDN通过智能技术生成

插入数据

判断记录是否存在
左连接情况下，判断某字段是否为 null 的写法
连接查询关联不同类型（string & int）字段
关联表（一对多关系），一条语句查询主表与子表数据
使用 IQueryable 类型的变量，嵌入 LINQ 查询语法中
常见的查询技巧
使用对象集合进行连接查询
插入数据

LINQ 语句

DataContext.CustomerTodos.Add(entity);

DataContext.SaveChanges();

生成 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’

判断记录是否存在

LINQ 语句

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

生成 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

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

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

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

生成 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]

执行结果

clip_image001[3]

分析

这种写法其实看起来很怪异，为什么要用一个实体去判断是否被关联呢（这是同事善用的而一种写法，哈哈）？
可见，在生成的 SQL 中使用了 CASE 做了一下判断，使用右表的主键 Id，如果为 null，就输出默认值，否则正常输出。
其实，我们还可以使用可空类型接受右表的字段，可以省去 CASE 子句的判断。
连接查询关联不同类型（string & int）字段

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

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

生成 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''

分析

以上的 LINQ 语句在某些情况下是会抛异常的，System.NotSupportedException:“无法将类型“System.Int32”强制转换为类型“System.Object”。LINQ to Entities 仅支持强制转换 EDM 基元或枚举类型。”，因为本人测试时，相同的环境下，一个项目中可以正常执行，而另一个则会报错，原因不详！
可见，外键字段（CityId）为 varchar 类型，而主键（Id）字段为 int 类型，为了强制关联将 int 类型强制转为 varchar 类型，在生成的代码中也是如此。
这样一来执行的 SQL 语句变得非常复杂，所以在实际的开发中，千万不能这样去设计数据表的结构！这里仅仅是为了演示这种非法设计的导致的后果。
关联表（一对多关系），一条语句查询主表与子表数据

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

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

生成 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

执行结果：

数据库

clip_image002[3]

程序

clip_image004[3]

分析

可见，生成的 SQL 语句转换成了左连接，将主表与子表中所需的字段一起查询，在返回的数据集中，应该是 EF 帮我们以主表字段分组，并获取子表结果集的操作。是不是 EF 还是挺强大，帮我们省去了很多代码！？
以上语法虽然看上去不知道 LINQ 是如何处理的，但 LINQ 确实是支持的，而且比较简单好理解。
我们除了使用以上语法，也可以类似的直接写左连接在程序中进行分组，再取明细结果集的方式。（其实与以上执行的 SQL 语句是一样的，这个根据自己的习惯方式决定）
另外还有一种方式，先查询主表，在遍历或包含去查询子表（强烈不推荐该方式）。
使用 IQueryable 类型的变量，嵌入 LINQ 查询语法中

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

生成 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

执行结果

clip_image005[4]

分析

首先，我们定义了一个 IQueryable 类型的变量，然后在使用这个变量作为数据源，进行关联查询。可见，在生成的 SQL 中直接进行了内连接查询，并输出相关字段。
这种写法主要是可以方便多次查询，可以多次利用这个 allCustomers 变量进行查询，从而可以省去相同的代码。当然，这样的 IQueryable 变量也可以写的比较复杂，作用就显得比较明显了。
但是这种嵌套不必嵌套太多层，不然影响可读性，不便于分析和理解。
常见的查询技巧

遍历查询 IQueryable 对象

LINQ 语句

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

foreach (var item in query1)

{

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

}

生成 SQL

SELECT

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

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

[Extent1].[Remark] AS [Remark]

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

分析

一次性生成 SQL，再遍历结果集。
根据不同条件生成对应 SQL

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

}

生成 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

分析

每次遍历，都会生成不同的SQL，并查询数据库。
通常情况下，不建议这样去遍历查询，可以先查询出所需的记录，再进行遍历。
根据不同条件生成对应 SQL

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

生成 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

分析

每次遍历，都会生成不同的SQL，并查询数据库。
并将条件以 AND（与）进行追加。
通常情况下，不建议这样去遍历查询，可以先查询出所需的记录，再进行遍历。

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

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

}

生成 SQL

SELECT

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

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

[Extent1].[Remark] AS [Remark]

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

weixin_53911379

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
深入理解 LINQ to SQL 生成的 SQL 语句

插入数据判断记录是否存在左连接情况下，判断某字段是否为 null 的写法连接查询关联不同类型（string & int）字段关联表（一对多关系），一条语句查询主表与子表数据使用 IQueryable 类型的变量，嵌入 LINQ 查询语法中常见的查询技巧使用对象集合进行连接查询插入数据LINQ 语句DataContext.CustomerTodos.Add(entity);DataContext.SaveChanges();生成 SQL
复制链接

扫一扫