c# – ADO .NET中的可组合查询每次迭代都会返回更多实体
发布时间:2020-12-15 21:39:34 所属栏目:百科 来源:网络整理
导读:堆栈溢出! 我有一个MS SQL数据库.该数据库的一部分将在下一张图片中显示 我正在尝试进行可压缩的查询,我正在尝试查找具有特定evEventKindID事件的患者.例如,我想找到具有(evEventKindtID == 1)事件和具有(evEventKindID == 1)事件的患者. var query = from
堆栈溢出!
我有一个MS SQL数据库.该数据库的一部分将在下一张图片中显示 我正在尝试进行可压缩的查询,我正在尝试查找具有特定evEventKindID事件的患者.例如,我想找到具有(evEventKindtID == 1)事件和具有(evEventKindID == 1)事件的患者. var query = from pt in db.tblPatient select pt; var list = query.ToList();// {1} foreach (var limit in group.limits.Values) { if (limit.eventKind.Type == TypeOfEventKind.ekEvent) { query = from pt in query where (pt.tblEvent.Count(j => j.evEventKindID == limit.eventKind.ID) > 0) select pt;// {2} list = query.ToList(); MessageBox.Show(query.Count().ToString()); } } 问题是每次下一次迭代都可以返回比前一次更多的元素.它为我辩护.如何从查询中查询返回比第一个查询更多的实体? 在SQL Server Profiler中,我发现了由ADO .NET EF生成的SQL查询.在{1}的地方: SELECT [Extent1].[ptID] AS [ptID],[Extent1].[ptFullName] AS [ptFullName],[Extent1].[ptHomeAddress] AS [ptHomeAddress],[Extent1].[ptPhone] AS [ptPhone],[Extent1].[ptBirthDate] AS [ptBirthDate],[Extent1].[ptIsMale] AS [ptIsMale],[Extent1].[ptUserID] AS [ptUserID],[Extent1].[ptINN] AS [ptINN],[Extent1].[ptSNILS] AS [ptSNILS] FROM [dbo].[tblPatient] AS [Extent1] 在第一次迭代的位置{2}: exec sp_executesql N'SELECT [Project1].[ptID] AS [ptID],[Project1].[ptFullName] AS [ptFullName],[Project1].[ptHomeAddress] AS [ptHomeAddress],[Project1].[ptPhone] AS [ptPhone],[Project1].[ptBirthDate] AS [ptBirthDate],[Project1].[ptIsMale] AS [ptIsMale],[Project1].[ptUserID] AS [ptUserID],[Project1].[ptINN] AS [ptINN],[Project1].[ptSNILS] AS [ptSNILS] FROM ( SELECT [Extent1].[ptID] AS [ptID],[Extent1].[ptSNILS] AS [ptSNILS],(SELECT COUNT(1) AS [A1] FROM [dbo].[tblEvent] AS [Extent2] WHERE ([Extent1].[ptID] = [Extent2].[evPatientID]) AND ([Extent2].[evEventKindID] = @p__linq__0)) AS [C1] FROM [dbo].[tblPatient] AS [Extent1] ) AS [Project1] WHERE [Project1].[C1] > 0',N'@p__linq__0 int',@p__linq__0=29 在第二次迭代的地方{2}: exec sp_executesql N'SELECT [Project2].[ptID] AS [ptID],[Project2].[ptFullName] AS [ptFullName],[Project2].[ptHomeAddress] AS [ptHomeAddress],[Project2].[ptPhone] AS [ptPhone],[Project2].[ptBirthDate] AS [ptBirthDate],[Project2].[ptIsMale] AS [ptIsMale],[Project2].[ptUserID] AS [ptUserID],[Project2].[ptINN] AS [ptINN],[Project2].[ptSNILS] AS [ptSNILS] FROM ( SELECT [Project1].[ptID] AS [ptID],[Project1].[ptSNILS] AS [ptSNILS],(SELECT COUNT(1) AS [A1] FROM [dbo].[tblEvent] AS [Extent3] WHERE ([Project1].[ptID] = [Extent3].[evPatientID]) AND ([Extent3].[evEventKindID] = @p__linq__1)) AS [C1] FROM ( SELECT [Extent1].[ptID] AS [ptID],(SELECT COUNT(1) AS [A1] FROM [dbo].[tblEvent] AS [Extent2] WHERE ([Extent1].[ptID] = [Extent2].[evPatientID]) AND ([Extent2].[evEventKindID] = @p__linq__0)) AS [C1] FROM [dbo].[tblPatient] AS [Extent1] ) AS [Project1] WHERE [Project1].[C1] > 0 ) AS [Project2] WHERE [Project2].[C1] > 0',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=31,@p__linq__1=31 你怎么看待这个问题? 解决方法
这是与foreach的常见混淆.引用变量的查询在执行查询时获取其参数值,而不是在绑定参数时获取.所以你可以拥有
int orderId = 1; var query = from o in context.Orders where o.Id == orderId; orderId = 2; MessageBox.Show(query.Single().Id.ToString()); // shows that order 2 was retrieved 在您的情况下,您的foreach循环有一个变量限制.你多次引用它,但那些多个引用都看到相同的值.这就是你看到的原因 N'@p__linq__0 int,@p__linq__1=31 两个参数都具有值31,第一次迭代中的29都消失了. 最简单的方法是每次创建一个新变量: foreach (var limit in group.limits.Values) { var locallimit = limit; // refer to locallimit in your query,not to limit } (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |