Framework Madness!

And other adventures C# and asp.net …

Posts Tagged ‘mysql

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 , ,

MySql Entity Framework Support Delayed

with one comment

Looks like we’ll have to wait a bit longer for the delayed Connector/net 6.0 with EF support. Looks like Sun is having ‘issues’ with the SQL generator and a rewrite for that part of the engine is underway.

http://forums.mysql.com/read.php?38,235788,241111#msg-241111

Written by Lynn Eriksen

January 16, 2009 at 10:57 am

Posted in Uncategorized

Tagged with