January 2010 - Posts

Filter IQueryable by String for ASP.NET MVC

In ASP.NET web applications, mostly seen in MVC, it is really nice to have a standard way to filter a query based on a pre-defined set of combinators. It is often annoying to have to test for different Request parameters in a controller action for MVC or on a page for WebForms. In this post I will describe what I’m calling StringToIQueryable, an open source parser library I built in a few days that I’m using on a few projects. Basically you feed a string to the parser and it manipulates an IQueryable according to a set of pre-defined combinators. The syntax is URL-friendly… that was the goal. Hopefully I will show you in this post how useful this tool can be for both consumption and extension.

I figure I might as well tell you where the source is first. I decided to use GitHub for this project… so it is hosted on GitHub here. Download it and have at it!

 

MONADS MONADS MONADS!

If Steve Ballmer were a functional programmer, he’d FOR SURE be screaming MONADS MONADS MONADS! I’ve discussed monads in the past (with a little less understanding than I have now :) ). Basically a parser (like what I’ve implemented) is like the IEnumerable monad in C#. There is a whole list of combinators you can use to define exactly what a parser does to arrive at a result. Intermediate results are stored as pairs of the parsed value and the string left to parse. I have defined a few simple combinators (I will talk about Or, When, and OrWhen specifically) that are useful. I’ve also defined some of the standard LINQ query operators… so you can easily use a parser as a LINQ query. As Erik Meijer says, “everything is a query!”.

 

Parser Internals

The parser has really 2 important internals, a delegate and a storage container for the result and “rest” string. Here’s the code for each.

// generic delegate for getting result/rest
public delegate ParserResult<T> Parse<T>(string input);

// class for holding the result and rest
public sealed class ParserResult<T>
{
    public ParserResult(T parsed, string remaining)
    {
        Parsed = parsed;
        Remaining = remaining;
    }

    public T Parsed { get; private set; }
    public string Remaining { get; private set; }
}

 

You’ll notice I’m using a generic… so you can pretty much parse anything here. The Parse delegate takes an input and returns a parser result. The parser’s job is to make sure everything is parsed correctly. This is in the ParserLib project of my source code.

 

The Combinators

Combinators in functional programming are kind of an awesome thing. The main operators are bind and return. Bind is implemented with Select, SelectMany and Then. These are really the important operators, but Or, When, and OrWhen are what I find particularly useful when dealing with constraints and possibilities in my URL to IQueryable configuration.

Or is a combinator that takes 2 Parse delegates. If the first should return null (indication of failure) then the result of the Parse will be the result of the second delegate.

When is a unary parser (takes only 1 parser) and takes a string predicate. If the predicate is satisfied by the input, then the result will be delegated to the Parse instance. Otherwise the parser fails and null is returned. This is especially useful in my implementation of String to IQueryable because I associate a beginning keyword to a parser.

OrWhen is a hybrid of Or and When that I built to mainly chain my parsers together. OrWhen takes a string predicate (like When) and 2 parsers (like Or). If the result isn’t null for the first parser, then that is the result. Otherwise the string is tested against the predicate and fails if the predicate fails or returns the result of the parse if the string predicate passes.

If you download the code, you will see these defined in the ParserLib project. I’m not going to show you the implementation for the sake of brevity. But I would recommend having a look.

 

The Query Parser

Ah now to the good stuff. QuerParser is it’s own separate project in my solution. I have defined what I call “ParserExpressions”, which mostly conform to the LINQ standard query operators. The expressions are take, skip, where, sort, and page (which isn’t a standard query operator). I will discuss why some of the other operators were not implemented in a minute. But you do have the option of extending the parser by creating your own operators. Here is the interface that all the expressions use.

 

public interface IParserExpression<T>
{
    IQueryable<T> Map(IQueryable<T> queriable);
}

 

That’s it. Just one method (plus a ToString… more on that in a bit). The expression has to be able to transform an IQueryable using the Map method. How this method works is up to the expression. For example, the take expression transforms the IQueryable by calling the Take method and passing an integer (which is passed in the constructor) to generate a new IQueryable. You will see how this is useful in a minute.

It is important to note that the ParserExpression does not parse the string. They simply act as containers for state until the IQueryable is mapped upon. The parse delegate, as I said before, is the logic behind the parser.

