How to convert an expression tree to a partial SQL query?

c# .net linq entity-framework lambda

42,946

Solution 1

The short answer seems to be that you cannot use a part of EF or LINQ to SQL as a shortcut to translation. You need at least a subclass of ObjectContext to get at the internal protected QueryProvider property, and that means all the overhead of creating the context, including all the metadata and so on.

Assuming you are ok with that, to get a partial SQL query, for example, just the WHERE clause you're basically going to need the query provider and call IQueryProvider.CreateQuery() just as LINQ does in its implementation of Queryable.Where. To get a more complete query you can use ObjectQuery.ToTraceString().

As to where this happens, LINQ provider basics states generally that

IQueryProvider returns a reference to IQueryable with the constructed expression-tree passed by the LINQ framework, which is used for further calls. In general terms, each query block is converted to a bunch of method calls. For each method call, there are some expressions involved. While creating our provider - in the method IQueryProvider.CreateQuery - we run through the expressions and fill up a filter object, which is used in the IQueryProvider.Execute method to run a query against the data store

and that

the query can be executed in two ways, either by implementing the GetEnumerator method (defined in the IEnumerable interface) in the Query class, (which inherits from IQueryable); or it can be executed by the LINQ runtime directly

Checking EF under the debugger it's the former.

If you don't want to completely re-invent the wheel and neither EF nor LINQ to SQL are options, perhaps this series of articles would help:

Here are some sources for creating a query provider that probably involve much more heavy lifting on your part to implement what you want:

Solution 2

Yes it is possible, you can parse a LINQ expression tree using the visitor pattern. You would need to construct a query translator by subclassing ExpressionVisitor like below. By hooking into the correct points you can use the translator to construct your SQL string from your LINQ expression. Note that the code below only deals with basic where/orderby/skip/take clauses, but you can fill it out with more as needed. Hopefully it serves as a good first step.

public class MyQueryTranslator : ExpressionVisitor
{
    private StringBuilder sb;
    private string _orderBy = string.Empty;
    private int? _skip = null;
    private int? _take = null;
    private string _whereClause = string.Empty;

    public int? Skip
    {
        get
        {
            return _skip;
        }
    }

    public int? Take
    {
        get
        {
            return _take;
        }
    }

    public string OrderBy
    {
        get
        {
            return _orderBy;
        }
    }

    public string WhereClause
    {
        get
        {
            return _whereClause;
        }
    }

    public MyQueryTranslator()
    {
    }

    public string Translate(Expression expression)
    {
        this.sb = new StringBuilder();
        this.Visit(expression);
        _whereClause = this.sb.ToString();
        return _whereClause;
    }

    private static Expression StripQuotes(Expression e)
    {
        while (e.NodeType == ExpressionType.Quote)
        {
            e = ((UnaryExpression)e).Operand;
        }
        return e;
    }

    protected override Expression VisitMethodCall(MethodCallExpression m)
    {
        if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
        {
            this.Visit(m.Arguments[0]);
            LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
            this.Visit(lambda.Body);
            return m;
        }
        else if (m.Method.Name == "Take")
        {
            if (this.ParseTakeExpression(m))
            {
                Expression nextExpression = m.Arguments[0];
                return this.Visit(nextExpression);
            }
        }
        else if (m.Method.Name == "Skip")
        {
            if (this.ParseSkipExpression(m))
            {
                Expression nextExpression = m.Arguments[0];
                return this.Visit(nextExpression);
            }
        }
        else if (m.Method.Name == "OrderBy")
        {
            if (this.ParseOrderByExpression(m, "ASC"))
            {
                Expression nextExpression = m.Arguments[0];
                return this.Visit(nextExpression);
            }
        }
        else if (m.Method.Name == "OrderByDescending")
        {
            if (this.ParseOrderByExpression(m, "DESC"))
            {
                Expression nextExpression = m.Arguments[0];
                return this.Visit(nextExpression);
            }
        }

        throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name));
    }

    protected override Expression VisitUnary(UnaryExpression u)
    {
        switch (u.NodeType)
        {
            case ExpressionType.Not:
                sb.Append(" NOT ");
                this.Visit(u.Operand);
                break;
            case ExpressionType.Convert:
                this.Visit(u.Operand);
                break;
            default:
                throw new NotSupportedException(string.Format("The unary operator '{0}' is not supported", u.NodeType));
        }
        return u;
    }


    /// <summary>
    /// 
    /// </summary>
    /// <param name="b"></param>
    /// <returns></returns>
    protected override Expression VisitBinary(BinaryExpression b)
    {
        sb.Append("(");
        this.Visit(b.Left);

        switch (b.NodeType)
        {
            case ExpressionType.And:
                sb.Append(" AND ");
                break;

            case ExpressionType.AndAlso:
                sb.Append(" AND ");
                break;

            case ExpressionType.Or:
                sb.Append(" OR ");
                break;

            case ExpressionType.OrElse:
                sb.Append(" OR ");
                break;

            case ExpressionType.Equal:
                if (IsNullConstant(b.Right))
                {
                    sb.Append(" IS ");
                }
                else
                {
                    sb.Append(" = ");
                }
                break;

            case ExpressionType.NotEqual:
                if (IsNullConstant(b.Right))
                {
                    sb.Append(" IS NOT ");
                }
                else
                {
                    sb.Append(" <> ");
                }
                break;

            case ExpressionType.LessThan:
                sb.Append(" < ");
                break;

            case ExpressionType.LessThanOrEqual:
                sb.Append(" <= ");
                break;

            case ExpressionType.GreaterThan:
                sb.Append(" > ");
                break;

            case ExpressionType.GreaterThanOrEqual:
                sb.Append(" >= ");
                break;

            default:
                throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", b.NodeType));

        }

        this.Visit(b.Right);
        sb.Append(")");
        return b;
    }

    protected override Expression VisitConstant(ConstantExpression c)
    {
        IQueryable q = c.Value as IQueryable;

        if (q == null && c.Value == null)
        {
            sb.Append("NULL");
        }
        else if (q == null)
        {
            switch (Type.GetTypeCode(c.Value.GetType()))
            {
                case TypeCode.Boolean:
                    sb.Append(((bool)c.Value) ? 1 : 0);
                    break;

                case TypeCode.String:
                    sb.Append("'");
                    sb.Append(c.Value);
                    sb.Append("'");
                    break;

                case TypeCode.DateTime:
                    sb.Append("'");
                    sb.Append(c.Value);
                    sb.Append("'");
                    break;

                case TypeCode.Object:
                    throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));

                default:
                    sb.Append(c.Value);
                    break;
            }
        }

        return c;
    }

    protected override Expression VisitMember(MemberExpression m)
    {
        if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
        {
            sb.Append(m.Member.Name);
            return m;
        }

        throw new NotSupportedException(string.Format("The member '{0}' is not supported", m.Member.Name));
    }

    protected bool IsNullConstant(Expression exp)
    {
        return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
    }

    private bool ParseOrderByExpression(MethodCallExpression expression, string order)
    {
        UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
        LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;

        lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);

        MemberExpression body = lambdaExpression.Body as MemberExpression;
        if (body != null)
        {
            if (string.IsNullOrEmpty(_orderBy))
            {
                _orderBy = string.Format("{0} {1}", body.Member.Name, order);
            }
            else
            {
                _orderBy = string.Format("{0}, {1} {2}", _orderBy, body.Member.Name, order);
            }

            return true;
        }

        return false;
    }

    private bool ParseTakeExpression(MethodCallExpression expression)
    {
        ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];

        int size;
        if (int.TryParse(sizeExpression.Value.ToString(), out size))
        {
            _take = size;
            return true;
        }

        return false;
    }

    private bool ParseSkipExpression(MethodCallExpression expression)
    {
        ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];

        int size;
        if (int.TryParse(sizeExpression.Value.ToString(), out size))
        {
            _skip = size;
            return true;
        }

        return false;
    }
}

Then visit the expression by calling:

var translator = new MyQueryTranslator();
string whereClause = translator.Translate(expression);

Solution 3

It isn't complete, but here are some thoughts for you to groove on if you come by this later:

    private string CreateWhereClause(Expression<Func<T, bool>> predicate)
    {
        StringBuilder p = new StringBuilder(predicate.Body.ToString());
        var pName = predicate.Parameters.First();
        p.Replace(pName.Name + ".", "");
        p.Replace("==", "=");
        p.Replace("AndAlso", "and");
        p.Replace("OrElse", "or");
        p.Replace("\"", "\'");
        return p.ToString();
    }

    private string AddWhereToSelectCommand(Expression<Func<T, bool>> predicate, int maxCount = 0)
    {           
        string command = string.Format("{0} where {1}", CreateSelectCommand(maxCount), CreateWhereClause(predicate));
        return command;
    }

    private string CreateSelectCommand(int maxCount = 0)
    {
        string selectMax = maxCount > 0 ? "TOP " + maxCount.ToString() + " * " : "*";
        string command = string.Format("Select {0} from {1}", selectMax, _tableName);
        return command;
    }

Solution 4

In Linq2SQL you can use:

var cmd = DataContext.GetCommand(expression);
var sqlQuery = cmd.CommandText;

Solution 5

After searching for hours for an implementation of an Expression tree to SQL converter, I did not found anything usefull or free or somehow working with .NET Core. Then I found this. Thank you Ryan Wright. I took his code and modified it a bit to fit my needs. Now I am giving it back to the community.

Current version can do the following:

Bulk update

            int rowCount = context
                .Users
                .Where(x => x.Status == UserStatus.Banned)
                .Update(x => new
                {
                    DisplayName = "Bad Guy"
                });

This will produce the following sql

DECLARE @p0 NVarChar
DECLARE @p1 Int
SET @p0 = 'Bad Guy'
SET @p1 = 3
UPDATE [Users]
SET [DisplayName] = @p0
WHERE ( [Status] = @p1 )

Bulk delete

            int rowCount = context
                .Users
                .Where(x => x.UniqueName.EndsWith("012"))
                .Delete();

The produced sql

DECLARE @p0 NVarChar
SET @p0 = '%012'
DELETE
FROM [Users]
WHERE [UniqueName] LIKE @p0

Outputing SQL Statements

            string sql = context
                .Users
                .Where(x => x.Status == UserStatus.LockedOut)
                .OrderBy(x => x.UniqueName)
                .ThenByDescending(x => x.LastLogin)
                .Select(x => new
                {
                    x.UniqueName,
                    x.Email
                })
                .ToSqlString();

This produces the sql

DECLARE @p0 Int
SET @p0 = 4
SELECT [UniqueName], [Email]
FROM [Users]
WHERE ( [Status] = @p0 )
ORDER BY [LastLogin] DESC, [UniqueName] ASC

Another example

            string sql = context
                .Users
                .Where(x => x.Status == UserStatus.LockedOut)
                .OrderBy(x => x.UniqueName)
                .ThenByDescending(x => x.LastLogin)
                .Select(x => new
                {
                    x.UniqueName,
                    x.Email,
                    x.LastLogin
                })
                .Take(4)
                .Skip(3)
                .Distinct()
                .ToSqlString();

The sql

DECLARE @p0 Int
SET @p0 = 4
SELECT DISTINCT [UniqueName], [Email], [LastLogin]
FROM [Users]
WHERE ( [Status] = @p0 )
ORDER BY [LastLogin] DESC, [UniqueName] ASC OFFSET 3 ROWS FETCH NEXT 4 ROWS ONLY

Another example with a local variable

            string name ="venom";

            string sql = context
                .Users
                .Where(x => x.LastLogin == DateTime.UtcNow && x.UniqueName.Contains(name))
                .Select(x => x.Email)
                .ToSqlString();

The produced sql

DECLARE @p0 DateTime
DECLARE @p1 NVarChar
SET @p0 = '20.06.2020 19:23:46'
SET @p1 = '%venom%'
SELECT [Email]
FROM [Users]
WHERE ( ( [LastLogin] = @p0 ) AND [UniqueName] LIKE @p1 )

The SimpleExpressionToSQL class itself can be used directly

var simpleExpressionToSQL = new SimpleExpressionToSQL(queryable);
simpleExpressionToSQL.ExecuteNonQuery(IsolationLevel.Snapshot);

The code

The evaluator used here come from here

SimpleExpressionToSQL

    public class SimpleExpressionToSQL : ExpressionVisitor
    {
        /*
         * Original By Ryan Wright: https://stackoverflow.com/questions/7731905/how-to-convert-an-expression-tree-to-a-partial-sql-query
         */

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<string> _groupBy = new List<string>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<string> _orderBy = new List<string>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<SqlParameter> _parameters = new List<SqlParameter>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<string> _select = new List<string>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<string> _update = new List<string>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private readonly List<string> _where = new List<string>();

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private int? _skip;

        [DebuggerBrowsable(DebuggerBrowsableState.Never)]
        private int? _take;

        public SimpleExpressionToSQL(IQueryable queryable)
        {
            if (queryable is null)
            {
                throw new ArgumentNullException(nameof(queryable));
            }

            Expression expression = queryable.Expression;
            Visit(expression);
            Type entityType = (GetEntityType(expression) as IQueryable).ElementType;
            TableName = queryable.GetTableName(entityType);
            DbContext = queryable.GetDbContext();
        }

        public string CommandText => BuildSqlStatement().Join(Environment.NewLine);

        public DbContext DbContext { get; private set; }

        public string From => $"FROM [{TableName}]";

        public string GroupBy => _groupBy.Count == 0 ? null : "GROUP BY " + _groupBy.Join(", ");
        public bool IsDelete { get; private set; } = false;
        public bool IsDistinct { get; private set; }
        public string OrderBy => BuildOrderByStatement().Join(" ");
        public SqlParameter[] Parameters => _parameters.ToArray();
        public string Select => BuildSelectStatement().Join(" ");
        public int? Skip => _skip;
        public string TableName { get; private set; }
        public int? Take => _take;
        public string Update => "SET " + _update.Join(", ");

        public string Where => _where.Count == 0 ? null : "WHERE " + _where.Join(" ");

        public static implicit operator string(SimpleExpressionToSQL simpleExpression) => simpleExpression.ToString();

        public int ExecuteNonQuery(IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
        {
            DbConnection connection = DbContext.Database.GetDbConnection();
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = CommandText;
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(Parameters);

#if DEBUG
                Debug.WriteLine(ToString());
#endif

                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                using (DbTransaction transaction = connection.BeginTransaction(isolationLevel))
                {
                    command.Transaction = transaction;
                    int result = command.ExecuteNonQuery();
                    transaction.Commit();

                    return result;
                }
            }
        }

        public async Task<int> ExecuteNonQueryAsync(IsolationLevel isolationLevel = IsolationLevel.RepeatableRead)
        {
            DbConnection connection = DbContext.Database.GetDbConnection();
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = CommandText;
                command.CommandType = CommandType.Text;
                command.Parameters.AddRange(Parameters);

#if DEBUG
                Debug.WriteLine(ToString());
#endif

                if (command.Connection.State != ConnectionState.Open)
                    await command.Connection.OpenAsync();

                using (DbTransaction transaction = connection.BeginTransaction(isolationLevel))
                {
                    command.Transaction = transaction;
                    int result = await command.ExecuteNonQueryAsync();
                    transaction.Commit();

                    return result;
                }
            }
        }

        public override string ToString() =>
            BuildDeclaration()
                .Union(BuildSqlStatement())
                .Join(Environment.NewLine);

        protected override Expression VisitBinary(BinaryExpression binaryExpression)
        {
            _where.Add("(");
            Visit(binaryExpression.Left);

            switch (binaryExpression.NodeType)
            {
                case ExpressionType.And:
                    _where.Add("AND");
                    break;

                case ExpressionType.AndAlso:
                    _where.Add("AND");
                    break;

                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    _where.Add("OR");
                    break;

                case ExpressionType.Equal:
                    if (IsNullConstant(binaryExpression.Right))
                    {
                        _where.Add("IS");
                    }
                    else
                    {
                        _where.Add("=");
                    }
                    break;

                case ExpressionType.NotEqual:
                    if (IsNullConstant(binaryExpression.Right))
                    {
                        _where.Add("IS NOT");
                    }
                    else
                    {
                        _where.Add("<>");
                    }
                    break;

                case ExpressionType.LessThan:
                    _where.Add("<");
                    break;

                case ExpressionType.LessThanOrEqual:
                    _where.Add("<=");
                    break;

                case ExpressionType.GreaterThan:
                    _where.Add(">");
                    break;

                case ExpressionType.GreaterThanOrEqual:
                    _where.Add(">=");
                    break;

                default:
                    throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", binaryExpression.NodeType));
            }

            Visit(binaryExpression.Right);
            _where.Add(")");
            return binaryExpression;
        }

        protected override Expression VisitConstant(ConstantExpression constantExpression)
        {
            switch (constantExpression.Value)
            {
                case null when constantExpression.Value == null:
                    _where.Add("NULL");
                    break;

                default:

                    if (constantExpression.Type.CanConvertToSqlDbType())
                    {
                        _where.Add(CreateParameter(constantExpression.Value).ParameterName);
                    }

                    break;
            }

            return constantExpression;
        }

        protected override Expression VisitMember(MemberExpression memberExpression)
        {
            Expression VisitMemberLocal(Expression expression)
            {
                switch (expression.NodeType)
                {
                    case ExpressionType.Parameter:
                        _where.Add($"[{memberExpression.Member.Name}]");
                        return memberExpression;

                    case ExpressionType.Constant:
                        _where.Add(CreateParameter(GetValue(memberExpression)).ParameterName);

                        return memberExpression;

                    case ExpressionType.MemberAccess:
                        _where.Add(CreateParameter(GetValue(memberExpression)).ParameterName);

                        return memberExpression;
                }

                throw new NotSupportedException(string.Format("The member '{0}' is not supported", memberExpression.Member.Name));
            }

            if (memberExpression.Expression == null)
            {
                return VisitMemberLocal(memberExpression);
            }

            return VisitMemberLocal(memberExpression.Expression);
        }

        protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
        {
            switch (methodCallExpression.Method.Name)
            {
                case nameof(Queryable.Where) when methodCallExpression.Method.DeclaringType == typeof(Queryable):

                    Visit(methodCallExpression.Arguments[0]);
                    var lambda = (LambdaExpression)StripQuotes(methodCallExpression.Arguments[1]);
                    Visit(lambda.Body);

                    return methodCallExpression;

                case nameof(Queryable.Select):
                    return ParseExpression(methodCallExpression, _select);

                case nameof(Queryable.GroupBy):
                    return ParseExpression(methodCallExpression, _groupBy);

                case nameof(Queryable.Take):
                    return ParseExpression(methodCallExpression, ref _take);

                case nameof(Queryable.Skip):
                    return ParseExpression(methodCallExpression, ref _skip);

                case nameof(Queryable.OrderBy):
                case nameof(Queryable.ThenBy):
                    return ParseExpression(methodCallExpression, _orderBy, "ASC");

                case nameof(Queryable.OrderByDescending):
                case nameof(Queryable.ThenByDescending):
                    return ParseExpression(methodCallExpression, _orderBy, "DESC");

                case nameof(Queryable.Distinct):
                    IsDistinct = true;
                    return Visit(methodCallExpression.Arguments[0]);

                case nameof(string.StartsWith):
                    _where.AddRange(ParseExpression(methodCallExpression, methodCallExpression.Object));
                    _where.Add("LIKE");
                    _where.Add(CreateParameter(GetValue(methodCallExpression.Arguments[0]).ToString() + "%").ParameterName);
                    return methodCallExpression.Arguments[0];

                case nameof(string.EndsWith):
                    _where.AddRange(ParseExpression(methodCallExpression, methodCallExpression.Object));
                    _where.Add("LIKE");
                    _where.Add(CreateParameter("%" + GetValue(methodCallExpression.Arguments[0]).ToString()).ParameterName);
                    return methodCallExpression.Arguments[0];

                case nameof(string.Contains):
                    _where.AddRange(ParseExpression(methodCallExpression, methodCallExpression.Object));
                    _where.Add("LIKE");
                    _where.Add(CreateParameter("%" + GetValue(methodCallExpression.Arguments[0]).ToString() + "%").ParameterName);
                    return methodCallExpression.Arguments[0];

                case nameof(Extensions.ToSqlString):
                    return Visit(methodCallExpression.Arguments[0]);

                case nameof(Extensions.Delete):
                case nameof(Extensions.DeleteAsync):
                    IsDelete = true;
                    return Visit(methodCallExpression.Arguments[0]);

                case nameof(Extensions.Update):
                    return ParseExpression(methodCallExpression, _update);

                default:
                    if (methodCallExpression.Object != null)
                    {
                        _where.Add(CreateParameter(GetValue(methodCallExpression)).ParameterName);
                        return methodCallExpression;
                    }
                    break;
            }

            throw new NotSupportedException($"The method '{methodCallExpression.Method.Name}' is not supported");
        }

        protected override Expression VisitUnary(UnaryExpression unaryExpression)
        {
            switch (unaryExpression.NodeType)
            {
                case ExpressionType.Not:
                    _where.Add("NOT");
                    Visit(unaryExpression.Operand);
                    break;

                case ExpressionType.Convert:
                    Visit(unaryExpression.Operand);
                    break;

                default:
                    throw new NotSupportedException($"The unary operator '{unaryExpression.NodeType}' is not supported");
            }
            return unaryExpression;
        }

        private static Expression StripQuotes(Expression expression)
        {
            while (expression.NodeType == ExpressionType.Quote)
            {
                expression = ((UnaryExpression)expression).Operand;
            }
            return expression;
        }

        [SuppressMessage("Style", "IDE0011:Add braces", Justification = "Easier to read")]
        private IEnumerable<string> BuildDeclaration()
        {
            if (Parameters.Length == 0)                        /**/    yield break;
            foreach (SqlParameter parameter in Parameters)     /**/    yield return $"DECLARE {parameter.ParameterName} {parameter.SqlDbType}";

            foreach (SqlParameter parameter in Parameters)     /**/
                if (parameter.SqlDbType.RequiresQuotes())      /**/    yield return $"SET {parameter.ParameterName} = '{parameter.SqlValue?.ToString().Replace("'", "''") ?? "NULL"}'";
                else                                           /**/    yield return $"SET {parameter.ParameterName} = {parameter.SqlValue}";
        }

        [SuppressMessage("Style", "IDE0011:Add braces", Justification = "Easier to read")]
        private IEnumerable<string> BuildOrderByStatement()
        {
            if (Skip.HasValue && _orderBy.Count == 0)                       /**/   yield return "ORDER BY (SELECT NULL)";
            else if (_orderBy.Count == 0)                                   /**/   yield break;
            else if (_groupBy.Count > 0 && _orderBy[0].StartsWith("[Key]")) /**/   yield return "ORDER BY " + _groupBy.Join(", ");
            else                                                            /**/   yield return "ORDER BY " + _orderBy.Join(", ");

            if (Skip.HasValue && Take.HasValue)                             /**/   yield return $"OFFSET {Skip} ROWS FETCH NEXT {Take} ROWS ONLY";
            else if (Skip.HasValue && !Take.HasValue)                       /**/   yield return $"OFFSET {Skip} ROWS";
        }

        [SuppressMessage("Style", "IDE0011:Add braces", Justification = "Easier to read")]
        private IEnumerable<string> BuildSelectStatement()
        {
            yield return "SELECT";

            if (IsDistinct)                                 /**/    yield return "DISTINCT";

            if (Take.HasValue && !Skip.HasValue)            /**/    yield return $"TOP ({Take.Value})";

            if (_select.Count == 0 && _groupBy.Count > 0)   /**/    yield return _groupBy.Select(x => $"MAX({x})").Join(", ");
            else if (_select.Count == 0)                    /**/    yield return "*";
            else                                            /**/    yield return _select.Join(", ");
        }

        [SuppressMessage("Style", "IDE0011:Add braces", Justification = "Easier to read")]
        private IEnumerable<string> BuildSqlStatement()
        {
            if (IsDelete)                   /**/   yield return "DELETE";
            else if (_update.Count > 0)     /**/   yield return $"UPDATE [{TableName}]";
            else                            /**/   yield return Select;

            if (_update.Count == 0)         /**/   yield return From;
            else if (_update.Count > 0)     /**/   yield return Update;

            if (Where != null)              /**/   yield return Where;
            if (GroupBy != null)            /**/   yield return GroupBy;
            if (OrderBy != null)            /**/   yield return OrderBy;
        }

        private SqlParameter CreateParameter(object value)
        {
            string parameterName = $"@p{_parameters.Count}";

            var parameter = new SqlParameter()
            {
                ParameterName = parameterName,
                Value = value
            };

            _parameters.Add(parameter);

            return parameter;
        }

        private object GetEntityType(Expression expression)
        {
            while (true)
            {
                switch (expression)
                {
                    case ConstantExpression constantExpression:
                        return constantExpression.Value;

                    case MethodCallExpression methodCallExpression:
                        expression = methodCallExpression.Arguments[0];
                        continue;

                    default:
                        return null;
                }
            }
        }

        private IEnumerable<string> GetNewExpressionString(NewExpression newExpression, string appendString = null)
        {
            for (int i = 0; i < newExpression.Members.Count; i++)
            {
                if (newExpression.Arguments[i].NodeType == ExpressionType.MemberAccess)
                {
                    yield return
                        appendString == null ?
                        $"[{newExpression.Members[i].Name}]" :
                        $"[{newExpression.Members[i].Name}] {appendString}";
                }
                else
                {
                    yield return
                        appendString == null ?
                        $"[{newExpression.Members[i].Name}] = {CreateParameter(GetValue(newExpression.Arguments[i])).ParameterName}" :
                        $"[{newExpression.Members[i].Name}] = {CreateParameter(GetValue(newExpression.Arguments[i])).ParameterName}";
                }
            }
        }

        private object GetValue(Expression expression)
        {
            object GetMemberValue(MemberInfo memberInfo, object container = null)
            {
                switch (memberInfo)
                {
                    case FieldInfo fieldInfo:
                        return fieldInfo.GetValue(container);

                    case PropertyInfo propertyInfo:
                        return propertyInfo.GetValue(container);

                    default: return null;
                }
            }

            switch (expression)
            {
                case ConstantExpression constantExpression:
                    return constantExpression.Value;

                case MemberExpression memberExpression when memberExpression.Expression is ConstantExpression constantExpression:
                    return GetMemberValue(memberExpression.Member, constantExpression.Value);

                case MemberExpression memberExpression when memberExpression.Expression is null: // static
                    return GetMemberValue(memberExpression.Member);

                case MethodCallExpression methodCallExpression:
                    return Expression.Lambda(methodCallExpression).Compile().DynamicInvoke();

                case null:
                    return null;
            }

            throw new NotSupportedException();
        }

        private bool IsNullConstant(Expression expression) => expression.NodeType == ExpressionType.Constant && ((ConstantExpression)expression).Value == null;

        private IEnumerable<string> ParseExpression(Expression parent, Expression body, string appendString = null)
        {
            switch (body)
            {
                case MemberExpression memberExpression:
                    return appendString == null ?
                        new string[] { $"[{memberExpression.Member.Name}]" } :
                        new string[] { $"[{memberExpression.Member.Name}] {appendString}" };

                case NewExpression newExpression:
                    return GetNewExpressionString(newExpression, appendString);

                case ParameterExpression parameterExpression when parent is LambdaExpression lambdaExpression && lambdaExpression.ReturnType == parameterExpression.Type:
                    return new string[0];

                case ConstantExpression constantExpression:
                    return constantExpression
                        .Type
                        .GetProperties(BindingFlags.Public | BindingFlags.Instance)
                        .Select(x => $"[{x.Name}] = {CreateParameter(x.GetValue(constantExpression.Value)).ParameterName}");
            }

            throw new NotSupportedException();
        }

        private Expression ParseExpression(MethodCallExpression expression, List<string> commandList, string appendString = null)
        {
            var unary = (UnaryExpression)expression.Arguments[1];
            var lambdaExpression = (LambdaExpression)unary.Operand;

            lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);

            commandList.AddRange(ParseExpression(lambdaExpression, lambdaExpression.Body, appendString));

            return Visit(expression.Arguments[0]);
        }

        private Expression ParseExpression(MethodCallExpression expression, ref int? size)
        {
            var sizeExpression = (ConstantExpression)expression.Arguments[1];

            if (int.TryParse(sizeExpression.Value.ToString(), out int value))
            {
                size = value;
                return Visit(expression.Arguments[0]);
            }

            throw new NotSupportedException();
        }
    }

I will post the extension in the comments Edit: too long for the comment... I'll add another answer.

Use it with caution on production

Feel free to make a Nuget package out of it :)

View more solutions

42,946

Arseni Mourzenko

Developer, architect, project manager, tester, and active DevOps supporter, I'm studying, observing and advising companies which have an important risk to fail their IT-related projects. I specialize in quality and productivity. After six years of freelancing, I worked for several companies, including Tata Consultancy Services. Today, I'm a happy member of Finaxys. I mostly work with Linux, Python, and Node.js, as well as the Microsoft stack. Outside information technology, I'm interested by photography. I'm mostly active on SE.SE, and also maintain my blog. If you want to contact me, my email is [email protected]. Feel free to drop me a note about any development-related discussions. If you live in Paris or want to visit Paris, you're very welcome to contact me too.

Updated on June 21, 2020

Comments

Arseni Mourzenko almost 4 years
When EF or LINQ to SQL runs a query, it:
1. Builds an expression tree from the code,
2. Converts the expression tree into an SQL query,
3. Executes the query, gets the raw results from the database and converts them to the result to be used by the application.
Looking at the stack trace, I can't figure out where the second part happens.

In general, is it possible to use an existent part of EF or (preferably) LINQ to SQL to convert an Expression object to a partial SQL query (using Transact-SQL syntax), or I have to reinvent the wheel?

