DotNetWise©

Sunday, December 12, 2010

LINQ to Entities string based dynamic OrderBy [EF CTP5 solution]

With all the respect for Davy Landman, I'm quoting his blog post happy that his example works with Entity Framework CTP5! Speaking of this, I've actually needed this for ExtJS remote store ordering and WCF Data Services custom query - so handful in many cases :)


WCF Data Services doesn't support certain parameters on custom query - but of course there is an work around by using dynamic ordering and passing ordering as a method parameter.

Query options $orderby, $inlinecount, $skip and $top cannot be applied to the requested resource


The problem I kept having was that at some point you’ll need to convert the GridView.SortExpression from your ASP.NET GridView to a Lamba Expression for your Queryable.OrderBy.

The challenge

You’ll get the string.

"OrderDate DESC"

And you have to translate that to:

ObjectContext.Orders.OrderByDescending(order => order.OrderDate)

In march/may I had spent a half day researching possibilities to solve this automatically. At that point I couldn’t find a clean solution. So I went with a generated solution (using T4 templates) which generated a big switch statement per entity.

A nice solution

We jump to november and I was browsing a little bit on StackOverflow to see if their were some interesting questions. My eye caught the question “How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?”, and it got me started to solve the sorting challenge. I had some hours available so I thought I’d give it another try.

There are a few other’s who have created a solution using the Expression building method, and I’ve combined it and refactored it to make it match more with the current Entity Framework. I’ve also added support for an infinite amount of child entities.


/***** BEGIN LICENSE BLOCK *****
* Version: MPL 1.1/GPL 2.0/LGPL 2.1
*
* The contents of this file are subject to the Mozilla Public License Version
* 1.1 (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
* http://www.mozilla.org/MPL/
*
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
* for the specific language governing rights and limitations under the
* License.
*
* The Original Code is LINQExtensions.StringFieldNameSortingSupport.
*
* The Initial Developer of the Original Code is
* Davy Landman.
* Portions created by the Initial Developer are Copyright (C) 2008
* the Initial Developer. All Rights Reserved.
*
* Contributor(s):
*
*
* Alternatively, the contents of this file may be used under the terms of
* either the GNU General Public License Version 2 or later (the "GPL"), or
* the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
* in which case the provisions of the GPL or the LGPL are applicable instead
* of those above. If you wish to allow use of your version of this file only
* under the terms of either the GPL or the LGPL, and not to allow others to
* use your version of this file under the terms of the MPL, indicate your
* decision by deleting the provisions above and replace them with the notice
* and other provisions required by the GPL or the LGPL. If you do not delete
* the provisions above, a recipient may use your version of this file under
* the terms of any one of the MPL, the GPL or the LGPL.
*
* ***** END LICENSE BLOCK ***** */
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace LINQExtensions
{
public static class StringFieldNameSortingSupport
{
#region Private expression tree helpers

private static LambdaExpression GenerateSelector<TEntity>(String propertyName, out Type resultType) where TEntity : class
{
// Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
var parameter = Expression.Parameter(typeof(TEntity), "Entity");
// create the selector part, but support child properties
PropertyInfo property;
Expression propertyAccess;
if (propertyName.Contains('.'))
{
// support to be sorted on child fields.
String[] childProperties = propertyName.Split('.');
property = typeof(TEntity).GetProperty(childProperties[0], BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
for (int i = 1; i < childProperties.Length; i++)
{
property = property.PropertyType.GetProperty(childProperties[i], BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
}
}
else
{
property = typeof(TEntity).GetProperty(propertyName, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
propertyAccess = Expression.MakeMemberAccess(parameter, property);
}
resultType = property.PropertyType;
// Create the order by expression.
return Expression.Lambda(propertyAccess, parameter);
}
private static MethodCallExpression GenerateMethodCall<TEntity>(IQueryable<TEntity> source, string methodName, String fieldName) where TEntity : class
{
Type type = typeof(TEntity);
Type selectorResultType;
LambdaExpression selector = GenerateSelector<TEntity>(fieldName, out selectorResultType);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, selectorResultType },
source.Expression, Expression.Quote(selector));
return resultExp;
}
#endregion
public static IOrderedQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string fieldName) where TEntity : class
{
MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "OrderBy", fieldName);
return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
}

public static IOrderedQueryable<TEntity> OrderByDescending<TEntity>(this IQueryable<TEntity> source, string fieldName) where TEntity : class
{
MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "OrderByDescending", fieldName);
return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
}
public static IOrderedQueryable<TEntity> ThenBy<TEntity>(this IOrderedQueryable<TEntity> source, string fieldName) where TEntity : class
{
MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "ThenBy", fieldName);
return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
}
public static IOrderedQueryable<TEntity> ThenByDescending<TEntity>(this IOrderedQueryable<TEntity> source, string fieldName) where TEntity : class
{
MethodCallExpression resultExp = GenerateMethodCall<TEntity>(source, "ThenByDescending", fieldName);
return source.Provider.CreateQuery<TEntity>(resultExp) as IOrderedQueryable<TEntity>;
}
public static IOrderedQueryable<TEntity> OrderUsingSortExpression<TEntity>(this IQueryable<TEntity> source, string sortExpression) where TEntity : class
{
String[] orderFields = sortExpression.Split(',');
IOrderedQueryable<TEntity> result = null;
for (int currentFieldIndex = 0; currentFieldIndex < orderFields.Length; currentFieldIndex++)
{
String[] expressionPart = orderFields[currentFieldIndex].Trim().Split(' ');
String sortField = expressionPart[0];
Boolean sortDescending = (expressionPart.Length == 2) && (expressionPart[1].Equals("DESC", StringComparison.OrdinalIgnoreCase));
if (sortDescending)
{
result = currentFieldIndex == 0 ? source.OrderByDescending(sortField) : result.ThenByDescending(sortField);
}
else
{
result = currentFieldIndex == 0 ? source.OrderBy(sortField) : result.ThenBy(sortField);
}
}
return result;
}
}
}

