堆栈溢出!

我有一个 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/

10-09 01:08
查看更多