Welcome to the Lounge
The Lounge is rated Safe For Work. If you're about to post something inappropriate for a shared office environment, then don't post it. No ads, no abuse, and no programming questions. Trolling, (political, climate, religious or whatever) will result in your account being removed.
|So I have this project in .NET Core 2.0 with Entity Framework Core 2.0.
I have this entity which can be linked to other records of that same entity, let's say people.
So we have John, Bob, and Mary.
Mary can be linked to John and Bob.
My goal is simple, get all people that are not yet linked to the current person.
The LINQ query looks something like this:
var result = context.Persons.Select(p => p.Name)
.Except(context.PersonLink.Where(pl => pl.PersonId == x)
.Select(pl => pl.PersonLink.Name))
.OrderBy(n => n)
.Skip(a).Take(b).ToList();And I'd except the SQL To look something like this:
FROM PersonLink pl
JOIN Person p ON p.Id = pl.PersonLinkId
WHERE pl.PersonId = x
ORDER BY Name
OFFSET a FETCH NEXT b ROWS ONLYAwfully easy.
Except EF Core messes this up.
It first gets the first set, then the second set, does the comparison in memory and, thus, the offset fetch in memory as well.
The result, in this case, is that the entire Person table is send to my client app, 8000 records, instead of the 100 I'm asking for.
It get's worse, let's say 7999 people are linked, I now have to get 15999 records to my client to end up with 1
Checked my code, my DbContext, Entity classes, re-read my LINQ query a thousand times, even tried it in EF6, but everything seemed fine.
Then I stumbled upon Query: Translate IQueryable.Concat/Union/Intersect/Except/etc. to server · Issue #6812 · aspnet/EntityFrameworkCore · GitHub[^]
It seems EF Core simply doesn't translate set operators to SQL
Of course we're only dealing with sets here, so there's no real need to support any set operators
I know .NET Core and EF Core aren't exactly proven technologies yet, but come on...
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.