Let’s now have a look at the parse code.

public class StringQueryParser<T>
{
    private string expressions;
    public StringQueryParser(string exprs)
    {
        expressions = exprs.ToLower();
    }

    protected Predicate<string> StartsWith(string test)
    {
        return str => str.StartsWith(test);
    }

    protected virtual Parse<IParserExpression<T>> Parsers
    {
        get
        {
            return Parser.When(StartsWith(ParserConstants.ExpressionSeparator.ToString()), IgnoreSeparatorParser)
                     .OrWhen(StartsWith(ParserConstants.PageIndicator), PageParser)
                     .OrWhen(StartsWith(ParserConstants.SkipIndicator), SkipParser)
                     .OrWhen(StartsWith(ParserConstants.TakeIndicator), TakeParser)
                     .OrWhen(StartsWith(ParserConstants.SortIndicator), SortParser)
                     .Or(WhereParser); 
        }
    }

    public IEnumerable<IParserExpression<T>> Parse()
    {
        if (string.IsNullOrEmpty(expressions))
            return new List<IParserExpression<T>>();

        return Parsers.Repeat()
                      .Invoke(expressions)
                      .Parsed
                      .Where(x => x != null);
    }

    public IQueryable<T> Map(IQueryable<T> queriable)
    {
        foreach (var parseExpr in Parse())
            queriable = parseExpr.Map(queriable);

        return queriable;
    }
    
    
    // the parsers have been removed for brevity. once
    // again, download the code for have a look at the
    // entire parser code
    ......
}

 

As you’ll see I’ve removed the parsers. Just go download the code to look at the parser code. Those are generally unimportant for this post. I can go in-depth if there is a demand for it.

I’ve implemented a Parsers property that returns a composite parser that encapsulates the logic to parse the string. If you were to override this class, you’d want to override the Parsers property to append your own parsers or just completely start anew. Each indicator (page, take, sort and skip) will be covered in the next section, but basically we filter an expression down to it’s parts and create the expression from there. You’ll have a better idea of how to form these expressions by the end. Don’t worry :) So really there’s no surprises here (other than repeat… that’s in ParserLib. Kind of intuitive… you parse until the input is empty). When all is said and done, the IQueryable is mapped through all the expressions generated by the string. Here’s how accomplish a parse in an ASP.NET MVC controller.

 

public class MyController : Controller
{
    ....
    // option #1, instantiate a StringQueryParser
    public ActionResult List(string query)
    {
        // replace with your data access
        IQueryable<User> usersQueryable = Session.Linq<User>();
        // do the parsing
        var parser = new StringQueryParser<User>(query);
        usersQueryable = parser.Map(usersQueryable);
        
        // manipulate and parse as needed
        return Json(usersQueryable.ToList());
    }

    // option #2, use the extension method
    public ActionResult List2(string query)
    {
        // replace with your data access
        var usersQueryable = Session.Linq<User>().Parse(query);        
        
        // manipulate and parse as needed
        return Json(usersQueryable.ToList());
    }
    ......
}

 

I personally prefer option 2. That’s a little neater, but both do the same thing. You will want to setup a “catch all” route in MVC that maps to this.

 

How to form expressions

At this point, I’ve been blabbering about the internals of the parser. Some of you probably don’t care… which is actually fine with me. The important part of this whole project is the real world application. Basically you will be passing a well-formed expression to your controllers that will manipulate the IQueryable accordingly. Here’s the low down on how to do this by string.

Singular Expressions

/skip-5/

skips the first 5 elements

/take-4/

returns only 4 elements

/take-all/

returns all elements

/page-5-11/

returns a paged result on page 5 (1-based) with page size 11

/page-5/

returns a paged result on page 5, default page size is 10 in my library

/sort-name/

applies an ascending sort on the name property

/sort-desc-name/

applies a descending sort on the name property

/sort-name,age,birthmonth/

applies an ascending sort on name property, then ascending sort on age property, then on birthmonth property

/sort-desc-name,age,birthmonth/

applies a descending sort on the name property, then ascending sort on age property, then on birthmonth property

/name-equals-jon/

applies a where name equals jon

/age-greaterthan-4/

applies a greater than 4 where

