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
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
-
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
:thumbsup: ;)
-
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