You can also download this file. All you have to do to use these extensions methods is add a using LINQExtensions;".

It's now very simple to write queries like this

var result = queryableOrders.OrderBy("Employee.LastName").ThenByDescending("Freight").ToList();

I’ve deliberately kept the parsing of the SortExpression out of these extension methods. In my opinion it should be kept outside of the string field names support. I’ve added a new extension method just for this purpose.

var result = queryableOrders.OrderUsingSortExpression("Employee.LastName, Freight DESC").ToList();

Bummer

When researching for writing this article I found the article Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library) by the Scott Guthrie, which does exactly as I developed. The only reason I still post this code is that it’s less heavy and not under de Microsoft Permissive License (but under a very flexible triple license).

So decide for yourself, use this code directly or download the samples pack and locate the DynamicLinq folder and use that library (which has some very cool stuff in it).

Monday, June 14, 2010

'.ashx' has not been pre-compiled, and cannot be requested.

Recently we had to deploy a Asp.Net .Net 3.5 website to a IIS7 Server (this application was previously hosted only on IIS 6.*). All went fine, just that some handlers (used to generate thumbnails - .ashx) when accessed were generating the following error:
'.ashx' has not been pre-compiled, and cannot be requested.
Afer a little investigation we came up with 2 solutions:
  • First solution - not recommended: App Pools - Managed Pipeline Mode changed it from Integrated to Classic Not recommended because you are basically giving up an all good IIS7 functionality - the common event pipeline. (More info)
  • Second solution - recommended: Move/copy the content of
    system.web -
    httpHandlers
    to
    system.webServer
    - handlers

    In this case you'll might need to consider several other attributes for a handler (More info)
We wrote this blog entry because searching by the actual error message we didn't find any useful result. Hope will help.

Wednesday, March 3, 2010

