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.

in the general case, though, a query may need more than that. Navigating a child-property could add a join, for example... — Marc Gravell, Nov 22 '11 at 21:25
Agreed. I was trying to get a case that works for the WHERE clause, not a general purpose "write any expression and go crazy" -- I'm perfectly happy throwing an exception if the final generated SQL turns out invalid. — im_nullable, Nov 22 '11 at 21:29
You could always parse the `WHERE` clause from the generated query. — Sheridan, Nov 23 '11 at 00:21

score 2 · Accepted Answer · answered Nov 23 '11 at 12:09

2

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.

answered Nov 23 '11 at 12:09

user489998

4,473
2
29
35

score 1 · Answer 2 · answered Jul 16 '19 at 09:30

This is a code excerpt that I use to build my own predicate to use in the Where function. The compiler can't cope with ienumerables of complex objects, so you have to do it yourself.

Essentially, the code gets passed an ienumerable of (string code, string exchange) tuples, and then builds an expression to retrieve all Security objects that have Security.Code == tuple.Code AND (Security.MasterExchangeForStocksId == tuple.exchange OR SecurityExchangeId == tuple.exchange).

CreateTrEntitiesAsync() simply returns a Entity Framework context, which has a DbSet Security property.

public async Task<Security[]> GetSecurities(IEnumerable<(string code, string exchange)> tickers)
{
    using (var ctx = await CreateTrEntitiesAsync())
    {
        var securityExpr = Expression.Parameter(typeof(Security), "security");
        Expression expr = null;
        Expression exprToadd;

        foreach (var item in tickers)
        {
            exprToadd = Expression.And(
                Expression.Equal(Expression.Property(securityExpr, nameof(Security.Code)), Expression.Constant(item.code)),
                Expression.Or(
                    Expression.Equal(Expression.Property(Expression.Property(securityExpr, nameof(Security.Exchange)), nameof(Exchange.MasterExchangeForStocksId)), Expression.Constant(item.exchange)),
                    Expression.Equal(Expression.Property(securityExpr, nameof(Security.ExchangeId)), Expression.Constant(item.exchange))
                )
            );

            if (expr == null)
                expr = exprToadd;
            else
                expr = Expression.Or(expr, exprToadd);
        }

        var criteria = Expression.Lambda<Func<Security, bool>>(expr, new ParameterExpression[] { securityExpr });
        var items = ctx.Securities.Where(criteria);
        return await items.ToArrayAsync();
    }
}

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

2 Answers2

Linked