Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / C#

re-linq|ishing the Pain: Using re-linq to Implement a Powerful LINQ Provider on the Example of NHibernate

Rate me:
Please Sign up or sign in to vote.
4.86/5 (19 votes)
18 Jan 2010MIT9 min read 91.8K   1.4K   51   24
This article shows how to use the open source re-linq library to easily implement a powerful LINQ provider on the example of LINQ to NHibernate HQL.

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):

C#
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:

C#
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:

C#
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:
C#
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:

C#
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

C#
"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.

C#
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:

C#
public string SelectPart { get; set; }

In the case of the from-statement, it also handles emission of the correct HQL-alias-syntax:

C#
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:
C#
from pn in NHQueryFactory.Queryable<PhoneNumber>(session)
  where pn.CountryCode == "11111"
  select pn;
The implementation is as straightforward as above:
C#
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:
C#
protected override Expression VisitBinaryExpression (BinaryExpression expression)
{
  _hqlExpression.Append ("(");
  VisitExpression (expression.Left);

  switch (expression.NodeType)
  {
    case ExpressionType.Equal:
      _hqlExpression.Append (" = ");
      break;

    // handle additional binary expressions

    default:
      base.VisitBinaryExpression (expression); // throws
      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:
C#
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;

// etc...

join Clause

Implementing join-operations also takes only a few lines of code:
C#
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:
C#
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); // throws
  }
}

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):
C#
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.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Austria Austria
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSample available on GitHub Pin
Michael (MK)24-Dec-15 0:17
Michael (MK)24-Dec-15 0:17 
GeneralMy vote of 5 Pin
Florian.Witteler8-Aug-12 22:27
Florian.Witteler8-Aug-12 22:27 
GeneralRe: My vote of 5 Pin
Fabian Schmied12-Aug-12 23:26
Fabian Schmied12-Aug-12 23:26 
QuestionDoes it work in medium trust sites? Pin
Menelaos Vergis2-Jul-12 2:10
Menelaos Vergis2-Jul-12 2:10 
AnswerRe: Does it work in medium trust sites? Pin
Fabian Schmied3-Jul-12 0:03
Fabian Schmied3-Jul-12 0:03 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey2-Mar-12 22:32
professionalManoj Kumar Choubey2-Mar-12 22:32 
GeneralAny chance you can update this with the latest version of Re-Linq? Pin
Member 801590418-Jun-11 3:45
Member 801590418-Jun-11 3:45 
GeneralRe: Any chance you can update this with the latest version of Re-Linq? Pin
Fabian Schmied26-Jun-11 21:34
Fabian Schmied26-Jun-11 21:34 
GeneralRe: Any chance you can update this with the latest version of Re-Linq? Pin
Florian.Witteler12-Aug-12 21:33
Florian.Witteler12-Aug-12 21:33 
GeneralRe: Any chance you can update this with the latest version of Re-Linq? Pin
Michael (MK)11-Feb-16 20:10
Michael (MK)11-Feb-16 20:10 
QuestionMIT or LGPL? Pin
JakeSays9-Mar-11 13:49
JakeSays9-Mar-11 13:49 
AnswerRe: MIT or LGPL? Pin
Michael (MK)13-Apr-11 20:09
Michael (MK)13-Apr-11 20:09 
Hi Jake!

re-linq is licensed under LGPL v2. The license of the article only refers to the sample.

Michael
GeneralMy vote of 4 Pin
Ole Thrane31-Jan-11 1:05
Ole Thrane31-Jan-11 1:05 
QuestionHow Can I use this to generate SQL queries against my custom ORM business objects ? Pin
Ruchit S.18-Jan-10 7:22
Ruchit S.18-Jan-10 7:22 
AnswerRe: How Can I use this to generate SQL queries against my custom ORM business objects ? Pin
Fabian Schmied19-Jan-10 4:59
Fabian Schmied19-Jan-10 4:59 
GeneralNice Pin
Nicholas Butler18-Jan-10 5:23
sitebuilderNicholas Butler18-Jan-10 5:23 
GeneralRe: Nice Pin
Markus Giegl19-Jan-10 8:05
Markus Giegl19-Jan-10 8:05 
QuestionWhy did you override VisitQueryModel ? Pin
emperon23-Dec-09 10:22
emperon23-Dec-09 10:22 
AnswerRe: Why did you override VisitQueryModel ? Pin
Markus Giegl4-Jan-10 0:40
Markus Giegl4-Jan-10 0:40 
GeneralNice! Pin
AlwiNus14-Sep-09 13:03
AlwiNus14-Sep-09 13:03 
GeneralWell done Pin
FabioMaulo4-Sep-09 2:46
FabioMaulo4-Sep-09 2:46 
GeneralRe: Well done Pin
Markus Giegl4-Sep-09 4:01
Markus Giegl4-Sep-09 4:01 
GeneralRe: Well done Pin
FabioMaulo4-Sep-09 4:11
FabioMaulo4-Sep-09 4:11 
GeneralRe: Well done Pin
swenig4-Sep-09 4:29
swenig4-Sep-09 4:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.