Programming Brain Teaser

My Normal Approach is Useless Here
We have started to learn ExtJS and use it in production with this very nice tutorial.
It's strict to the point and very easy to follow. You can try it yourself, 30 minutes per day are perfect :)
Ext JS rocks!


Meanwhile I've just got an interesting "task" from Ciprian and I couldn't go further with tutorials until I've done it :D.
What's your resolvation? Post the link with your solution on the comments. I will post mine too :)

Don't cheat yourself!

The problem is simple

First, you have an array. It looks like this:

The array


var arr = ['a', 'b', 'c', 'c', 'd','e', 'e', 'e', 'e', 'e', 'f', 'e', 'f', 'e',
'f', 'a', 'a', 'a', 'f', 'f', 'f'];

Be sure to use the array above in your example. You can iterate through an array easily with a batch function like forEach.

using forEach

arr.forEach(funciton(item, index, ar) {
// do stuff here
});

or using for

for(var index = 0; index<arr.length;index++ {
//do stuff here
}

In the end, we’d like to have an output that looks like the following:

The final output

a b c c d e e <span>e e e</span> f e f e f a a <span>a</span> f f <span>f</span>

You can use basic string concatenation while looping through the items to build your final output. Be sure to test and compare your output results with the actual results. And last but not least, the rule.

So the rule is this

Group together all duplicate items that occur anytime beyond twice by wrapping them with a tag, naturally “bookending” them.
Simple, right? No, really. Tease your brain for a few minutes, you can fix that bug after lunch. :p


This problem was first published here.
Enjoy your brain!

Friday, February 26, 2010

PLINQO aka Professional LINQ to Objects rules!

I was looking for a couple of days of how to upgrade to web 2.0 applications by using the last new technologies such as ExtJS, ASP.NET MVC, WCF and others.
All good regarding the new technologies, but suddenly I realize that while I'm upgrading the front-end to web 2.0, I'm still getting stuck to the old back-end!

I personally hate Linq-To-SQL because of its buggy limitations, and the ease of making things go slow. On the other hand I do love the LINQ by its design.
Same problem like three years ago in 2007 when I ended up to write my own generator because at that time CodeSmith was too limited for my needs.
Now, I hate my generator too, because it works too slow and I don't have enough time to upgrade my designs. I also hate NHibernate, because I don't want to learn dying technologies like HQL.
I also don't understand the purposes of other languages than javascript /:) (well, kidding, but that is true). Of course javascript lacks of several features from other languages i.e. LINQ :) but that's another story I'm gonna share later on.

After two days of investigating a dozen of brand new technologies, I came across over a few back-end solutions, all looking promising, but none of them more than PLINQO!

How do you feel, if you can still use the LINQ concept and don't care about all of its past issues that were on the standard Linq-to-Sql design? (some would say LinQ to StinQ design, yeah it stinks l-))
Well, you still care about them, but now you have quick and nice ways of dealing with them without all the painstaking work and the missing of the bits and pieces.

Too many talking!
Let's get to the facts.

I'm not going to describe each and every advantage of the PLINQO features, as you can read about them right here.

However, there are a few that caught my eye and I would like to get a short brief about them.
  • Entities, especially Serialization / Deserialization of entities.
  • Oh yeah, Caching :-> although I'm not yet convinced about if it has support for SqlCacheDependency support for SQL Server 2008 features
  • Futures also known as batch grouping.


  • Batch updates & deletes
  • Many to many relationships - I hate that third table too, now it's gone!
  • Rules


  • Enums
  • Linq-To-SQL Profiler

... and a lot more :)

I loved this article about LinQ-to-StinQ :) ! Well, not anymore with PLINQO!
I will get back in a few weeks, when I get a project in production with more discoveries and impressions.

The whole idea with PLINQ is that you can generate the entities in a few seconds and use them whatever you like, on the fly with the power of LINQ.



Happy generating!
 
Blog powered up by Blogger