What is the Equivalent of ObjectQuery.Parameters in DbQuery

Question

I have code that gets a query using a linq statement making it a DbQuery and not ObjectQuery and I was adding parameters through a foreach loop using that query. But DbQuery has no parameter support. I know I could add them manually. But I have 36 different parameters in my SQL statement. So I need to find a way to add the parameters.

foreach (var parameter in query.Parameters)
{
    parameters.Add(new System.Data.SqlClient.SqlParameter { ParameterName = parameter.Name, Value = parameter.Value });
}

entities.Database.ExecuteSqlCommand(sql, parameters.ToArray());

That is the code im using for the parameters.

I need a way to do that with DbQuery

Answer 1

You can compose a DbQuery by appending expressions to it. A pattern often used to do that is:

IQueryable<MyType> query = dbContext.Set<MyType>();
if (param1 != null)
    query = query.Where(x => x.Property1 == param1);
if (param2 != null)
    query = query.Where(x => x.Property2 == param2);

I don't know if this would work in your situation. If not you can always resort to ObjectQuery. A DbContext is a wrapper around an ObjectContext and you can get the latter by ((IObjectContextAdapter)dbContext).ObjectContext. That's a starting point to create ObjectQueries.

Answer 2

I create an extension method to get the parameters

public static class ExpressionExtension
{
    public static object[] GetParameters(this Expression expr)
    {
        var fetcher = new ParameterFetcher();
        fetcher.Visit(expr);
        return fetcher.Parameters.ToArray();
    }

    class ParameterFetcher : System.Linq.Expressions.ExpressionVisitor
    {
        public readonly List<object> Parameters = new List<object>();
        protected Stack<string> CallingStringMethod = new Stack<string>();
        protected int Visited = 0;

        protected override Expression VisitMember(MemberExpression node)
        {
            if (Visited > 0)
            {
                Visited--;
            }
            else
            {
                var member = node;
                while (member != null && member.Expression is MemberExpression)
                {
                    member = (MemberExpression)member.Expression;
                    Visited++;
                }

                if (member != null && member.Expression.NodeType == ExpressionType.Constant)
                {
                    var baseType = Nullable.GetUnderlyingType(node.Type) ?? node.Type;

                    if (baseType == typeof(string) || baseType == typeof(Guid) || baseType.IsPrimitive == true)
                    {
                        var objectMember = Expression.Convert(node, typeof(object));
                        var getterLambda = Expression.Lambda<Func<object>>(objectMember);
                        var value = getterLambda.Invoke();

                        if (value != null && CallingStringMethod.Count > 0)
                        {
                            switch (CallingStringMethod.Peek())
                            {
                                case "StartsWith":
                                    value = Convert.ToString(value).Replace("[", "[[]").Replace("_", "[_]").Replace("%", "[%]") + "%";
                                    break;
                                case "Contains":
                                    value = "%" + Convert.ToString(value).Replace("[", "[[]").Replace("_", "[_]").Replace("%", "[%]") + "%";
                                    break;
                                case "EndsWith":
                                    value = "%" + Convert.ToString(value).Replace("[", "[[]").Replace("_", "[_]").Replace("%", "[%]");
                                    break;
                            }
                        }

                        Parameters.Add(value);
                    }
                }
            }

            return base.VisitMember(node);
        }

        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node != null && node.Method.DeclaringType == typeof(string))
            {
                switch (node.Method.Name)
                {
                    case "StartsWith":
                    case "Contains":
                    case "EndsWith":
                        CallingStringMethod.Push(node.Method.Name);
                        var val = base.VisitMethodCall(node);
                        CallingStringMethod.Pop();
                        return val;
                }
            }
            return base.VisitMethodCall(node);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            return base.VisitParameter(node);
        }
    }
}

usage

....(IQueryable<TEntity> query)
var parameters = query.Expression.GetParameters();