Generate a SQL clause using a Linq-to-Sql Expression

Question

I would like to create a repository model that could take an Expression and use Linq-To-Sql to generate the required SQL statement.

For example, I have a function such as this:

// Possible criteria
Expression<Func<Purchase,bool>> criteria1 = p => p.Price > 1000;

// Function that should take that criteria and convert to SQL statement
static IEnumerable<Customer> GetCustomers (Expression<Func<Purchase,bool>> criteria)
{
   // ...
}

Inside the function, I would like to convert criteria to a SQL statement using Linq-To-Sql.

I am aware that you can use DataContext.Log to see the executed queries and DataContext.GetCommand(query).CommandText to see the full query before it is executed. However, I would like just a part of the entire expression generated.

What I am hoping to accomplish is to make my repository abstract the underlying technology (Linq-to-Sql, Dapper, etc). That way I could pass the Expression to the repository, have it generate the right statement and use the right technology to execute it.

Answer 1

You could do something like this:

string sql = DataContext.GetTable<Customer>().Where(criteria).ToString();

ToString() gives you the SQL expression. You could then use regex to pull out the WHERE clause.