Currently, I’m developing my own ORM in C# (working title: DynamicModel). Why? Well, it’s not that I think I can do better than the existing implementations, such as NHibernate, or the excellent (but commercial) Lightspeed. But it’s fun and convenient to work with an ORM which you can customize for specific scenario’s relatively easy, because you know the code inside out.
Currently, querying is mainly based on ActiveRecord like ‘Find’ methods. Find() excepts a condition, and returns an ICollection of entity objects.
For example, to find all albums by the band “Oceansize“:
Notice: I use the Chinook database to test my ORM.
As you can see, DynamicModel Find() does support Linq expressions, but only as a where clause in a query. The technique behind this – an expression tree visitor – is inspired by Manfred’s excellent Blog post ‘LINQ, Expression trees and ORMapper‘.
After implementing where expressions in the ‘Find()’ method, I wanted to extend the query possibilities of my ORM by adding full Linq support. If you start searching for tutorials on implementing your own Linq provider, you inevitably end-up studying the ‘LINQ: Building an IQueryable provider series‘ by Matt Warren. This series provides a fairly complete overview of the steps needed to build your own Linq to SQL provider. (A LINQ provider translates LINQ queries into specific API calls against a data source, in my case a SQL database).
By reading Matt’s blog post series, I realised how complicated a custom Linq provider can become very quickly. It is nice to have a bit of Linq support in my ORM, but I don’t want to spend night after night just to understand what the example code does. This naturally raised the question: why not just use LINQ to objects (IEnumerable<T>). Since Find returns an ICollection<T>, which extends IEnumerable<T>.
Obviously, one of the reasons why IQueryable implementations exists, is because executing queries in the native database language (SQL) often results in performance benefits. But is the performance gain really worth the effort? Furthermore, a (near) complete implementation of IQueryable would cause my code base to grow substantially, introducing a lot of potential bugs which are hard to solve. Is that worth it, or are there alternatives which are acceptable?
I think there is an acceptable alternative. My conclusion is that a basic IQueryable implementation is all I need for now. For more complex (read: unsupported) scenario’s, querying over IEnumerable will do just fine.
So, I was happy to find this week that my conclusion was not as unorthodox as I feared: in his blog post ‘Mixing LINQ Providers and LINQ to Objects‘ Bill Wagner describes exactly this method.
Conclusion: thanks to Matt, I was able to implement a Linq provider that supports basic queries (including joins). My provider does not support more complex scenario’s, such as aggregates, but complex queries can still be realised by using Linq to objects.
Result: I can now write code like this:
And if I want to do more complex things, like grouping, I’ll write
.Where(item => item.GenreId == 3)
.GroupBy(s => s.Title);
Writing queries against my ORM is now almost as easy as with .NET ORM’s which do have comprehensive Linq support, such as Lightspeed and Telerik’s OpenAccess :).
More reading about implementing LINQ: