Lambda Expressions and Stored Procedures

Question

Hi Everyone.

I'm trying to mimic the LINQ Where extension method for my ADO.NET DAL methods.

Bascially, my aim is to have a single method that I can call. Such as:

Product p = Dal.GetProduct(x => x.ProductId == 32);
Product p2 = Dal.GetProduct(x => x.ProductName.Contains("Soap"));

I then want to dissect those Predicates and send the filter options to parameters in an ADO.NET Stored Procedure call.

Any comments greatly appreciated.

Answer 1

As @Daniel points out, this is far from simple. The solution outline is to let GetProduct take an argument of type Expression<Func<Product, bool>>. You then have to traverse the parse-tree of this expression, generating the correct SQL for functions known and also decide how to handle unknown functions. There are basically two options for that:

1. Throw an error (as linq-to-sql does).
2. Skip it in the translation and then apply it on the returned result. The performance impact of this can of course be huge if a lot of data is retreived just to be filtered out.

It would be a fun exercise to do it - but I can hardly see a way to justify it in the real world, when there are already linq2sql, linq2entities and linq2NHibernate that does the job.

Answer 2

In addition to Anders's answer, I just want to mention that you can analyze the expression tree by using an expression visitor. To do that, you can inherit the ExpressionVisitor class (it's new in .NET 4, but you can find a 3.5 implementation in LinqKit) and override the methods you want to analyze each node.

You might also be interested in those links :

• Building a LINQ Provider
• Walkthrough: Creating an IQueryable LINQ Provider