Mehfuz's WebLog

Live crazy, think different!

Sponsors

News

Passionate about cutting edge technologies and facinated by the modern web and phone revolution.Currently working at Telerik Corporation, the leading .net component vendor.
Follow me


Articles


Projects

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

 image

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

kick it on DotNetKicks.com
Posted: Feb 25 2009, 09:48 PM by mehfuzh | with 6 comment(s) |
Filed under: , ,

Comments

Damien Guard said:

Hope you're going to do proper encoding when including values and object names in the TSQL - would hate to see this open to SQL injection...

[)amien

# February 28, 2009 4:45 PM

mehfuzh said:

Hi Damien,

I see your point. It will  be really hard to do any sql injection, as the retsult is built on object results and for string "'" is always replaced with  "''" that makes it even diffcult. If someone tries to do this '; drop table users --, it will always produce an invalid sql due to the filteration.

Regards,

Mehfuz.

# March 1, 2009 12:29 AM

Geo said:

true, but what about cases where a user enters 'OR 1=1'. No filter provided there

# March 13, 2009 1:20 PM

mehfuzh said:

Hi Geo,

Nice question, but i guess you are slightly messing it up with classic style sql injection that we have read.  

When you call Bucket.Instance.Translate , it will generate query on top the LINQ expression if you try this

var query  = from book in bookContext

                   where book.id = 10  && book.ISBN = 'OR 1= 1'

                   select book;

This will eventaully generate

Select * from book where [id] = 10 AND ISBN = 'OR 1=1'

So, there will be no result.

Hope that helps,

Mehfuz

# March 13, 2009 3:00 PM