Search code examples
c#.netsqllinqexpression-trees

Convert LINQ Expression to SQL Text without DB Context

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.

Solution

  • This is something I briefly looked into quite a while ago. You may want to have a look at http://iqtoolkit.codeplex.com/ and/or http://expressiontree.codeplex.com/ for ideas. As mentioned by others, Linq query provider building is far from trivial if you do not limit your scope to the minimum set of features you really need.

    If your goals relate to "specification" or "query object" notion of repositories according to DDD, this may not be the best direction to take. Instead of CRUD like technology related abstractions, it may be more productive to focus on ways in which the behaviour of the domain can be expressed, with a minimum of direct dependencies on technology related abstractions. As Eric Evans recently discussed, he regrets the focus on the technical building blocks, such as repositories, in his initial descriptions of DDD.