It was the EF providers. EntityDataSource off the hook.

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!

EntityDataSource Paging problems with providers for MySql

Last week I was working on a small internal project that involved taking payment info, processing it thru a gateway, and saving to a database. I picked the Entity Framework for general data management and ASP.net Dynamic Data to create my admin. Everything was good.

So I have gotten my admin set up quickly (used the XML Asp.Net membership providers on CodePlex – http://xmlproviderlibrary.codeplex.com/) and I’m in the admin and trying to sort test payment records in the GridView. But it will not sort. So I look thru all the settings and everything appears correct. I try to look at the ESQL on the Selecting event but have a hard time finding that. I even eventually switch from dotConnect by Devart to Connector/net from MySql and still the same problem. No sorting – still! I feel like Snoopy in a hospital.

The only solution to get sorting was to TURN OFF AutoPaging – and that may be VERY problematic if database paging is turned off.  I really don’t want to return a large number of records for no reason, and MySQL does have paging support. So I am going to try to find some time to get make a sample project and sit down with Connector/Net, which I can get source for, and try to get at the bottom of it. Not sure when, but I’m hoping this blog post helps keep my feet to the fire.

 

Update: 2009-06-23 at 12:12 pm

Today I was able to confirm my suspicions.

When AutoPage is turned on for the EntityDataSource two commands are issued.

• The first command looks up to total number of rows.
• The second command applies the correct paging syntax at the end (for MySql this is ‘Limit start,count’) but the Order By syntax is ignored.

When paging is turned off there is a single command issued with out paging but they ‘Order By’ syntax is generated.

Update: 2009-06-24 at 11 am

Further investigation yesterday suggests this may be a issue with the providers in question or the EF assemblies in the framework, not the entity data source. I will try to investigate further today and post my finding to Microsoft, Devart and MySql.

Update: 2009-06-24 at 1:43 pm

Posted bug reports:

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

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=469985

Also posted to the Devart Entity Framework forum.

Asp.net MVC – Why?

I have been using ASP.net Web Forms since the 1.0 Beta 2 release in late 2001.  At the time of it’s release ASP.net as a whole eclipsed ASP classic in many ways. But like any technology its was designed to solve the technology of it’s time. However, in recent years many of these solutions that Web Forms solves are no longer problems, and quite often today much of what comprises Web Forms and it’s elaborate control architecture seems … unnecessary. Since ASP.net is really just making the .Net framework available for web page creation many of the functional decisions can be traced to browsers of the day. So what was that environment?

• IE 5/5.5, IE 5 for Mac, and Netscape for 4.x all had a very different support for html layout and css styling. ASP.net’s controls could adapt html css style output depending on what kind of browser was making the request. This would often result much different html and css being sent to the various browsers, depending on the control in question.
• The XmlHttpRequest object, the X in AJAX, existed primarily in IE for Windows at the time. ASP.net therefore requires full page post backs, but with the existence of control model that emulated state – that allowed for only one form per page and elaborate sever side control pipeline. If you have ever tried to write an elaborate server control you know.
• The JavaScript environments varied wildly between the browsers, so ASP.net aimed squarely at a creating a pseudo event-driven environment on the server that would allow for a more consistent programming environment. And sure – some control such as validators supported JavaScript in IE, but ASP.net would transparently run the same code on the server if the browser requesting the page didn’t measure up.

So it was a big win for lots of us developers, upcoming and established alike. That was 2000-2001.

Flash forward six or seven years later and all of the scenarios above can be easily handled by all major browsers on Windows, Mac and Linux either by the browser alone or with the assistance of JavaScript libraries. The success of the .net framework and ASP.net have brought in a bucket load of developers that find .net Framework to be powerful but ASP.net engineered. And those of us that have persisted have learned that simpler is almost always better, test if you want to make a living, and you should only have to write your code once. ASP.net Web Forms wasn’t really engineered for these concerns, so the case for adopting Model-View-Controller as an alternative web platform within ASP.net was born.

Entity Framework CTP with Code Only – Now Available

Looks like the EF crew kept their promise and we have the new code only features to play with. I’ll be trying to investigate over the next week or so.

Here is the link:

http://blogs.msdn.com/adonet/pages/feature-ctp-walkthrough-code-only-for-the-entity-framework.aspx

It’s our (lack of) philosophy – dummy! :) (diversion)

 

I’m still a fan of Reagan today as much as I was in high school, because he was a man of vision, courage and ideals. He was the kind of man that could inspire and teach. An authentic person … and probably the last truly American president we have had. Check out this speech:

http://www.fordham.edu/halsall/mod/1964reagan1.html

It’s greatness is not just in it’s timelessness but it’s daring: that men of reason and temperance could not just contemplate the possibilities of the future but could foster the principles that would undergird it. He believed that not only was important for us to freely seek a living for ourselves but to understand why it was a gift to do so. From that understanding not only would come prosperity but also community, a shared protection of our mutual self interest. These were not new ideas, but he was a man that was not afraid to say them and to be persistent about it. These are things I believe, try to live, and often fail at.

