堆栈溢出!
我有一个 MS SQL 数据库。该数据库的部分显示在下一张图片中
我正在尝试进行可组合查询,我试图在其中查找具有特定 evEventKindID 事件的患者。例如,我想找到具有 (evEventKindtID == 1) 事件和 (evEventKindtID == 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].[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],
(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].[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],
(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],
[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],
(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
的常见混淆。引用变量的查询在执行查询时获取其参数值,而不是在绑定(bind)参数时。所以你可以有
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
循环有一个变量 limit
。您多次引用它,但这些多个引用都看到相同的值。这就是为什么你看到N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=31,@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
}
关于c# - ADO .NET EF 中的可组合查询每次迭代返回更多实体,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12238509/