SQL Syntax C# Filtering

Take the following list:

var persons = new Person[]
{
    new Person { Name = "Adrian", Age=34 },
    new Person { Name = "Lilach", Age=32 },
    new Person { Name = "Yuval", Age=5 },
    new Person { Name = "Alon", Age=3 },
};

Of the following class:

[DebuggerDisplay("{Name} ({Age})")]
class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Using LINQ, we can easily select some items using lambda expressions, for example:

var persons = new Person[]
{
    new Person { Name = "Adrian", Age=34 },
    new Person { Name = "Lilach", Age=32 },
    new Person { Name = "Yuval", Age=5 },
    new Person { Name = "Alon", Age=3 },
};

var selected = persons.Where(p => p.Age > 5 || p.Name.Contains("va"));

And get this result:

image

The Where method accepts a hard-coded expression and if we want to provide the user the ability to define filters, we have to build the expression at runtime.

In-addition to building the expression at runtime, we have to give the user a proper way to input such an expression.

A simple approach to giving the user the ability to filter the data may be using an SQL syntax, for example:

var persons = new Person[]
{
    new Person { Name = "Adrian", Age=34 },
    new Person { Name = "Lilach", Age=32 },
    new Person { Name = "Yuval", Age=5 },
    new Person { Name = "Alon", Age=3 },
};

var selected = persons.Where("Age > 5 OR Name LIKE '*va*'");

That filter string can be entered by some user of the application, for example in a TextBox..

The question is, how can we use that expression to actually filter the data?

Remember DataTables?

Since .NET Framework v1.0, System.Data.DataTable already have an internal mechanism for selecting rows using the SQL syntax.

We can use it to query our own generic list of Persons.

Here’s how:

  1. Create a table having each property of the Person class as a column of the table.
  2. For each item in the list – add a row of its values to the table.
  3. Run the query expression on the table and get a set of resulting rows.
  4. Find the original items in the list according to the resulting rows.

And the code:

static class Extensions
{
    public static IEnumerable<T> Where<T>(
        this IEnumerable<T> collection, string expression)
    {
        return Where(collection, expression, false);
    }

    public static IEnumerable<T> Where<T>(
        this IEnumerable<T> collection, string expression, bool caseSensitive)
    {
        var table = new DataTable
        {
            CaseSensitive = caseSensitive
        };

        var props =
            typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        // create a column for each property giving its name and type
        //
        table.Columns.AddRange(
            props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());

        // for each item in the collection –
        // create an array of values based on the list of properties
        //
        var values = collection.ToDictionary(
            item => props.Select(p => p.GetValue(item, null)).ToArray(),
            item => item);

        // for each array of values – add it as a row in the table
        //
        foreach (var valueArray in values.Keys)
        {
            table.Rows.Add(valueArray);
        }

        try
        {
            // run the expression
            //
            var rows = table.Select(expression);

            // for each matching row –
            // find the item that has all the same values
            //
            var matches = rows.Select(
                row => values.First(
                    kvp => kvp.Key.SequenceEqual(row.ItemArray)).Value);

            return matches;
        }
        catch (EvaluateException)
        {
            // the expression is invalid.
            // for example, it might contain an unknown column name.
            //
            return Enumerable.Empty<T>();
        }
    }
}

Supported Functionality

The following MSDN article describes all the syntax and supported functions:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

More Examples

var selected = persons.Where("Name IN ('Adrian','Yuval')");

var selected = persons.Where("Len(Name) + Age > 8");

Share, please:
  • Digg
  • del.icio.us
  • Google Bookmarks
  • DotNetKicks
  • DZone
  • StumbleUpon
  • Facebook
  • Tumblr
  • Twitter
This entry was posted in .NET and tagged . Bookmark the permalink.

3 Responses to SQL Syntax C# Filtering

  1. Are you aware of Dynamic Linq? It does exactly that (and more), without using DataTables. You can find the code in Visual Studio’s samples.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Spam protection by WP Captcha-Free