/name-not-null/

applies a name must not be null constraint

 

For the where expression, there are a lot of combinators that we support (equals, like, not, greater than, less than, greater than or equal, and less than or equal). We also support null as a “keyword”. So don’t expect your name properties to be null or something… that would be interpreted as null by the parser and not “null” as in the string.

 

Multiple Expressions

It is particularly useful to chain these expressions together. Here’s some examples. Feel free to combine these however you’d like.

/page-5/name-equals-jim/age-lessthan-90/age-greaterthanequal-4/

/skip-4/take-3/name-equals-kim/haschainsaw-equals-true/

/gender-equals-female/page-5-10/

The order is typically of no importance except for using skip and take together. It is a good idea to use a skip before a take. That’s just been my experience. You’ll notice in these examples (which are fictitious, by the way :)) that we support a lot of different types. Strings, int, double, float, enum, and more. In the future I’ll add an interceptor that you can parse where’s differently.

 

How properties work

When designing this solution, I had this idea that you might want to name a property differently or ignore a property. I have added two attributes (ParserIgnore and ParserPropertyName). The parser takes these into effect. If you pass the parser an ignored property then the constraint isn’t parsed. You can define a parser property name to a property and that name will be used in determining which property to apply a constraint. This avoids the issue of property name conflicts… you have to resolve those yourself using the property name attribute for the expected behavior to work when you have the same property name in different case.

Also, it’s worth mentioning that everything is parsed in lower-case. So the case in your expressions are of no importance… it’ll just be changed to lower-case upon parsing.

 

Using the generator

Sometimes you don’t want to form these by hand… concatenation isn’t exactly useful when you’re using paged data, for example. Because of this, I’ve built a generator. Basically you’d pass a few IParserExpression’s to this generator and it’ll generate the string for the query for you. The generator uses the ToString method on each IParserExpression. It is important to note that you must override ToString for the generator to work on your custom expressions. You can take a look at the code yourself, but I thought I might show you an example of how to use this.

 

....
<h2>My MVC Page</h2>
....
<a href="#<% new StringQueryGenerator<User>(new IParserExpression<User>[] 
                                            { 
                                                 new WhereExpression<User>(u => u.Name, WhereCombinator.equals, "foo"),
                                                 new PageExpression<User>(4, 10)
                                            }).Generate() %>
......

 

This will generate a string like /name-equals-foo/page-4/ . Both where and sort have a constructor overload that you can pass in a property expression… so you don’t have to use the property name as a string.

 

 

Conclusion

Wow that was a long blog post :) I hope that you’ve seen that this is useful for ASP.NET MVC in particular and in an instance where a user can define their own query. There are many examples of this sort of thing on the web where people are using this approach to filter results based on a clean-looking query. I hope you check out the source code and let me know if you have any suggestions or comments on my implementation. And with that I say DEATH TO QUERY STRING FILTERING!


kick it on DotNetKicks.com

jQuery DataTables Plugin Meets C#

Over the weekend, I was doing some work on the internal CMS we use over at eagleenvision.net and I wanted to scrap my custom table implementation for a table system that would use JSON to return data rather than have the data be statically allocated on the page. Basically I wanted to have the ability to refresh, etc for editing purposes. My little table marker was too much code for something so simple.

I’m a HUGE fan of jQuery, especially of the recent changes in jQuery 1.4. We have already made a significant investment in jQuery code for our CMS and I didn’t want to simply add another library or framework into the mix… by that I mean I didn’t want to throw in the Ext framework, which specialized in UI controls rather than general purpose JavaScript.

I stumbled upon the jQuery DataTables plugin. It has a lot of great features… one of which is the ability to have server-side processing of your data. The examples on the site are written with PHP, as are the downloadable demos. I don’t use PHP, I use ASP.NET :). So I had to write my own library to process the incoming request. DataTables has a set of pre-defined request variables that are passed to the server for processing. A successful implementation will take all these variables into account and send the correct data back per these variables. (To see a complete list, check out the server-side usage page).

 

Our friend, IQueriable

If you’re a C# developer, there’s no way you don’t already know about LINQ… (double negatives… oops…. every C# knows about LINQ, or they’re not really current with technology… that’s better :) ). This includes VB people as well. IQueriable is the fundamental type of LINQ that drives the functionality of the various sequence operators. In my implementation of DataTables processing, I wanted to leverage LINQ so that you could throw this thing an IQueriable from Linq2Sql, Linq to NHibernate, Entity Framework, LightSpeed, in-memory list, or ANYTHING that has IQueriable functionality and it would just work.

 

How to output

The DataTables plugin accepts either JSON or XML… whichever jQuery will parse. My opinion is never use XML with JavaScript. It’s slower and there’s no point to using XML over JSON… especially in .NET where there are built-in JSON serializers. Having said that, you could certainly use XML… although I haven’t tested my code for this, I think (in theory) it will work the same. He’s the output type, which should be serialized into JSON or XML, which I will cover in a minute.

public class FormatedList
{
    public FormatedList()
    {
    }
    public int sEcho { get; set; }
    public int iTotalRecords { get; set; }
    public int iTotalDisplayRecords { get; set; }
    public List<List<string>> aaData { get; set; }
    public string sColumns { get; set; }
    public void Import(string[] properties)
    {
        sColumns = string.Empty;
        for (int i = 0; i < properties.Length; i++)
        {
            sColumns += properties[i];
            if (i < properties.Length - 1)
                sColumns += ",";
        }
    }
}

Basically the only interesting thing here is the output of the columns. I made a custom Import method that just takes a list of properties and forms the column string that DataTables will parse. Other than that the code here is just basic property holding.

 

ASP.NET MVC

Readers of my blog and twitter will know I am also a HUGE fan of ASP.NET MVC. I don’t think I’ll ever return to ASP.NET WebForms. But who knows. Anyways, here’s how you will output the thing in MVC

public ActionResult List()
{
    IQueriable<User> users = Session.Linq<User>();
    if (Request["sEcho"] != null)
    {
        var parser = new DataTableParser<User>(Request, users);
        return Json(parser.Parse());
    }
    return Json(users);
}

You’ll notice that I referenced DataTableParser, which I will get to in a minute. This takes an HttpRequestBase (or an HttpRequest) and an IQueriable of whatever type. It will output a new FormattedList in the parse method, which you will return via JSON (which is serialized in the Json method for MVC).

 

ASP.NET Webservices

While I don’t claim to be an expert at ASP.NET Webservice, this I can handle :). He’s how would would do the same thing in ASP.NET Webservices.

using System.Web.Script.Serialization;
using System.Linq;
...
public class MyWebservice : System.Web.Services.WebService
{
    public string MyMethod()
    {
        // change the following line per your data configuration
        IQueriable<User> users = Session.Linq<User>();
        
        response.ContentType = "application/json";
        
        if(Request["sEcho"] != null)
        {
            var parser = new DataTableParser<User>(Request, users);
            return new JavaScriptSerializer().Serialize(parser.Parse());
        }
        
        return new JavaScriptSerializer().Serialize(users); 
    }
}

 

This is the same code… it just uses webservices and the JavaScriptSerializer (which MVC uses under the covers) to serialize the FormatedList object.

 

It should be noted that you should ALWAYS check if the request is a DataTables request (which is what that sEcho business is all about).

 

The Parser

Now is the time to show you my parser. I have taken out my code comments to keep this short on my blog… but you can download my code from here and the comments should explain what is going on.

 

public class DataTableParser<T>
{
    private const string INDIVIDUAL_SEARCH_KEY_PREFIX = "sSearch_";
    private const string INDIVIDUAL_SORT_KEY_PREFIX = "iSortCol_";
    private const string INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX = "sSortDir_";
    private const string DISPLAY_START = "iDisplayStart";
    private const string DISPLAY_LENGTH = "iDisplayLength";
    private const string ECHO = "sEcho";
    private const string ASCENDING_SORT = "asc";
    private IQueryable<T> _queriable;
    private readonly HttpRequestBase _httpRequest;
    private readonly Type _type;
    private readonly PropertyInfo[] _properties;
    public DataTableParser(HttpRequestBase httpRequest, IQueryable<T> queriable)
    {
        _queriable = queriable;
        _httpRequest = httpRequest;
        _type = typeof(T);
        _properties = _type.GetProperties();
    }
    public DataTableParser(HttpRequest httpRequest, IQueryable<T> queriable)
        : this(new HttpRequestWrapper(httpRequest), queriable)
    { }
    
