Dynamic LINQ – back to strings!!

 

Background:

During some recent refactoring – I was converting some old NHibernate code that was using the Criteria API to instead use Linq-to-NHibernate – I ran into a major roadblock. In my previous code – I was receiving parameters from my client regarding pagination, sorting, and filtering. Converting the pagination wasn’t too bad, then I got to the filtering and sorting…

My client (javascript) has passed me the string for the column it wants sorted. Linq wants a strongly typed function pointing to the member to sort on. I really don’t want to go write a translation layer that converts every string they could send me to a valid Linq Expression… so then I found Dynamic Linq.

Dynamic Linq:

You can find a blog about it by Scott Guthrie here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

You can download the sample which contains the code here: http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx

Down to the Code:

I set up a proof of concept – available here: https://github.com/jhoguet/DynamicLinq---Nhibernate-Proof-of-Concept

Filtering:

So how can we filter on a column name available in string format…
        [Test]
        public void ProofWhereEquals()
        {
            var person = _dao.GetPeople().Where("FirstName=@0", "Jon").Single();

            Assert.AreEqual(expected:"Jon", actual:person.FirstName);
        }

and note this gets translated all the way down to NHibernate generating the following SQLite code

select person0_.Id as Id0_, person0_.FirstName as FirstName0_, person0_.LastName as LastName0_ from "Person" person0_ where person0_.FirstName=@p0;@p0 = 'Jon' [Type: String (0)]

not bad – how about something more realistic for a filter… LIKE

        [Test]
        public void ProofWhereContains()
        {
            var person = _dao.GetPeople().Where("FirstName.Contains(@0)", "o").Single();

            Assert.AreEqual(expected: "Jon", actual: person.FirstName);
        }

which generated…

select person0_.Id as Id0_, person0_.FirstName as FirstName0_, person0_.LastName as LastName0_ from "Person" person0_ where person0_.FirstName like ('%'||@p0||'%');@p0 = 'o' [Type: String (0)]

alright I am impressed – but surely it will break if we do a NOT

        [Test]
        public void ProofWhereNotLike()
        {
            var person = _dao.GetPeople().Where("!FirstName.Contains(@0)", "o").Single();

            Assert.AreEqual(expected: "Manrique", actual: person.FirstName);
        }

which generated…

select person0_.Id as Id0_, person0_.FirstName as FirstName0_, person0_.LastName as LastName0_ from "Person" person0_ where  not (person0_.FirstName like ('%'||@p0||'%'));@p0 = 'o' [Type: String (0)]

alright I am impressed!

But can it handle the order by problem…

        [Test]
        public void CanOrderBy()
        {
            var person = _dao.GetPeople().OrderBy("FirstName descending").First();

            Assert.AreEqual(expected: "Manrique", actual: person.FirstName);
        }

which generated…

select person0_.Id as Id0_, person0_.FirstName as FirstName0_, person0_.LastName as LastName0_ from "Person" person0_ order by person0_.FirstName desc limit 1

sweet!

At this point I was satisfied and moved on – I know I read some blogs that spoke of limited support within DynamicLinq – but it certainly covered what I needed (I vaguely remember one example LIKE ‘StartWith%’)

I did find one surprising side effect (I might argue bug).

 [Test]
        public void WhenArgIsNullBadThingsHappen()
        {
            try
            {
                var person = _dao.GetPeople().Where("!FirstName.Contains(@0)", null).Single();
            }
            catch
            {
                //you need to check for null in your implementation
            }
        }

 

When you pass in null as the parameter you get some misleading exception

System.Linq.Dynamic.ParseException : No property or field '0' exists in type 'Person'

Took me a little while to figure out tha tit was looking for a property of ‘0’ because I passed in NULL, but my fix was to add a code branch looking for nulls and avoiding the additional LINQ altogether if the argument would be NULL.