EntityFramework Any 生成的sql语句超长。

今天早上在做一个新功能的时候发现了有一个bug.

跟踪的时候发现

 

Service Error: SQL 语句的某些部分嵌套过深。请重写查询或将其分解为若干较小的查询。. <!-- at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)-->

 

看一下我们的linq语句

   var query2 = ctx.Boxes.Where(b => PartyIds.Any(d => d == b.PartyID)); ParyIds是一个Int[]数组有72数字在里面

结果它生成的sql语句就超恐怖了。

 

SELECT
[Extent1].[Discriminator] AS [Discriminator],
[Extent1].[ID] AS [ID],
[Extent1].[PartyID] AS [PartyID],

FROM [dbo].[Boxes] AS [Extent1]
WHERE ([Extent1].[Discriminator] IN ('Inbox','Outbox','Box')) AND ( EXISTS (SELECT
1 AS [C1]
FROM (SELECT
[UnionAll70].[C1] AS [C1]
FROM (SELECT
[UnionAll69].[C1] AS [C1]
FROM (SELECT
[UnionAll68].[C1] AS [C1]
FROM (SELECT
[UnionAll67].[C1] AS [C1]
FROM (SELECT
[UnionAll66].[C1] AS [C1]
FROM (SELECT
[UnionAll65].[C1] AS [C1]
FROM (SELECT
[UnionAll64].[C1] AS [C1]
FROM (SELECT
[UnionAll63].[C1] AS [C1]
FROM (SELECT
[UnionAll62].[C1] AS [C1]
FROM (SELECT
[UnionAll61].[C1] AS [C1]
FROM (SELECT
[UnionAll60].[C1] AS [C1]
FROM (SELECT
[UnionAll59].[C1] AS [C1]
FROM (SELECT
[UnionAll58].[C1] AS [C1]
FROM (SELECT
[UnionAll57].[C1] AS [C1]
FROM (SELECT
[UnionAll56].[C1] AS [C1]
FROM (SELECT
[UnionAll55].[C1] AS [C1]
FROM (SELECT
[UnionAll54].[C1] AS [C1]
FROM (SELECT
[UnionAll53].[C1] AS [C1]
FROM (SELECT
[UnionAll52].[C1] AS [C1]
FROM (SELECT
[UnionAll51].[C1] AS [C1]
FROM (SELECT
[UnionAll50].[C1] AS [C1]
FROM (SELECT
[UnionAll49].[C1] AS [C1]
FROM (SELECT
[UnionAll48].[C1] AS [C1]
FROM (SELECT
[UnionAll47].[C1] AS [C1]
FROM (SELECT
[UnionAll46].[C1] AS [C1]
FROM (SELECT
[UnionAll45].[C1] AS [C1]
FROM (SELECT
[UnionAll44].[C1] AS [C1]
FROM (SELECT
[UnionAll43].[C1] AS [C1]
FROM (SELECT
[UnionAll42].[C1] AS [C1]
FROM (SELECT
[UnionAll41].[C1] AS [C1]
FROM (SELECT
[UnionAll40].[C1] AS [C1]
FROM (SELECT
[UnionAll39].[C1] AS [C1]
FROM (SELECT
[UnionAll38].[C1] AS [C1]
FROM (SELECT
[UnionAll37].[C1] AS [C1]
FROM (SELECT
[UnionAll36].[C1] AS [C1]
FROM (SELECT
[UnionAll35].[C1] AS [C1]
FROM (SELECT
[UnionAll34].[C1] AS [C1]
FROM (SELECT
[UnionAll33].[C1] AS [C1]
FROM (SELECT
[UnionAll32].[C1] AS [C1]
FROM (SELECT
[UnionAll31].[C1] AS [C1]
FROM (SELECT
[UnionAll30].[C1] AS [C1]
FROM (SELECT
[UnionAll29].[C1] AS [C1]
FROM (SELECT
[UnionAll28].[C1] AS [C1]
FROM (SELECT
[UnionAll27].[C1] AS [C1]
FROM (SELECT
[UnionAll26].[C1] AS [C1]
FROM (SELECT
[UnionAll25].[C1] AS [C1]
FROM (SELECT
[UnionAll24].[C1] AS [C1]
FROM (SELECT
[UnionAll23].[C1] AS [C1]
FROM (SELECT
[UnionAll22].[C1] AS [C1]
FROM (SELECT
[UnionAll21].[C1] AS [C1]
FROM (SELECT
[UnionAll20].[C1] AS [C1]
FROM (SELECT
[UnionAll19].[C1] AS [C1]
FROM (SELECT
[UnionAll18].[C1] AS [C1]
FROM (SELECT
[UnionAll17].[C1] AS [C1]
FROM (SELECT
[UnionAll16].[C1] AS [C1]
FROM (SELECT
[UnionAll15].[C1] AS [C1]
FROM (SELECT
[UnionAll14].[C1] AS [C1]
FROM (SELECT
[UnionAll13].[C1] AS [C1]
FROM (SELECT
[UnionAll12].[C1] AS [C1]
FROM (SELECT
[UnionAll11].[C1] AS [C1]
FROM (SELECT
[UnionAll10].[C1] AS [C1]
FROM (SELECT
[UnionAll9].[C1] AS [C1]
FROM (SELECT
[UnionAll8].[C1] AS [C1]
FROM (SELECT
[UnionAll7].[C1] AS [C1]
FROM (SELECT
[UnionAll6].[C1] AS [C1]
FROM (SELECT
[UnionAll5].[C1] AS [C1]
FROM (SELECT
[UnionAll4].[C1] AS [C1]
FROM (SELECT
[UnionAll3].[C1] AS [C1]
FROM (SELECT
[UnionAll2].[C1] AS [C1]
FROM (SELECT
[UnionAll1].[C1] AS [C1]
FROM (SELECT
89 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
UNION ALL
SELECT
88 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
UNION ALL
SELECT
87 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
UNION ALL
SELECT
86 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3]
UNION ALL
SELECT
85 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4]
UNION ALL
SELECT
84 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable6]) AS [UnionAll5]
UNION ALL
SELECT
83 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable7]) AS [UnionAll6]
UNION ALL
SELECT
82 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable8]) AS [UnionAll7]
UNION ALL
SELECT
81 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable9]) AS [UnionAll8]
UNION ALL
SELECT
80 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable10]) AS [UnionAll9]
UNION ALL
SELECT
79 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable11]) AS [UnionAll10]
UNION ALL
SELECT
78 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable12]) AS [UnionAll11]
UNION ALL
SELECT
77 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable13]) AS [UnionAll12]
UNION ALL
SELECT
76 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable14]) AS [UnionAll13]
UNION ALL
SELECT
75 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable15]) AS [UnionAll14]
UNION ALL
SELECT
72 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable16]) AS [UnionAll15]
UNION ALL
SELECT
71 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable17]) AS [UnionAll16]
UNION ALL
SELECT
70 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable18]) AS [UnionAll17]
UNION ALL
SELECT
69 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable19]) AS [UnionAll18]
UNION ALL
SELECT
68 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable20]) AS [UnionAll19]
UNION ALL
SELECT
67 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable21]) AS [UnionAll20]
UNION ALL
SELECT
66 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable22]) AS [UnionAll21]
UNION ALL
SELECT
65 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable23]) AS [UnionAll22]
UNION ALL
SELECT
64 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable24]) AS [UnionAll23]
UNION ALL
SELECT
63 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable25]) AS [UnionAll24]
UNION ALL
SELECT
62 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable26]) AS [UnionAll25]
UNION ALL
SELECT
61 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable27]) AS [UnionAll26]
UNION ALL
SELECT
60 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable28]) AS [UnionAll27]
UNION ALL
SELECT
59 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable29]) AS [UnionAll28]
UNION ALL
SELECT
55 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable30]) AS [UnionAll29]
UNION ALL
SELECT
54 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable31]) AS [UnionAll30]
UNION ALL
SELECT
53 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable32]) AS [UnionAll31]
UNION ALL
SELECT
52 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable33]) AS [UnionAll32]
UNION ALL
SELECT
51 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable34]) AS [UnionAll33]
UNION ALL
SELECT
50 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable35]) AS [UnionAll34]
UNION ALL
SELECT
49 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable36]) AS [UnionAll35]
UNION ALL
SELECT
48 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable37]) AS [UnionAll36]
UNION ALL
SELECT
47 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable38]) AS [UnionAll37]
UNION ALL
SELECT
46 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable39]) AS [UnionAll38]
UNION ALL
SELECT
45 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable40]) AS [UnionAll39]
UNION ALL
SELECT
44 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable41]) AS [UnionAll40]
UNION ALL
SELECT
43 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable42]) AS [UnionAll41]
UNION ALL
SELECT
42 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable43]) AS [UnionAll42]
UNION ALL
SELECT
41 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable44]) AS [UnionAll43]
UNION ALL
SELECT
40 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable45]) AS [UnionAll44]
UNION ALL
SELECT
39 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable46]) AS [UnionAll45]
UNION ALL
SELECT
38 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable47]) AS [UnionAll46]
UNION ALL
SELECT
37 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable48]) AS [UnionAll47]
UNION ALL
SELECT
36 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable49]) AS [UnionAll48]
UNION ALL
SELECT
35 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable50]) AS [UnionAll49]
UNION ALL
SELECT
34 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable51]) AS [UnionAll50]
UNION ALL
SELECT
33 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable52]) AS [UnionAll51]
UNION ALL
SELECT
32 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable53]) AS [UnionAll52]
UNION ALL
SELECT
31 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable54]) AS [UnionAll53]
UNION ALL
SELECT
30 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable55]) AS [UnionAll54]
UNION ALL
SELECT
29 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable56]) AS [UnionAll55]
UNION ALL
SELECT
28 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable57]) AS [UnionAll56]
UNION ALL
SELECT
27 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable58]) AS [UnionAll57]
UNION ALL
SELECT
26 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable59]) AS [UnionAll58]
UNION ALL
SELECT
25 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable60]) AS [UnionAll59]
UNION ALL
SELECT
24 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable61]) AS [UnionAll60]
UNION ALL
SELECT
23 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable62]) AS [UnionAll61]
UNION ALL
SELECT
22 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable63]) AS [UnionAll62]
UNION ALL
SELECT
21 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable64]) AS [UnionAll63]
UNION ALL
SELECT
20 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable65]) AS [UnionAll64]
UNION ALL
SELECT
19 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable66]) AS [UnionAll65]
UNION ALL
SELECT
18 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable67]) AS [UnionAll66]
UNION ALL
SELECT
17 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable68]) AS [UnionAll67]
UNION ALL
SELECT
16 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable69]) AS [UnionAll68]
UNION ALL
SELECT
3 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable70]) AS [UnionAll69]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable71]) AS [UnionAll70]
UNION ALL
SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable72]) AS [UnionAll71]
WHERE [UnionAll71].[C1] = [Extent1].[PartyID]
))

 


 

 换成另一种写法就ok了