    public FormatedList Parse()
    {
        var list = new FormatedList();
        list.Import(_properties.Select(x => x.Name).ToArray());
        
        list.sEcho = int.Parse(_httpRequest[ECHO]);
        
        list.iTotalRecords = _queriable.Count();
        
        ApplySort();
        
        int skip = 0, take = 10;
        int.TryParse(_httpRequest[DISPLAY_START], out skip);
        int.TryParse(_httpRequest[DISPLAY_LENGTH], out take);
        
        list.aaData = _queriable.Where(ApplyGenericSearch)
                                .Where(IndividualPropertySearch)
                                .Skip(skip)
                                .Take(take)
                                .Select(SelectProperties)
                                .ToList();
                                
        list.iTotalDisplayRecords = list.aaData.Count;
        return list;
    }
    private void ApplySort()
    {
        foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
        {
            int sortcolumn = int.Parse(_httpRequest[key]);
            if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                break;
                
            string sortdir = _httpRequest[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)];
            
            var paramExpr = Expression.Parameter(typeof(T), "val");
            var propertyExpr = Expression.Lambda<Func<T, object>>(Expression.Property(paramExpr, _properties[sortcolumn]), paramExpr);
            
            if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
                _queriable = _queriable.OrderBy(propertyExpr);
            else
                _queriable = _queriable.OrderByDescending(propertyExpr);
        }
    }
    
    private Expression<Func<T, List<string>>> SelectProperties
    {
        get
        {
            // 
            return value => _properties.Select
                                        (
                                            prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
                                        )
                                       .ToList();
        }
    }
    
    private Expression<Func<T, bool>> IndividualPropertySearch
    {
        get
        {
            var paramExpr = Expression.Parameter(typeof(T), "val");
            Expression whereExpr = Expression.Constant(true); // default is val => True
            foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
            {
                int property = -1;
                if (!int.TryParse(_httpRequest[key].Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property) 
                    || property >= _properties.Length || string.IsNullOrEmpty(_httpRequest[key]))
                    break; // ignore if the option is invalid
                string query = _httpRequest[key].ToLower();
                
                var toStringCall = Expression.Call(
                                    Expression.Call(
                                        Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
                                    typeof(string).GetMethod("ToLower", new Type[0]));
                
                whereExpr = Expression.And(whereExpr, 
                                           Expression.Call(toStringCall, 
                                                           typeof(string).GetMethod("Contains"), 
                                                           Expression.Constant(query)));
                
            }
            return Expression.Lambda<Func<T, bool>>(whereExpr, paramExpr);
        }
    }
    
    private Expression<Func<T, bool>> ApplyGenericSearch
    {
        get
        {
            string search = _httpRequest["sSearch"];
            
            if (string.IsNullOrEmpty(search) || _properties.Length == 0)
                return x => true;
                
            var searchExpression = Expression.Constant(search.ToLower());
            var paramExpression = Expression.Parameter(typeof(T), "val");
            
            var propertyQuery = (from property in _properties
                                let tostringcall = Expression.Call(
                                                    Expression.Call(
                                                        Expression.Property(paramExpression, property), "ToString", new Type[0]),
                                                        typeof(string).GetMethod("ToLower", new Type[0]))
                                select Expression.Call(tostringcall, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();
                                
            Expression compoundExpression = propertyQuery[0];
            
            for (int i = 1; i < propertyQuery.Length; i++)
                compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);
                
            return Expression.Lambda<Func<T, bool>>(compoundExpression, paramExpression);
        }
    }
}

 

Caveat

Currently there’s a bug here, that I need to research :). If you have a boolean property and apply a sort on it, you’ll get an exception because I am trying to cast it as an object with the Expression.Lambda<Func<T, object>> call. I’ll look into this and update this blog post accordingly. If you can provide any help, that would be great :)

 

Conclusion

This is just a simple example of parsing a request and mutating IQueriable acordingly. I hope this helps someone out there who would like to use C# with the DataTables plugin. Again, you can download my code from here with full comments.

Posted by zowens | 26 comment(s)
More Posts