Introduction
While a lot of free LINQ providers exist, they typically provide only basic functionality and fail at even mildly complex queries. The reason for this is that .NET does not provide a LINQ provider with the LINQ query in the syntax most people use in their C# source code (from-where-select etc.), but instead in the form of an abstract syntax tree. Unfortunately this abstract syntax tree is not well suited to transforming the query expression to typical query languages (the implementation effort can easily reach several man months for a single specific LINQ provider; in addition only very little code reuse is possible between different LINQ providers based directly on the abstract syntax tree representation).
The TDD developed open source re-motion framework supplies a commercial class LINQ provider library in the form of its re-linq component, that transforms the abstract syntax tree back to an object model that represents the LINQ query expression in a form much closer to typical query languages (In fact, re-linq can even transform hand-made abstract syntax trees into a semantically identical representation in query expression syntax). Using the re-linq provided representation, one can easily create powerful LINQ providers that emit queries in any number of query languages, including SQL, NHibernate's HQL, XQuery or Entity Framework's Entity SQL.
Moreover, re-linq provides a flexible query model that lets you modify existing queries before generating code in a target query language. This allows you to create and modify queries from application code, but also to share transformation methods between different LINQ providers based on re-linq. For more information on this advanced topic see "Transforming queries" in the "How to write a LINQ provider - the simple way"-blog-post, which also goes into more detail on how to implement a general re-linq based LINQ provider.
Why re-linq
Existing LINQ provider implementations (such as Microsoft's Linq2SQL) typically work by transforming the LINQ tree representation to another query language in many small steps. This approach has several drawbacks: For instance it requires a wealth of up-front knowledge about what tree combinations are actually possible, in addition to requiring a deep knowledge on the exact workings of how LINQ works internally. It also leads to implementations which are tightly coupled to the target query language, i.e. there is very little potential for generically reusable code parts: You have to start every LINQ provider from scratch.
The re-linq open source LINQ provider library takes a different approach: It transforms the LINQ query into an internal C#-LINQ-like model, which is easier to understand and therefore to translate. re-linq requires the implementer to just override some methods in two visitor classes, which re-linq feeds with easily consumable parameters, while the whole tree traversal is handled in the background. This makes implementing any kind of LINQ provider a straightforward task.
You can find more about re-linq at the re-linq CodePlex page.
Background
To understand this article you will need to have a basic knowledge of Microsoft LINQ technology. The article describes how to implement a LINQ provider for NHibernate, so it is beneficial if you know the Hibernate Query Language (HQL); however, due to the syntactic similarity of HQL to SQL, the principles shown in the sample can easily be translated into using re-linq to implement a SQL LINQ provider instead (even most of the code will be identical).
Knowing the Visitor pattern is not required, but might aid in understanding the workings of re-linq.
Sample Queries
Below are some sample queries which our 4 hour re-linq for NHibernate implementation can handle.
Member Access and Multiple from
-Statements (query returns all people who own their homes):
from l in NHQueryFactory.Queryable<Location>(session)
from p in NHQueryFactory.Queryable<Person>(session)
where (l.Owner == p) && (p.Location == l)
select p;
Joining and Sorting:
from p in NHQueryFactory.Queryable<Person>(session)
where p.Surname == "Oerson"
orderby p.Surname
join pn in NHQueryFactory.Queryable<PhoneNumber>(session) on p equals pn.Person
select pn;
Complex Expressions and Method Calling:
from l in NHQueryFactory.Queryable<Location>(session)
from p in NHQueryFactory.Queryable<Person>(session)
where (((((3 * l.ZipCode - 3) / 7)) == 9) && p.Surname.Contains ("M") && p.Location == l)
select l;
[Setup]
Before you get started you will need to download and unzip the sample source code. If you want to execute the tests you then need to open the "hibernate.cfg.xml" file in the NHibernate.ReLinq.Sample.UnitTests
project and adapt the DBMS connection data to your DBMS. You will also need to create the sample DB given under Initial Catalog="...", which defaults to "NHibernate_ReLinq".
Note that while re-linq is implemented following TDD principles, the sample code is only an integration test spike (It can be a good exercise for someone getting started with TDD to turn the spike into a TDD implementation).
Fast Forward
If you are an expert and would rather dig into the code right now, here is a quick rundown of how the sample works:
NHQueryFactory
creates
NHQueryable<T>
, the NHibernate specific LINQ
IQueryable<T>
.
NHQueryable<T>
creates and holds a
NHQueryExecutor
, which forms the bridge between re-linq and the concrete query provider implementation through its
ExecuteScalar<T>
,
ExecuteSingle<T>
and
ExecuteCollection<T>
methods. For the NHibernate LINQ provider it suffices for
ExecuteScalar<T>
and
ExecuteSingle<T>
to basically forward to
ExecuteCollection<T>
;
ExecuteCollection<T>
in turn uses a
HqlGeneratorQueryModelVisitor
to traverse the re-linq
QueryModel
and retrieve the resulting HQL query string.
The HqlGeneratorQueryModelVisitor
contains the specific LINQ provider code which creates the resulting HQL for the LINQ select
, from
, where
, orderby
, join
, etc commands through its VisitSelectClause
, VisitWhereClause
, etc methods. Internally, it uses an (also LINQ-provider-specific) HqlGeneratorExpressionTreeVisitor
to process different LINQ expressions, such as binary expressions (Equal
, Add
, Multiply
,...), member access (person.Location
), method calls (person.FirstName.Contains("John")
), etc through its VisitBinaryExpression
, VisitMemberExpression
,... methods. These two classes constitute the heart of any specific re-linq based LINQ provider.
HqlGeneratorQueryModelVisitor
internally uses a QueryPartsAggregator
to collect multiple from
, where
, etc statements and emit them in the correct HQL order. This is necessary, since LINQ is much more flexible than HQL (or SQL) with regards to the order of operations (QueryPartsAggregator
might be moved to re-linq or the re-motion contrib library in the future).
The Test Domain
For our tests we use a simple test domain constisting of
Location
|s,
Person
|s and
PhoneNumber
|s. Each
Person
has exactly one
Location
and can have an arbitrary number of
PhoneNumber
|s. You can find the classes in the test project under "DomainObjects", the NHibernate mapping under "Mappings" (Note: You do not need to know how an NHibernate mapping works; suffice to say that that the mapping tells NHibernate how to persist instances of our test domain in the DBMS, and how to query them using its SQL-like HQL query language).
re-linq|ing NHibernate
select ... from Statement
After having set up the DB, open the file "IntegrationTests.cs" in
NHibernate.ReLinq.Sample.UnitTests
. It contains typical NHibernate setup and teardown code, making sure that the tests do not interfere with each other. Scroll down until you come to the first
[Test]
,
SelectFrom()
. The test shows the simplest possible LINQ query:
from pn in NHQueryFactory.Queryable<PhoneNumber>(session)
select pn;
which simply returns all
PhoneNumber
|s created in the
SetupTestData()
method.
NHQueryFactory
is the re-linq NHibernate LINQ queryable factory which allows domain objects of the passed generic type to be queried through LINQ; e.g. for PhoneNumber
instances:
NHQueryFactory.Queryable<PhoneNumber>
In the case of NHibernate it has to be passed the
NHibernate.ISession
to use.
For testing purposes, CreateNHQuery(session, query.Expression).QueryString
gives the HQL query string corresponding to the LINQ query
, which equals
"select pn from NHibernate.ReLinq.Sample.UnitTests.DomainObjects.PhoneNumber as pn"
Executing the
query
directly gives all 5
PhoneNumber
|s in the test domain, as expected.
The implementation shows that re-linq does most of the work for us. All we have to do is override VisitMainFromClause
and VisitSelectClause
in HqlGeneratorQueryModelVisitor
(which derives from re-linq's QueryModelVisitorBase
), with the following trivial implementation.
public override void VisitMainFromClause (MainFromClause fromClause, QueryModel queryModel)
{
_queryParts.AddFromPart (fromClause);
base.VisitMainFromClause (fromClause, queryModel);
}
public override void VisitSelectClause (SelectClause selectClause, QueryModel queryModel)
{
_queryParts.SelectPart = GetHqlExpression (selectClause.Selector);
base.VisitSelectClause (selectClause, queryModel);
}
All the work on our parts is done by
QueryParts
, a simple helper class, which collects together different query components of the same type (e.g.
from
-statements) and emits them in the correct order at the end.
The from
-statement is just stored:
public string SelectPart { get; set; }
In the case of the from
-statement, it also handles emission of the correct HQL-alias-syntax:
public void AddFromPart (IQuerySource querySource)
{
FromParts.Add (string.Format ("{0} as {1}", GetEntityName (querySource),
querySource.ItemName));
}
Note that this is easy to do, since re-linq already supplies us with the information we need.
where Statement
The next test introduces the
where
-statement and the equality comparison operator:
from pn in NHQueryFactory.Queryable<PhoneNumber>(session)
where pn.CountryCode == "11111"
select pn;
The implementation is as straightforward as above:
public override void VisitWhereClause (WhereClause whereClause,
QueryModel queryModel, int index)
{
_queryParts.AddWherePart (GetHqlExpression (whereClause.Predicate));
base.VisitWhereClause (whereClause, queryModel, index);
}
GetHqlExpression
uses a
HqlGeneratorExpressionTreeVisitor
(which derives from re-linq's
ThrowingExpressionTreeVisitor
, aptly named since he throws when he encounters an expression for which no handler has been implemented.), which handles e.g. binary expression evaluation; in the case of the equality operator:
protected override Expression VisitBinaryExpression (BinaryExpression expression)
{
_hqlExpression.Append ("(");
VisitExpression (expression.Left);
switch (expression.NodeType)
{
case ExpressionType.Equal:
_hqlExpression.Append (" = ");
break;
default:
base.VisitBinaryExpression (expression);
break;
}
VisitExpression (expression.Right);
_hqlExpression.Append (")");
return expression;
}
(Note: In production code, using a lookup table instead of the switch-case-construct will be the better choice).
The VisitExpression
method call is executed by re-linq code and does the required traversal of data structures.
Binary Expressions
Other binary expressions can be handled as trivially as the equality-operator:
case ExpressionType.Equal:
_hqlExpression.Append (" = ");
break;
case ExpressionType.AndAlso:
case ExpressionType.And:
_hqlExpression.Append (" and ");
break;
case ExpressionType.OrElse:
case ExpressionType.Or:
_hqlExpression.Append (" or ");
break;
case ExpressionType.Add:
_hqlExpression.Append (" + ");
break;
case ExpressionType.Subtract:
_hqlExpression.Append (" - ");
break;
join Clause
Implementing
join
-operations also takes only a few lines of code:
public override void VisitJoinClause (JoinClause joinClause,
QueryModel queryModel, int index)
{
_queryParts.AddFromPart (joinClause);
_queryParts.AddWherePart (
"({0} = {1})",
GetHqlExpression (joinClause.OuterKeySelector),
GetHqlExpression (joinClause.InnerKeySelector));
base.VisitJoinClause (joinClause, queryModel, index);
}
Where again the
VisitJoinClause
call is executed by re-linq.
Method Calls
The following shows the implementation of a method call, in this case the test whether a string contains the given substring; the call to
Contains
is transformed into the SQL/HQL "
like %
<substring>
%
" syntax:
protected override Expression VisitMethodCallExpression (MethodCallExpression expression)
{
var supportedMethod = typeof (string).GetMethod ("Contains");
if (expression.Method.Equals (supportedMethod))
{
_hqlExpression.Append ("(");
VisitExpression (expression.Object);
_hqlExpression.Append (" like '%'+");
VisitExpression (expression.Arguments[0]);
_hqlExpression.Append ("+'%')");
return expression;
}
else
{
return base.VisitMethodCallExpression (expression);
}
}
Putting it all Together
The final step to finish the LINQ to NHibernate query transformation is the call to
QueryPartsAggregator
BuildHQLString
, which emits the information collected by the two visitors in the correct HQL order. The implementation, again, is straightforward (
SeparatedStringBuilder.Build
is a re-motion helper method which simply concatenates together strings from an enumerable, separating them with its first argument):
public string BuildHQLString ()
{
var stringBuilder = new StringBuilder ();
if (string.IsNullOrEmpty (SelectPart) || FromParts.Count == 0)
throw new InvalidOperationException (
"A query must have a select part and at least one from part.");
stringBuilder.AppendFormat ("select {0}", SelectPart);
stringBuilder.AppendFormat (" from {0}",
SeparatedStringBuilder.Build (", ", FromParts));
if (WhereParts.Count > 0)
stringBuilder.AppendFormat (" where {0}",
SeparatedStringBuilder.Build (" and ", WhereParts));
if (OrderByParts.Count > 0)
stringBuilder.AppendFormat (" order by {0}",
SeparatedStringBuilder.Build (", ", OrderByParts));
return stringBuilder.ToString ();
}
The Rest
The examples above are about as complex as it gets, you should now have a good idea of how to go about writing a LINQ provider based on re-linq. For further exploration please have a look at the demo project (download link at top of page).
Troubleshooting
Q: If I execute the tests, all tests fail. What gives ?
A: Please check if you created the "NHibernate_ReLinq" DB in your DBMS and adapted the "hibernate.cfg.xml" file.
[Teardown]
In this article we have shown how the
re-motion re-linq library can be used to easily implement feature rich LINQ providers with little effort. re-linq makes it easy to do so, since it encapsulates the complex LINQ parsing algorithms and provides a easily digestible model of the query, ready to be transformed by the two provider specific
HqlGeneratorQueryModelVisitor
and
HqlGeneratorExpressionTreeVisitor
visitor classes.
// May all your tests be green.
Cleanup();
// Ma;-)rkus
History
- 2009.09.03 - Initial posting.
- 2009.09.04 - Loosened license to MIT.
- 2010.01.18 - Added copyright notice, re-motion user group link, re-linq page link.
- 2010.01.19 - Fixed code sample generic arguments.
Copyright
Copyright 2009 by rubicon informationstechnologie gmbh
Contact
For questions about this article or re-linq, please contact the
re-motion mailing list.