Bug with Entity Framework using OrderBy,Skip,and Take

[leriksen71]

I have encountered a scenario where the Entity Framework provider (using dotConnect for MySQL) is not providing sorted results when using OrderBy, Skip and Take together. Here is some sample code:

1) If I execute the following statement:
TestModel.tblquarantine.OrderByDescending(q => q.MsgDate).Take(100).ToList();

This yields correctly sorted data as the Order By statements appear.

2) If I execute the following statement:
TestModel.tblquarantine.OrderByDescending(q => q.MsgDate).Skip(100).Take(100).ToList();

This does not yield correctly sorted data, the Order By parameters sever appear.

I first encountered the issue with the dotConnect driver and also found it in the MySql connector/net driver as well. Since it has source code available I was able to research the problem. My work with that driver may provide a few clues. Here is the link to a more detailed posting with sample data at the MySql forum.

http://bugs.mysql.com/bug.php?id=45723

I can also provide a small sample project that verifies the issue with the dotConnect driver v 5.20.29.0 for MySql.

[AndreyR]

Thank you for the report, but this is an Entity Framework limitation. Please refer to the following post for more details:
http://msdn.microsoft.com/en-us/library/bb896273.aspx

[leriksen71]

Hello Andrey,

I have had a look at the link you sent and the section on SKIP suggests that with Sql Server 2000 that using Order By and Skip together might not work. It suggests that this is a DB query problem with SQL server. What it doe not suggest is that the Order By syntax is completely ignored which is the case I am experiencing with the MySql driver.

If I call the following LINQ query:
TestModel.tblquarantine.OrderByDescending(q => q.MsgDate).Skip(100).Take(100)

I get the following SQL:

SELECT
c.QuarID AS QuarID,
c.MsgDate AS MsgDate,
c.MsgID AS MsgID,
c.RejectID AS RejectID,
c.Deliver AS Deliver,
c.Expire AS Expire,
c.ServerID AS ServerID
FROM eds_testing.tblquarantine AS c
limit 100,100

The 'Order By' expression is ignored.

As a paying customer and as some one who has spent some time investigating the issue I would expect a more indepth response.

Did you test the scenario?

[AndreyR]

This is an internal limitation of LINQ to Entities.
If any additional operations are performed after the ordering operation, there is no guarantee,
that the ordering will be preserved in those additional operations.
Information on this subject can be found here:
http://msdn.microsoft.com/en-us/library/bb896317.aspx

[leriksen71]

Andrey, while I found the link you provided above to be a more plausable scenario - further into the issue has provided me a solution for the problem.

When Skip() and Take() are used in conjunction the sorting information is located in the DbSkipExpression object instead of a seperate DbSortExpression object. Specifically, sorting information is stored as collection DbSortClause in the DbSkipExpression 'SortOrder' property.

When the method 'Visit(DbSkipExpression)' is called on DbExpressionVisitor-based sql generation object, the
DbSortClause objects inside the DbSkipExpression.SortOrder collection need to be processed and added to the SqlFragment. If not then the 'Order By' sql is never generated by the provider as a call to 'Visit(DbSortExpression)' is never made.

Not only have I verified this as a requirement for correct Sql generation by viewing Microsoft's SqlClient entity driver source through Reflector, I have just submitted a patch to the MySql connector/net project (http://bugs.mysql.com/bug.php?id=45723) and have solved this problem for their driver - which was giving me the exact same results as the dotConnect driver. Base on the construction of their driver the fix required 3 additonal lines of code.

I had purchased a copy of dotConnect professional several months ago and have been very, satisfied - until now. I would very much appreciate if you investigated the issue based on my findings and implemented a fix for the next driver release.

I will not be placated by another "It's not supported by the Framework" reply.

[AndreyR]

Thank you for your help, we have fixed the problem.
The fix will be included in the nearest build.

[damon.cognito]

Thank you for your help, we have fixed the problem.
The fix will be included in the nearest build.

I have the exact same problem using EF and dotConnect for PostgreSQL; using Take and Skip and orderby generates a SQL statement without the order by.

[AndreyR]

We are working on the PostgreSQL-specific fix. I will let you know as soon as it is available.

[AndreyR]

The fix is implemented. Look forward to the next build.