Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

net5.0 OrderBy problem #468

Closed
totpero opened this issue Dec 9, 2020 · 10 comments
Closed

net5.0 OrderBy problem #468

totpero opened this issue Dec 9, 2020 · 10 comments
Labels

Comments

@totpero
Copy link

totpero commented Dec 9, 2020

Hi,
I'm using aspboilerplate that use this nuget.
After update entityframework to version 5 and net5.0 this not longer work

When i run sqlprofiler on my query i saw that is translated into: order by (SELECT 3) and this is not ok;

Because this two query are not equals on result:
Select top 10 * from [pay].[table] order by 3 DESC

Select top 10 * from [pay].[table] order by (SELECT 3) DESC

@StefH StefH added the bug label Dec 9, 2020
@StefH
Copy link
Collaborator

StefH commented Dec 9, 2020

Hello @totpero, I'll take a look.

(Note that I'm also building a EF5 specific NuGet which could maybe solve your issue)

I keep you informed.

@StefH
Copy link
Collaborator

StefH commented Dec 9, 2020

Should a normal OrderBy not be like:
Select top 10 * from [pay].[table] order by id DESC instead of 3 desc ?

@njsent
Copy link

njsent commented Dec 9, 2020

Hi,
We're having the same issue with dynamic sql order by in .NET Core 5. What's strange is that everything works fine until I added 2 new columns to a sql server view, BillingIndLastName and BillingIndFirstName. I'm able to filter using these two columns with ef core linq to sql, but the dynamic OrderBy throws an error ("No property or field 'BillingIndLastName' exists in type 'MyView'") when I try to sort on these columns. There is an object in the EF5 class for this view named BillingInd and a property named BillingIndId - so I'm not sure if that's related to the problem. I will try to research more but will look for the EF5 nuget lib. Thank you for this awesome library!

@StefH
Copy link
Collaborator

StefH commented Dec 9, 2020

I've uploaded a preview for EF5 (Microsoft.EntityFrameworkCore.DynamicLinq 5.2.7-preview-03)
See https://www.myget.org/feed/Packages/system-linq-dynamic-core

@totpero
Copy link
Author

totpero commented Dec 9, 2020

Should a normal OrderBy not be like:
Select top 10 * from [pay].[table] order by id DESC instead of 3 desc ?

The boilerplate use datatables.net and the order is made by the number of the column. If the translated into order by 3 DESC insted of that will solve the problem order by (SELECT 3) DESC

@StefH
Copy link
Collaborator

StefH commented Dec 9, 2020

As far I can see, this code

var orderBy3 = context.Cars.OrderBy("3 DESC").ToList();

Does compile, however no order by is executed because the generated SQL does not have an order by generated:

SELECT [c].[Key], [c].[Brand], [c].[Color], [c].[DateDeleted], [c].[DateLastModified], [c].[Extra], [c].[NullableInt], [c].[Vin], [c].[Year]
      FROM [Cars] AS [c]

When using var orderByYear = context.Cars.OrderBy("Year DESC").ToList();
This results in SQL:

 SELECT [c].[Key], [c].[Brand], [c].[Color], [c].[DateDeleted], [c].[DateLastModified], [c].[Extra], [c].[NullableInt], [c].[Vin], [c].[Year]
      FROM [Cars] AS [c]
      ORDER BY [c].[Year] DESC

@totpero
Copy link
Author

totpero commented Dec 9, 2020

Yes, you have right, the .OrderBy("3 DESC") do nothing , but after that I have PageBy() and there is set the ORDER BY (SELECT 1) clause.
But in the previous verison of ef core this works fine OrderBy("3 DESC")

@StefH
Copy link
Collaborator

StefH commented Dec 9, 2020

OK. Closing this issue.

@njsent If you still have questions, raise a new issue regarding this.

@StefH StefH closed this as completed Dec 9, 2020
@njsent
Copy link

njsent commented Dec 10, 2020

I've uploaded a preview for EF5 (Microsoft.EntityFrameworkCore.DynamicLinq 5.2.7-preview-03)
See https://www.myget.org/feed/Packages/system-linq-dynamic-core

Unfortunately the new version did not fix the problem but I was able to solve the problem by SELECTing the last & first columns. These two columns were only used for dynamic OrderBy previously so I'm not sure if it's even related to .net core 5.0 or if I just have to always SELECT the columns that are used for dynamic OrderBy. I really appreciate you putting the 5.0 lib up so quickly anyway.

@StefH
Copy link
Collaborator

StefH commented Dec 10, 2020

#470

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

No branches or pull requests

3 participants