var query = from it in ctx.Boxes where PartyIds.Contains(it.PartyID) select it;

生成的sql语句就跟我们正常写的差不多了
SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Discriminator] AS [Discriminator], 
[Extent1].[PartyID] AS [PartyID], 

FROM [dbo].[Boxes] AS [Extent1]
WHERE ([Extent1].[Discriminator] IN ('Inbox','Outbox','Box')) AND ([Extent1].[PartyID] IN (89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,72,71,70,69,68,67,66,65,64,63,62,61,60,59,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,3,2,1))


============================
 var query2 = ctx.Boxes.Where(b => PartyIds.Any(d => d == b.PartyID)); ParyIds是一个Int[]数组有72数字在里面
var query = from it in ctx.Boxes where PartyIds.Contains(it.PartyID) select it;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
你可以使用 Entity Framework 来操作 SQL Server 数据库。Entity Framework 是一个对象关系映射(ORM)框架,它可以帮助你通过代码来操作数据库,而不需要直接编写 SQL 查询语句。 首先,你需要安装 Entity Framework。你可以在 Visual Studio 的 NuGet 包管理器中搜索并安装 Entity Framework 相关的包。 接下来,你需要定义一个数据上下文(DbContext)类,该类表示数据库中的一组实体(表)。你可以在数据上下文类中定义 DbSet 属性,每个 DbSet 属性对应一个实体的集合。 然后,你可以使用 LINQ 查询语法来查询和操作数据库。Entity Framework 会将 LINQ 查询转换为相应的 SQL 查询语句,并执行在数据库中。 下面是一个简单的示例: ```csharp // 创建数据上下文类 public class MyDbContext : DbContext { public DbSet<Customer> Customers { get; set; } // 其他实体的 DbSet 属性 } // 定义实体类 public class Customer { public int Id { get; set; } public string Name { get; set; } // 其他属性 } // 使用数据上下文类操作数据库 using (var context = new MyDbContext()) { // 查询数据 var customers = context.Customers.Where(c => c.Name.StartsWith("John")).ToList(); // 插入数据 var customer = new Customer { Name = "John Smith" }; context.Customers.Add(customer); context.SaveChanges(); // 更新数据 customer.Name = "John Doe"; context.SaveChanges(); // 删除数据 context.Customers.Remove(customer); context.SaveChanges(); } ``` 这只是一个简单的示例,你可以根据具体的需求进一步学习和使用 Entity Framework 操作 SQL Server 数据库。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值