CODEWITHSUNDEEP
c#.netsqllinqexpression-trees
Brent Arias
Brent Arias

Reputation: 30165

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:

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.

Upvotes: 17

Views: 6896

Answers (3)

constantine g
constantine g

Reputation: 169

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.

Upvotes: 2

Alex
Alex

Reputation: 13224

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.

Upvotes: 1

svick
svick

Reputation: 244777

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.

Upvotes: 4

Related Questions