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.
