Convert LINQ Expression to SQL Text without DB Context

Question

Either LINQ to SQL or LINQ to Entities already have the ability to convert LINQ into a SQL text string. But I want my application to make the conversion without using the db context - which in turn means an active database connection - that both those providers require.

I'd like to convert a LINQ expression into an equivalent SQL string(s) for WHERE and ORDER BY clauses, without a DB context dependency, to make the following repository interface work:

public interface IStore<T> where T : class 
{
     void Add(T item);
     void Remove(T item);
     void Update(T item);
     T FindByID(Guid id);

     //sure could use a LINQ to SQL converter!
     IEnumerable<T> Find(Expression<Func<T, bool>> predicate);
     IEnumerable<T> FindAll();
}

QUESTION

It is primarily the expression tree traversal and transform I am interested in. Does anyone know of an existing library (nuget?) that I can incorporate to be used in such a custom context?

As it is I've already built my own working "LINQ transformed to SQL text" tool, similar to this expression tree to SQL example which works in my above repository. It allows me to write code like this:

IRepository<Person> repo = new PersonRepository();
var maxWeight = 170;
var results = repo.Find(x => (x.Age > 40 || x.Age < 20) && x.Weight < maxWeight);

But my code and that sample are primitive (and that sample itself relies on a LINQ to SQL db context). For example, neither handle generation of "LIKE" statements.

I don't expect or need a generator-tool that handles every conceivable LINQ query. For example, I'm not worried about handling and generating joins or includes. In fact, with another ~20 hours my own custom code may cover all the cases that I care about (mostly "WHERE" and "ORDER BY" statements).

But at the same time I feel that I should not have to write my own custom code to do this. If I'm stuck writing my own, then I'd still be interested if someone could point me to specific classes I can reflect and imitate (NHibernate, EF, etc.). I'm asking about specific classes to peek at, if you know them, because I don't want to spend hours sifting through the code of a massive tool just to find the part I need.

Not that it matters, but if anyone wants to know why I'm not simply using LINQ to SQL or LINQ to Entities...for my specific application I simply prefer to use a tool such as Dapper.

USE CASES Whether I finish building the tool myself, or find a 3rd party library, here are reasons why a "LINQ to SQL text string" would be useful:

• The predicate I type into the IRepository.Find method has intellisense and basic compile-time checking.
• My proposed IStore interface can be implemented for DB access or web service access. To clarify, if I can convert the LINQ "WHERE/ORDER BY" predicate to a SQL "WHERE/ORDER BY" clause then...
  • The SQL string could be used by Dapper directly.
  • The SQL string, unlike a LINQ expression, can be sent to a WCF service to be used for direct DB access (which itself might not be using Dapper).
  • The SQL string could be deserialized, with custom code, back into a LINQ statement by the WCF service. Eric Lippert comments on this.
• The UI can use IQueryable mechanics to dynamically generate a predicate to give to the repository

In short, such a tool helps fulfill the "specification" or "query object" notion of repositories according to DDD, and does so without taking a dependency on EF or LINQ to SQL.

Answer 1

Doing this properly is really extremely complicated, especially if right now, you don't seem to know much about expression trees (which is what IQueryable uses to represent queries).

But if you really want to get started (or just get an idea of how much work it would be), have a look at Matt Warren's 17-part series Building an IQueryable provider.

Answer 2

I can confirm as this is a fairly big amount of work that’s suited only for the most experienced .NET developers. Perfect knowledge of C#, experience with multiple languages, including T-SQL is a must. One must be very well versed in both C# (or VB.NET) and T-SQL as they’ll have to write translator using the former into the latter. Additionally, this is in the realm of meta-programming, which is considered a fairly advanced branch of computer science. There is a lot of abstract thinking involved. Layers of abstract concepts stacked on each other.

If all of this isn’t a barrier, then this exercise can actually be quite enjoyable and rewarding, at least the first month or so. One common problem in these providers I noticed is that inflexibility and questionable design choices at the start led to difficulties later on and hacky fixes, etc. Planning as much as possible in advance, clearly understanding the whole process, different stages, components properly identifying layers and concerns would make it much easier to develop this. The biggest mistake I saw in one provider was – failing to break down the output query into its parts – select, from, where and order by. Each part should be represented by its own object throughout and then put together at the end. I explain this approach in my end-to-end tutorial on how to write a provider in the series linked below. There’s a sample project included, with a simpliefied/tutorial variant and the full version made from scratch for a project. Finding the time to write about it was a challenge in itself.

• How to write a LINQ to SQL provider in C#:
  • Introduction
  • Expression Visitor
  • Where Clause Visitor
  • Compiling Expression Trees