What is going on today is truly terrible. Spending is not the problem but the ultimate symptom. As a nation we are bankrupt, a nation that has lost it’s way. And we must at some point ask ourselves if we are the people that are still worthy of the ideas that Washington, Jefferson and Lincoln stood for. Are we a nation of men and women of  bold ideas, unwavering nerve, personal charity – visionaries looking towards a better future for ourselves and our children? Right now I say no we are not. We recognize this trait as something uniquely our heritage but it is missing yet we do not know why. We have become to enthralled with comforts, cowards seeking gain at others expense, irresponsible stewards of our minds, our resources and our future – and a people that above all must be entertained. At this point we are not worthy of the gift of God – to by his grace to live as free men.

And I will take responsibility for my self.

Thinking of Windows 8 (Take 1)

So Windows 7 RC is installed and know we know that it will RTM in late July. Then it’s time to “pony up” and buy and upgrade, unless of course a new machine comes into the works. But I digress. It’s time to think about Windows 8 and what could be in the works for developers. I think the one of the main themes Windows 8 will be about is providing relational data services into the OS. Why? Applications are becoming increasingly experiential, networked (local and internet) and data driven. The first two Microsoft has delivered on nicely as core Windows services, while the ‘data’ component has remained elusive. This is my BEST GUESS on what could be coming next.

So instead of telling the history – lets jump right in and get to the possibilities.

• The Win 8 OS would ship will a relational data service (RDS) engine. This will provide query, indexing and transaction services for RDS subscribers that can be of various types.
• The main default RDS subscriber built in would be Relational Entity containers. Data schema, access and updates would all be provided via the Entity Framework and will feature next generation file support from SQL server.
• Applications would be able to create their own RDS entity containers to store relational data and files. They will be able to set entity schema and access policies that can be controlled via group policy.
• Applications such as Libraries, Indexing and IE 9 would all benefit immediately from this kind of service being available. (Note: for IE 9 a more traditional SQL provider would be used.) Other applications such as Mail, Media and Photo applications could benefit from ‘enhanced options’ as well.
• It is quite possible these data features would ship as a part of the .net framework and have a degree of backwards compatibility – namely versions of Windows with transactional NTFS (Vista and Win 7).
• NTFS in Windows 8 would be enhanced to support special file containers that would enhance relational data container performance.
• Containers could be synchronized using next gen Sync Services.

Unlike WinFS which aimed to be a broad consumer-level release with a full set of features, add RDS services would be a developer-targeted release designed to move data service integration forward.

Entity Framework ‘Code Only’? If you take the red pill …

Updated: Alex James confirms that TPH, TPT and TPC inheritance models will be supported.

 

Well, if you have used the Ado.net Entity Framework so far you know he advantages and the drawbacks. Here are the big drawbacks:

1. You MUST use XML mapping.
2. POCO is NOT not straight forward.
3. No change tracking in the framework.
4. THE BUILT IN DESIGNER IS SEVERELY LACKING.

Yikes. That cause for a major turn off, or at least a cause for extreme caution. But let me tell you – after working with DataSets for 8 years (about 2/3 years too long) leveraging the Entity Framework with it’s companion Data Services are welcome relief.

Once you get past the designer issues the next big thing is the necessity for XML mapping. The syntax is overly verbose because it’s all encompassing. Not only does it contain DB schema mappings and Class mappings, but mappings between the two as well. If you need to make manual changes things can get messy and confusing in a hurry.

Today they EF team made their first official ‘Code Only’ post to generate initial buzz. The new features look to make XML mapping completely optional and POCO dirt simple. It looks like ‘the plan’ is leveraging the goodness of LINQ to make the meta mappings with code. Here is an excerpt of how a future CTP release (and hopefully the final product) could possible make custom mappings of tables and classes work:

 var builder = new ContextBuilder<MyContext>(); 

 builder[“products”] =  
  from c in builder.OfType<Product>() 
  select new { 
    pcode = p.ID, 
    p.Name, 
    cat = p.Category.ID 
  } 
);

 

The ContextBuilder object serves to apply metadata to a custom ObjectContext that you specify.  Since it is essentially a factory object, once the mappings are applied you can call the ‘create’ method on the builder get the Object Context. For the rest of the mapping details I’ll just lift another excerpt:

 

This snippet (above):

• Maps Product entities to the ‘prds’ table.
• Maps Product.ID to a ‘pcode’ column.
• Maps Product.Name to a ‘Name’ column.

Maps the FK under the Product.Category relationship to the ‘cat’ column.

 

To possibly be that concise and totally refactorable rocks! And they even supply an inheritance example to (table per hierarchy) in the post. The downside – right now it’s vaporware. But this direction is a really welcome change. I hope it gets fully integrated by Beta 2. Almost forgot, they plan to make this work to generate a database as well. Go figure.

Here is the link:

http://blogs.msdn.com/efdesign/archive/2009/06/10/code-only.aspx