|
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:
SELECT Name
FROM Person
EXCEPT
SELECT Name
FROM PersonLink pl
JOIN Person p ON p.Id = pl.PersonLinkId
WHERE pl.PersonId = x
ORDER BY Name
OFFSET a FETCH NEXT b ROWS ONLY Awfully 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...
|
|
|
|
|
Sander Rossel wrote: EF Core simply doesn't translate set operators to SQL
Ok, that is a pretty serious problem. Which also hopefully means it will be fixed pretty quickly.
|
|
|
|
|
Thought so too, but one of the people on that GitHub thread says "Changing this to producing a single SQL query on the server isn't currently a top priority because we believe that for many common scenarios (e.g. for the sample queries you posted) there isn't a significant difference."
It was ultimately planned for the 2.1 release, but I've updated to the latest 2.1 preview and it's not yet fixed, so...
|
|
|
|
|
Ouch, tough luck.
I would say it's a good idea to move that piece of code into a stored proc (until they fix this).
|
|
|
|
|
It's time to write a stored proc.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Ahem. 
|
|
|
|
|
I don't undesratnd the "ahem". He's using Entity Farcework, and trying to use Linq to get his collection. If he writes a stored proc, he avoids EF's lack of ability, and gets back exactly the results he's expecting instead of all 8000 records.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
I agree and I suggested that exact same thing to him 26 mins before you did, it's right above your post. 
|
|
|
|
|
Ahh, I don't read a lot of your stuff. It's too sensitive and touchy-feely. 
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Oh well.
|
|
|
|
|
u seemed to be an early adopter
Caveat Emptor.
"Progress doesn't come from early risers – progress is made by lazy men looking for easier ways to do things." Lazarus Long
|
|
|
|
|
Till fixed by EF Team, you can run a plain SQL from Entity Framework Core 2.0 and still map to your List<person> if I am right?
|
|
|
|
|
I'll have to check that out.
Don't know about it, but thanks for the tip 
|
|
|
|
|
|
Can't you achieve your goal like this?
var result = context.Persons
.Where(p=> !p.PersonLink.Any(pl=>p.Id == pl.PersonLinkId))
.Select(p => p.Name)
.OrderBy(n => n)
.Skip(a).Take(b).ToList();
from the moment you use a select your're requesting a projection.
Paulo Gomes
Measuring programming progress by lines of code is like measuring aircraft building progress by weight.
—Bill Gates
Everything should be made as simple as possible, but not simpler.
—Albert Einstein
|
|
|
|
|
Yeah, that might work.
I was thinking I could do a right join and select everything that can't be joined.
I guess this is the same, but different
Perhaps it's easier to just write some SQL for readability and clarity.
|
|
|
|
|
Stop using EF and just use SQL and a data access layer...
Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com
|
|
|
|
|
Steve Naidamast wrote: a data access layer
Like EF...? 
Ok, I know what you mean, but I'm not going back to writing weakly typed strings if I can have objects, strong types, intellisense and automatic mapping
|
|
|
|
|
I understand your desire for strongly typed data upon the return of your queries. I prefer the same myself so what I do is after I have retrieved my data using a data access layer, I load the items into a structure object in the business-logic tier, which has all of the fields defined that I require and then return an array-list of such objects to my front-end.
I have been doing this for many years and have never had a problem with the technique.
Using an ORM is fine if it is actually needed such as for example, a new application that will require access to a very large, existing database. In this case you may want to opt for an ORM to get the structures of your data more quickly accessible to you.
However, ORMs tend to be on the "heavy" side in terms of tiers, making them somewhat inefficient.
In addition, using any ORM forces one to learn the idiosyncrasies of that ORM as well as the interface language. In EF's case, that would be LINQ, though you can also use EF-SQL. The result is that you become fluent in a single ORM instead of the more general language of standard SQL.
It is of course a matter of personal preference but I am one who does not like to stray too far from the basics since most of the many tools today are always in a state of flux, which can cause more problems than they are worth at times.
Just saying... 
Steve Naidamast
Sr. Software Engineer
Black Falcon Software, Inc.
blackfalconsoftware@outlook.com
|
|
|
|
|
Steve Naidamast wrote: However, ORMs tend to be on the "heavy" side in terms of tiers, making them somewhat inefficient.
I'd gladly sacrifice some milliseconds for the days I save in development 
Steve Naidamast wrote: using any ORM forces one to learn the idiosyncrasies of that ORM as well as the interface language. In EF's case, that would be LINQ, though you can also use EF-SQL. The result is that you become fluent in a single ORM instead of the more general language of standard SQL.
I don't think you can be fluent in LINQ-to-Entities without being fluent in "bare" SQL as well.
Not being fluent in LINQ creates horrible SQL, which makes it a horrible LINQ query (no matter how "well" your LINQ query is written)
Learning how .NET translates LINQ to SQL took some time, but I thought I managed pretty well.
Until I found out EF Core doesn't support set operators that is 
I know what you mean though, and it's a shame that many developers are writing LINQ without knowing SQL.
I do keep my entities in its own data layer and only return "real" POCO's to my other layers though.
In theory, I could create a project, implement some interfaces, write a bunch of ADO.NET, and replace my entire EF with ADO.NET in a single project.
So you and I more or less do the same, but a little different
|
|
|
|
|
Okay, so do you know why some people call it the BLEEDING EDGE?
|
|
|
|
|
I do now 
|
|
|
|
|
EF supports stored procedures; that's what they're for.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
Not bad, huh? Easy to remember
|
|
|
|
|
Too late 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
