0

Maybe it's by design, but I have this strange example:

var attendees = await _dbContext.Attendees
    .Include(x => x.CourseAttends.Where(y => y.Status == "attended"))
    .ToListAsync();

I know that the first attendee in the query has 2 CourseAttends. The first with status attended and the second with cancelled.

If I do a foreach, the query returns a single CourseAttend for the Attendee. That's good.

Now, if I want a list of attendees, that have only 1 attended CourseAttend, I change the query to:

var attendees = await _dbContext.Attendees
    .Include(x => x.CourseAttends.Where(y => y.Status == "attended"))
    .Where(x => x.CourseAttends.Count == 1)
    .ToListAsync();

This time the first attendee is NOT included in the query, because it counts 2 (the 'cancelled' is included).

Is that how it should work?

I'm aware that I could build the query different, like:

var attendees = await _dbContext.Attendees.Where(x => x.CourseAttends.Count(y => y.Status == "attended") == 1))
.ToListAsync();

...but my real query is far more complex.

THE SQL FROM EF:

SELECT [a].[Id], [a].[Email], [a].[FirstName], [a].[LastName], [t].[Id], [t].[AttendeeId], [t].[Status] FROM [Attendees] AS [a] LEFT JOIN (     SELECT [c].[Id], [c].[AttendeeId], [c].[Status]     FROM [CourseAttendees] AS [c]     WHERE [c].[Status] = N'attended' ) AS [t] ON [a].[Id] = [t].[AttendeeId] WHERE (     SELECT COUNT(*)     FROM [CourseAttendees] AS [c0]     WHERE [a].[Id] = [c0].[AttendeeId]) = 1

It looks like EF counts ALL the attendees CourseAttends and not just the ones with status 'attended'?

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
Mads
  • 385
  • 1
  • 5
  • 18
  • 2
    Filtered Include is an EF Core 5 feature. – Panagiotis Kanavos Jan 22 '21 at 15:56
  • You're right, by bad. The .net core version is 3.1, the ef core is 5.0.2. Will change the tags. – Mads Jan 22 '21 at 15:59
  • In general, when in doubt, inspect the generated sql statement, and more often than not, that will answer your question. – insane_developer Jan 22 '21 at 15:59
  • Indeed, there's no indication that there's any problem in this question. No source data, no actual or expected results, no SQL query. It's quite likely that the data is *not* what you think it is. Since the question is about the generated SQL query, post it. – Panagiotis Kanavos Jan 22 '21 at 16:01
  • Do not add code or detalis in comments. Instead, [edit] your question and put them there where they can be seen as part of the question. – Ken White Jan 22 '21 at 17:09
  • You say your query is more complex, so I can imagine the actual generated SQL for that. Have you written an optimized version yourself in SQL? If so, consider calling it as a stored proc. I would definitely use a CTE here. – insane_developer Jan 22 '21 at 18:26
  • @insane_developer I might consider that. At first it would be nice to hear, if it's by design that the filtering in the select is not reflected in the where clause. The SQL example I posted from EF seems to show that. I just thought that EF was 'smarter'. – Mads Jan 22 '21 at 18:49
  • If I execute the whole query, as you shared before, I get no results (I should have gotten 1 record in my example). If I skip the where clause that selects the count, I get all the "attended" records only, so that filter does work. – insane_developer Jan 22 '21 at 19:23
  • @insane_developer I'm not sure what you mean. But no matter what I do, I can't make the query with the `.CourseAttends.Count` work. I know the attendee have one 'attended' and one 'cancelled'. The query with the .Count don't return anything when I set it equals 1, but only when I set it equals 2. The raw SQL shows that too, I think: `SELECT COUNT(*) FROM [CourseAttendees] AS [c0] WHERE [a].[Id] = [c0].[AttendeeId]) = 1` – Mads Jan 25 '21 at 07:34
  • You get what you ask: `_dbContext.Attendees.Where(x => x.CourseAttends.Count == 1)`. With or without `Include` the where clause determines the filtering. The `Inlcude` only determines the *content* of the result. I consider [this](https://stackoverflow.com/q/64065812/861716) a duplicate. – Gert Arnold Feb 26 '21 at 15:05

0 Answers0