"Where" Linq capability in my own application

Question

I need to have "Where" Linq functionality in my own code.

Let me explain: we have an application that lets users write the so-called "user code", which is code in C# that will be picked up by the app and compiled and run at runtime.

In user code, I need to be able to specify an SQL condition. A special case is the condition on a date column. We may have conditions like [DateColumn] = '1/1/2001' which are easy to implement, but also [DateColumn] = GetDate() + 1. In the latter case I would need to implement an parser to "understand" the expression. I don't want to blindly send to SQL whatever the user enters (avoid SQL injection).

The user would be most pleased to write a Linq-like query like this:

xxx.Where(field => field == new DateTime(2001, 1, 1));
xxx.Where(field => field == DateTime.Now.AddDays(1));

Is it possible to leverage the Linq to SQL framework in any way? I would need the SQL generated in the back to further construct the whole query and send it to SQL Server.

Are there any 3rd party tools that may help me?

I don't require the whole IQueryable interfaces (to compose, join querys etc.). Only the ability to convert a System.Linq.Expressions.Expression to T-SQL (even with limitations).

What is the System.Linq.* namespace that exposes this conversion functionality for SQL? Maybe I can get a few hints with Reflector.

Thank you very much.

Answer 1

I imagine you can make use of Linq's lazy evaluation to acheive this. If I understand your question correctly, I think you can do something like this;

// Before user code:
var products = from p in db.products select p;

// Insert user-generated code here, such as i.e.
userProducts = products.Where(p => price < 1000);

// After user code:
for (var product in userProducts)
{
  Whatever(product);
}

Since linq is lazy, the actual SQL query will not be issued to the database before you enumerate the 'result', in this case, "userProducts".

Answer 2

There is a Sample code in the SDK called dynamic linq.

\CS 2008 Samples\LinqSamples\DynamicQuery

You can do such things:

Northwind db = new Northwind(connString); 
db.Log = Console.Out;

var query =
    db.Customers.Where("City == @0 and Orders.Count >= @1", "London", 10).
    OrderBy("CompanyName").
    Select("New(CompanyName as Name, Phone)");

Another possibility is to implement a Custom Linq Provider. A very good example can be found at "THE WAYWARD WEBLOG" http://blogs.msdn.com/mattwar/pages/linq-links.aspx. This Blog helped me implementing my own custom Provider.

public interface IQueryable : IEnumerable {       
    Type ElementType { get; }
    Expression Expression { get; }
    IQueryProvider Provider { get; }
}

public interface IQueryProvider {
    IQueryable CreateQuery(Expression expression);
    IQueryable<TElement> CreateQuery<TElement>(Expression expression);
    object Execute(Expression expression);
    TResult Execute<TResult>(Expression expression);
}

Answer 3

With all due respect, I think you're jumping out of the frying pan and into the fire. You don't want to permit SQL injection, but you're permitting code injection. C# code, even a lambda, allows side effects.

AFAIK, C# 4.0 does allow you to dynamically compile code. But you'll have to perform compiler-quality analysis to prevent the user from doing something stupid or malicious. It's much simpler to massage the WHERE clause to prevent SQL injection.