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

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

插入数据

  1. 判断记录是否存在

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

  3. 连接查询关联不同类型(string & int)字段

  4. 关联表(一对多关系),一条语句查询主表与子表数据

  5. 使用 IQueryable 类型的变量,嵌入 LINQ 查询语法中

  6. 常见的查询技巧

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

  8. 插入数据

  1. LINQ 语句

DataContext.CustomerTodos.Add(entity);

DataContext.SaveChanges();

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

  1. 判断记录是否存在
  1. LINQ 语句

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

  1. 生成 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
  1. 左连接情况下,判断某字段是否为 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();
  1. 生成 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]
  1. 执行结果

clip_image001[3]

  1. 分析
  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();
  1. 生成 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''
  1. 分析
  1. 以上的 LINQ 语句在某些情况下是会抛异常的,System.NotSupportedException:“无法将类型“System.Int32”强制转换为类型“System.Object”。LINQ to Entities 仅支持强制转换 EDM 基元或枚举类型。”,因为本人测试时,相同的环境下,一个项目中可以正常执行,而另一个则会报错,原因不详!

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

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

  4. 关联表(一对多关系),一条语句查询主表与子表数据

Ø 说明:我们经常会接触多表查询,比如一对多、多对多的查询。如果我们需要一条语句查询出主表与子表的数据,比如以下场景 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();
  1. 生成 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
  1. 执行结果:
  1. 数据库

clip_image002[3]

  1. 程序

clip_image004[3]

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

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

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

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

  5. 使用 IQueryable 类型的变量,嵌入 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();
  1. 生成 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
  1. 执行结果

clip_image005[4]

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

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

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

  4. 常见的查询技巧

  1. 遍历查询 IQueryable 对象
  1. LINQ 语句

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

foreach (var item in query1)

{

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

}

  1. 生成 SQL

SELECT

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

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

[Extent1].[Remark] AS [Remark]

FROM [dbo].[Grade] AS [Extent1]
  1. 分析
  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));

}

  1. 生成 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
  1. 分析
  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));}
  1. 生成 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
  1. 分析
  1. 每次遍历,都会生成不同的SQL,并查询数据库。

  2. 并将条件以 AND(与)进行追加。

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

  1. 使用对象集合进行连接查询
  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);

}

  1. 生成 SQL

SELECT

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

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

[Extent1].[Remark] AS [Remark]

FROM [dbo].[Grade] AS [Extent1]
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值