Turn very simple Expression<Func<T, bool>> into SQL where clause

Question

I have a number of different data sources that I need to query and have been able to do confine all my queries to very simple expressions with no more than 2 conditions. An example of typical complexity of my lamba Expressions is:

b => b.user == "joe" && b.domain == "bloggs.com"

On my non-SQL data sources I'm ok as I can convert them to object lists and use a LINQ query such as:

public override T Get(List<T> assets, Expression<Func<T, bool>> whereClause)
{
    return assets.Where(a => whereClause.Compile()(a)).FirstOrDefault();
}

My problem is when I need to query a relational database - I'm only really concerned with Postgresql and MySQL - I've been struggling a bit. I've got nHibernate to Linq "working" but have had some issues where it stalls and stops accessing the database or can't close connections, typical sort of things to expect from something in beta so I'm not complaining.

Because my queries are so simple and I am happy to build the SQL myself I'd like to know if there is a relatively painless way to turn my Expression's into SQL where clauses?

I've had a quick search around and found some samples and of course there are already some Linq-to-SQL engines so I know it can be done. The question is whether it's something I can whip up in a day or less of effort? That's what I'd estimate it would take me to find a compatible Linq-to-SQL library and load test it assuming it works out ok. I do have big performance considerations so raw SQL is preferrable for me instead of running through an ORM.

Answer 1

Writing a LINQ provider can be very difficult. I would suggest looking into these links:

Linq Provider for MySql, Postgres, Oracle (This is open-source)
Writing custom LINQ provider

Answer 2

Based on your comment that you only want to build an SQL WHERE clause from your lambda, let's have a look at the WHERE clause. I will use it in context of a full SELECt statement, but it is the same for update and delete.

An example

SELECT * FROM b
WHERE b.user == 'joe' AND b.domain == 'bloggs.com'

As you can see, it will be very easy to build the where clause:

I take it that b is the name of the table. You then only have to:

• cut off everything before and including the "=>" operator
• change all operators from lambda to SQL syntax: '==' becomes '='.
• change '&&' to AND and '||' to OR
• change apostrophes accordingly (" -> ') in string operations.

You can use parenthesis to group your logic. Use LIKE to find substrings in strings. Have a look at this site for syntax of the where clause.

Use string replace to do the magic:

str = str.Replace("==", "=");