That's what you get for being an early adopter...
-
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
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.
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
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.
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
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... :sigh:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
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... :sigh:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
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).
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
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... :sigh:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
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 -
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, 2013Ahem. :rolleyes:
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
Ahem. :rolleyes:
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
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 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, 2013I agree and I suggested that exact same thing to him 26 mins before you did, it's right above your post. :-D
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
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, 2013Oh well. :)
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
-
I agree and I suggested that exact same thing to him 26 mins before you did, it's right above your post. :-D
Nish Nishant Consultant Software Architect Ganymede Software Solutions LLC www.ganymedesoftwaresolutions.com
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 -
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
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
-
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
Stop using EF and just use SQL and a data access layer... :)
Steve Naidamast Sr. Software Engineer Black Falcon Software, Inc. blackfalconsoftware@outlook.com
-
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 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 :wtf: :omg: Of course we're only dealing with sets here, so there's no real need to support any set operators :sigh: I know .NET Core and EF Core aren't exactly proven technologies yet, but come on... :wtf:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
Till fixed by EF Team, you can run a plain SQL from Entity Framework Core 2.0 and still map to your List if I am right? :)
I'll have to check that out. Don't know about it, but thanks for the tip :thumbsup:
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
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.
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
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 :D
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
-
Okay, so do you know why some people call it the BLEEDING EDGE?
-
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 :D
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
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
-
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 :sigh: 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 :)
Best, Sander Continuous Integration, Delivery, and Deployment arrgh.js - Bringing LINQ to JavaScript Object-Oriented Programming in C# Succinctly
