Create custom LINQ providers fluently

Just released LinqExtender 2.0. Over previous release , it contains generally bugs fixes. Overall, I have focused on striping out complexity as much as possible to keep you focused on your business logic. You can see the full list of features in the documentation that comes with it.

Now, while creating the LinqToTwitter example, I have shown that creating custom providers with LinqExtender requires two steps, first you have to define a query object by implementing IQueryObject and then you have made a Query<T> successor and override few methods. In LinqExtender, object/ entity equivalent is Bucket. Starting from 2.0, there is a Fluent interface implementation of it that works as an internal DSL and gives you a single entry point for all query and update , inserts and delete, thus making things more declarative and one way.

In this post, I will show you how to generate SQL statement from LINQ query with nested where clause, using the fluent interface implementation. To start, let's consider the following query

var query = from book in bookContext
where ((book.Author == "Mehfuz" && book.ISBN == "2") || book.Id == books[2].Id) || 
(book.Author == "Paolo Pialorsi" && (book.ISBN == "100-11-777" || book.ISBN == "1"))
select book;

It's taken from the Entrypoint.cs test class that comes with LinqExtender project along with OpenLinqToSql sample ORM. Now, to do things in a very basic way. Let's first create a string builder and append the initial select.

StringBuilder builder = new StringBuilder("SELECT * FROM [" + Bucket.Instance.Entity.Name + "]");

To be precise, Bucket.Instance is the entry point for all query and object details. Moving to where statement , LinqExtender uses simplified expression tree that is exposed by Bucket.Instance.ExpressionTree. All we need to setup how the output will look like and the rest will be covered by the toolkit.

builder.Append("WHERE");
builder.Append("\r\n");

Bucket
.Instance
.ExpressionTree
.DescribeContainerAs(builder)
.Root((containter, operatorType) => containter.Append(" " + operatorType + " "))
.Begin(container => container.Append("("))
.EachLeaf((container, item) =>
 {
     string value = GetValue(item.Value);
     container.Append(item.Name + RelationalOperators[item.RelationType] + value);
 })
.End(container => container.Append(")"))
.Execute();

Here, let assume that GetValue(..) will return a formatted SQL string and ReleationalOperators is a dictionary that has mappings for item.RealtionType ( GreaterThan -> ">" , LessThan => "<", etc). Basically, as the implementation shows, we are defining the way output will be stored in the container (In this case, StringBuilder). All these code should be placed in Query<T>.Process(IModify<T> items) method and on execute it will just return the following SQL block

Select * from book 
 WHERE
(((Author='Mehfuz' AND ISBN='2') OR Bk_Id='1734') 
OR (Author='Paolo Pialorsi' AND (ISBN='100-11-777' OR ISBN='1'))) 

Extender basically knows how to get though this nested where clause. From basic computer science, it builds linked objects based on a Syntax Tree, with each parenthesis adding diversion to the tree like shown below

So far, this is a very rudimentary example. With 2.0 it is also possible to build your own reusable format provider for building literals that saves repetitive code blocks for same kind of task (updating a database). This is actually just a schema builder that defines how the output will look like using the same Bucket.Instance calls. Out of the box, TSqlFormatProvider is provided.Depending under which Query<T> method it is used; it will generate insert, update, delete or select statement where all you have to call Bucket.Instance.Translate(...).

string sql = Bucket.Instance.Translate(new TSqlFormatProvider());

This enables you to build not only your own LINQ to Anything format providers but also share it with the community to reuse what is already out there. More information on how to get started can be found at the project documentation.

In a word, Bucket.Instance is all you need to go through and build your own LINQ provider. Optionally, you can override the following methods (under Query<T>) to give your provider various OTS (Object tracking service) support .

1. AddItem () - called during SubmitChanges() call for new object.
2. RemoveItem () - called during SubmitChanges() call for Delete.
3. UpdateItem () – called during SubmitChanges() call for update.

I have updated Creating LinqToTwitter using LinqExtender post with the latest release. You can get a copy of the latest 2.0 release from www.codeplex.com/linqextender,  and of course as usual all your feedbacks are really helping to shape the toolkit.

Hope that helps,

Update on 28th Feb 2009, with new patch for Bucket.Instance.ExpressionTree