DbQuery中ObjectQuery.Parameters的等效值是什么

问题描述

我有一个代码，使用一个linq语句使它成为 DbQuery 而不是 ObjectQuery 使用该查询通过foreach循环添加参数。但 DbQuery 没有参数支持。我知道我可以手动添加它们。但我有36个不同的参数在我的SQL语句。所以我需要找到一种方法来添加参数。

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());

这是参数使用的代码。

我需要一种方法来实现 DbQuery

I need a way to do that with DbQuery

推荐答案

我创建一个扩展方法来获取参数

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);
        }
    }
}

使用

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

这篇关于DbQuery中ObjectQuery.Parameters的等效值是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！