Framework Madness!

And other adventures C# and asp.net …

It was the EF providers. EntityDataSource off the hook.

with 3 comments

The title says it all. It was the EF providers I was looking at that cause my issues when using ‘OrderBy’, ‘Skip’ and ‘Take’ together. It all has to do with how the expression trees are created when using ‘Take’. Let’s look at the two scenarios.

1) Using ‘OrderBy’ and ‘Take’ (no ‘Skip’)

Here is the sample EF query:

TestModel.tblquarantine.OrderByDescending(q => q.MsgDate).Take(100).ToList();

Let’s take a closer look at the execution of this scenario thanks to MySql connector/net source:

  • The ‘Take’ syntax is translated into a DbLimitExpression and placed in the expression tree. 
    (see System.Data.Common.CommandTrees for this and other expression objects listed below)
  • The ‘OrderBy’ syntax is translated into a DbSortExpression and held in the DbLimitExpression’s ‘Argument’ property.
  • The expression tree is evaluated by the provider.
  • After the DbLimitExpression is evaluated to generate sql, the DbSortExpression is evaluated.
  • When the DbSortExpression is evaluated, the DbSortClause objects in the ‘SortOrder’ collection are simply looped over to produce the correct sql syntax.

In both the Devart and MySql providers for MySql the expected sql was returned.

2) Using ‘OrderBy’, ‘Skip’ and ‘Take’ together

Here is the sample EF query:

TestModel.tblquarantine.OrderByDescending(q => q.MsgDate).Skip(100).Take(100).ToList();

This is a very common scenario when  doing paging – and it fails in both the Devart and MySql EF providers. After several rounds at looking at the MySql EF provider source I stumbled upon the problem and the fix for the MySql connector/net provider.

Let’s take a closer look at the execution of this scenario thanks to MySql connector/net source:

  • The ‘Take’ syntax is translated into a DbLimitExpression and placed in the expression tree.
  • ‘Skip’ syntax is syntax is translated into a DbSkipExpression and passed the argument to the DbLimitExpression instead of a DbSortExpression.
  • This is key – the DbSkipExpression expression contains a ‘Count’ property for determining the number of rows to skip and the same ‘SortOrder’ collection used in a DbSortExpression. So, the DbSkipExpression contains both ‘Skip’ and ‘Order By’ information.
  • The expression tree is evaluated by the provider.
  • After the DbLimitExpression is evaluated to generate sql, the DbSkipExpression is evaluated.
  • In the MySql connector/net source only the ‘Count’ property is being evaluated at present. Evaluation of the ‘SortOrder’ collection is omitted, which cause all sorting to be omitted in the generated sql.
  • The Fix:  when the  DbSkipExpression is evaluated the ‘SortOrder’ collection should be evaluated after the Count property. Doing so adds sorting back to the generated sql. In limited testing this has produced the expected sql in all cases.

Now what was most curious is that this error is present in the Devart and MySql provider, which leads me to suspect that this may be in a bug in a sample provider provided by Microsoft to assist in the roll out of EF driver support. But to be fair, it could also just be coincidence.

My bug report of the MySql bug remains open – but I have filed several bugs and the guys that work in connector/net at MySql are usually really good about implementing fixes if they are appropriate. Curiously, my attempts at reporting same bug in the Devart EF forum, albeit with increasing levels of detail over three posts, have been swiftly dismissed as a “limit with the EF framework” and with questionable links to MSDN articles. As a paying customer of the their product (doConnect professional) and a holder of a masters of science (chemistry) I would expect more of an effort. At least show me that you investigated the issue and that a set of problems will result if you do the suggested fix. Devart – I want to comeback for dotconnect and dbForge fusion again. 🙂 Don’t push me away.

 

BTW – if you use Reflector to look the Microsoft EF sql client provider code they do process the DbSkipExpression’s ‘SortOrder’ collection – though in a more elaborate way.

Time to go to bed. See all you coders soon.

 

Updated 7/9/2009

Looks like Devart implemented a fix that will be rolled into their next driver release!
Much thanks!

Advertisements

Written by Lynn Eriksen

June 27, 2009 at 12:28 am

Posted in Uncategorized

Tagged with , ,

3 Responses

Subscribe to comments with RSS.

  1. For what it’s worth, we’ve done some research into our sample provider, and it doesn’t seem to have this problem. I’m not sure why both the MySql and Devart provider’s exhibit it.

    – Danny Simmons, Dev Manager of the EF team at Microsoft.

    Daniel Simmons

    June 30, 2009 at 9:09 pm

  2. Thanks for sharing this.
    MySQL commited a patch, which hasn’t made it to any releases yet, but building the source code wasn’t too hard.

    maxtoroq

    August 10, 2009 at 11:35 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: