Framework Madness!

And other adventures C# and asp.net …

Posts Tagged ‘entity framework

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

EntityDataSource Paging problems with providers for MySql

with one comment

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.

Snoopy should always be allowed!!!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.

Written by Lynn Eriksen

June 22, 2009 at 10:42 pm

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

with one comment


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

Written by Lynn Eriksen

June 10, 2009 at 9:24 pm

Ado.Net Data Services – Are you experienced?

with one comment

I am in the process of deploying my first project using Ado.net Data Services using an Entity Framework Context and I thought I would share some of my experience.

1) Authentication

Data services comes with the ability to apply credentials on the context, but if you’re wanting to roll-your-own method of passing credentials (such as a cookie) you’re not out of luck. The client ObjectContext supports a “SendingRequest” that is fired every time an HttpWebRequest object is created. In the args object you can get access to the web request. The code looks something like this:

private static void Context_SendingRequest(object sender, SendingRequestEventArgs e)
{
   if (e != null && (e.Request as HttpWebRequest != null))
   {
       var Request = e.Request as HttpWebRequest;
       Request.CookieContainer = new CookieContainer();
       Request.CookieContainer.Add(new Cookie("token", "value") { Domain = Request.RequestUri.Host });
   }
}

You can then use the cookie you in the service by overriding the “OnStartProcessingRequest” method.

   1: protected override void OnStartProcessingRequest(ProcessRequestArgs args)
   2: {
   3:         try
   4:         {
   5:             var Cookie = HttpContext.Current.Request.Cookies["token"];
   6:  
   7:             var Ticket = FormsAuthentication.Decrypt(Cookie.Value);
   8:  
   9:             if (Ticket != null && Ticket.Expired)
  10:             {
  11:                 throw new DataServiceException("");
  12:             }
  13:  
  14:  
  15:         }
  16:         catch (Exception)
  17:         {
  18:  
  19:             throw new DataServiceException("Invalid Login");
  20:         }
  21:     
  22:     base.OnStartProcessingRequest(args);
  23: }

2) .SVC mapping in IIS

For the .svc mime type mapping in IIS you’ll need to make sure the “POST”, “PUT’” and “DELETE” methods are supported.

 

3) Object Context  and connection string

After having trouble creating an object context using an .edmx file directly in an asp.net web site I moved it do a class library and everything seems fine now. Also, it’s very important to make sure your connection string is formatted correctly. When you create and ObjectContext using .edmx it will create a connection string in the .config file that corresponds to the class name of the object context. Additionally, it is very important  the connection string “metadata” section be formatted as “metadata=res://*/Context.csdl|res://*/Context.ssdl|res://*/Context.msl;”. And this is where it gets to be interesting. When you create the .edmx file a custom Object Context is created, but also portions of the .edmx file are embedded as resources in the compiled assembly. The “res://*/” paths, which are pipe separated (“|”), need to correspond to these resource names in the compiled assembly. If you don’t don’t know the names you can always use RedGate Reflector to find them. Thanks to Danny Simmons at Microsoft for pointing the right me in the right direction.

 

4) Turn on debugging output if needed.

There are two things you can do to get at error messages in the service output if necessary.

  1. Apply the ServiceBehavior attribute to the service. When you do this you want to include (“IncludeExceptionDetailInFaults = true”).
  2. In the “InitalizeService” method include “config.UseVerboseErrors = true”.

This will allow you to get at the exception details. Make sure you turn off when in production.

Written by Lynn Eriksen

March 24, 2009 at 10:36 am

Ado.net Entity Framework/Data Services – First Impressions

leave a comment »

Sorry, I have been away for a while. Been sick and busy, a frustrating combination. But … I digress. I have started to use Ado.net Entity Framework/Data Services on a project I am working on. So far it has been a very mixed experience.

Thus far I have really enjoyed working with the EDM in general and with the Data Services managing data thru n-tier is actually. Seriously easy. All you need to do is create a database, map it with a .edmx file, wire it up to an ado.net data service, create a client of some kind away you go. At a modest pace you can do that in about 15 minutes. Of course, this is the most simple scenario and would fit the security concerns of a deployment scenario. The end result is an experience that is vastly superior to using DataSets or trying to move objects between tiers, mainly because of LINQ.

What is been the largest pain point has been .edmx and the designer, especially in light of what the Ado.Net team demoed at the 2008 PDC. The Entity Framework Futures session demonstrated coming functionality (hopefully) that would all one to map POCO objects straight to an ObjectContext sans xml mapping file (.edmx) using attributes.  This would be so much easier than having to use .edmx and its verbose mapping scheme. How enjoyable the experience would be if you could just declare an ObjectQuery property in a custom ObjectContext and  use attributes for table and association mapping. It wouldn’t be as fancy, but it should would be direct and I think less cumbersome than current requirement of an .edmx mapping file.   I understand the necessity of .edmx in certain situations, but I wonder how much time the team could have saved if they would punted an XML representation down the road .Net 4.0. They could have had much more time to develop a good designer and perhaps used XAML 2009 for their representation. Working with the designer is tragic and reminds me of designing typed datasets in .net 1.0 Right now I find I often have to close/open a solution because sometimes the designer refuses to open, and then when working in the Designer the experience is buggy or incomplete. For example, when you set an association between entities the designer doesn’t clean up and leaves you to deal with a cryptic error. That’s just one example, there are more.

So what’s the conclusion? I wouldn’t go back to dataset or rolling my own object because the combination of Ado.net Entity Framework/Data Services brings too much to the table. I would rather work around the current short comings as they prepare for their next release because this combination of tech going forward brings too much value and ultimately save too much time to be ignored. But right now I would say this is clearly a leading edge and requires a good amount of “homework” to get right. Venture at your own risk, but so far I think it’s worth it.

Written by Lynn Eriksen

February 11, 2009 at 5:47 pm