Update: a comment asks to provide an example of what I'm trying to do.

Actually, the answer by Ryan Wright below illustrates perfectly what I want to achieve as a result, except the fact that my question is specifically about how can I do it by using existent mechanisms of .NET Framework actually used by EF and LINQ to SQL, instead of having to reinvent the wheel and write thousands of lines of not-so-tested code myself to do the similar thing.

Here is also an example. Again, note that there is no ORM-generated code.
```
private class Product
{
    [DatabaseMapping("ProductId")]
    public int Id { get; set; }

    [DatabaseMapping("Price")]
    public int PriceInCents { get; set; }
}

private string Convert(Expression expression)
{
    // Some magic calls to .NET Framework code happen here.
    // [...]
}

private void TestConvert()
{
    Expression<Func<Product, int, int, bool>> inPriceRange =
        (Product product, int from, int to) =>
            product.PriceInCents >= from && product.PriceInCents <= to;

    string actualQueryPart = this.Convert(inPriceRange);

    Assert.AreEqual("[Price] between @from and @to", actualQueryPart);
}
```
Where does the name Price come from in the expected query?

The name can be obtained through reflection by querying the custom DatabaseMapping attribute of Price property of Product class.

Where do names @from and @to come from in the expected query?

Those names are the actual names of the parameters of the expression.

Where does between … and come from in the expected query?

This is a possible result of a binary expression. Maybe EF or LINQ to SQL would, instead of between … and statement, stick with [Price] >= @from and [Price] <= @to instead. It's ok too, it doesn't really matter since the result is logically the same (I'm not mentioning performance).

Why there is no where in the expected query?

Because nothing indicates in the Expression that there must be a where keyword. Maybe the actual expression is just one of the expressions which would be combined later with binary operators to build a larger query to prepend with a where.
- Orion Adrian over 12 years
  
  Can you throw in an example of what you're trying to convert to what?
- Mehdi Dehghani over 4 years
  
  I opened an issue about it, join the discussion https://github.com/aspnet/AspNetCore/issues/13465
Arseni Mourzenko over 12 years

Your sample is inexact. The argument of GetCommand is not an Expression, but an IQueryable, and building an IQueryable requires to have an IQueryProvider. So the problem remain the same.
Magnus over 12 years

So you want to create a SQL query from an expression without a QueryProvider?
Arseni Mourzenko over 12 years

More precisely, I want to create a partial SQL query from an expression without having to add tables from database to EF/Linq2SQL. If I understood well, query providers used by Linq2SQL depend on those tables. Am I wrong?
Orion Adrian over 12 years

While a good start this appears to be missing any string manipulation or the ability to compare strings. E.g. Contains, StartsWith. Again, good start.
Kit over 12 years

For EF at least, doesn't that require he establish an ObjectContext or DbContext first (which means setting up just about everything) and not just a part of EF.
mdisibio over 10 years

I was looking for a way to extract only the 'Skip' and 'Take' within the Query() override of a custom System.ServiceModel.DomainServices.Server.DomainService class (RIA Service), and this was exactly what I was looking for.
programad over 10 years

Where is this "Evaluator" class located?
Peter about 9 years

Evaluator is needed to resolve references to local variables in your expressions. I've used implementation from here. Also it is worth while partially evaluate expression earlier (i.e. in the Translate method, and remove the call to Evaluator from ParseOrderByExpression like this): public string Translate(Expression expression) { expression = Evaluator.PartialEval(expression);
Felype almost 8 years

I feel your answer deserved more attention here. But you might find problems with method calls like, with the expression (f)=> f.SomeList.Where((g)=> g.Epicness > 30) Another thing to notice is that (f)=> f.Name != Environtment.MachineName will output something like "f.Name != 'Environment.MachineName'", this could be counter productive.
Alen.Toma over 6 years

THX man this is just what i needed. I have added contain and i will also add Any 😁
Patrick about 6 years

I can not believe that there exists No finished solution to convert an Expression to a (string) SQL Statement? Searched GitHub and the NuGet gallery + Google but without any result... If someone knows a maintained solution please notify me!
Ernesto Rodriguez over 5 years

I'm getting an error when try to translate DateTime.Now. For intance: t => t.Name == "NAME" && t.OpeningDate == DateTime.Now. Is there any workaround?
insomniac about 5 years

Anyone who is going to use it to generate a where clause to append it to SQL , please note that the single quotes are not escaped in the resulting clause. For that change sb.Append(c.Value); to sb.Append(c.Value.ToString.Replace('"","''")); in the VisitConstantmethod
George Taskos almost 5 years

I agree, a solution like that to just get an expression tree and generate raw SQL translation would be an amazing fit to the specification pattern. I wish we find something or maybe get a small group to start something.
Stacked over 3 years

For those asking about the "Evaluator", the provided link by @Peter is broken, here is where you can find it github.com/mattwar/iqtoolkit/issues/18 check part III
jacob spitzer almost 3 years

the Evaluator class can be found here: stackoverflow.com/questions/30308124/…. Found in this answer credit to Legacy Code
Secretary Of Education over 2 years

Hey man, do you have any full working example of this? I am having troubles to combine them all together. What is context and why it have Users? How could I use this on a simple class with status enabled bool?
Legacy Code about 2 years

@SecretaryOfEducation The context is just a DBContext instance.
JaedenRuiner almost 2 years

Does this provide the appropriate split of on data layer fields an non-data layer fields? eg: have a class with 10 properties, 5 are on the data layer, 5 aren't. a "lambda predicate" uses 3 data layer fields, and 2 non-data layer fields. can i get the 3 SqlParameters and the "WHERE" clause, as well as a newly created predicate of the remaining 2 fields that aren't on the data layer?