Composite query in ADO.NET EF returns more objects in each iteration

Question

Stackoverflow

I have an MS SQL database. Part of this database is shown in the following figure.

I am trying to make a complex query where I am trying to find patients who have had events with a specific evEventKindID. For example, I want to find patients who have an event with (evEventKindtID == 1) and an event with (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()); } } 

The problem is that each subsequent iteration can return more elements than the previous one. It makes me sad. How does a query from a query return more objects than the first query?

In SQL Server Profiler, I found SQL queries created by ADO.NET EF. In place {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] 

In the place {2} in the first iteration:

 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 

And in place of {2} in the second iteration:

 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 

What do you think of this problem?

Answer 1

This is a common confusion with foreach . Queries related to variables get the values ​​of their parameters when the query is executed, and not when the parameter is bound. So you can have

 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 

In your case, the foreach has one limit variable. You link to it several times, but these multiple links all have the same meaning. That's why you see

 N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=31,@p__linq__1=31 

Both parameters have a value of 31, 29 from the first iteration did not pass.

The easiest way is to create a new variable every time:

 foreach (var limit in group.limits.Values) { var locallimit = limit; // refer to locallimit in your query, not to limit }