客户端查询构建器(jquery)到Entity Framework查询

5
我想请问如何构建一个查询生成器引擎,类似于TFS用于查询的方式,允许用户组合多个条件,并在后台使用EF基于这些条件来查询数据。
类似这样:用户创建查询 -> 向后台发送json -> 后台处理查询作为EF代码优先 + 存储库模式。
有一个jQuery查询生成器插件,但我该如何将其转换为EF存储库模式?
jQuery查询生成器:jquery query builder
2个回答

3
今天我想使用 jQuery Query Builder 和 EF,我编写了以下代码将筛选器解析为表达式。希望对其他人有用。
规则数据模型:
public class Rule
{
    public string Condition { get; set; }
    public List<Rule> Rules { get; set; }
    public string Id { get; set; }
    public string Field { get; set; }
    public string Type { get; set; }
    public string Input { get; set; }
    public string Operator { get; set; }
    public List<string> Value { get; set; }
}

使用规则解析器的控制器类:

        public ActionResult GetRole(List<Rule> rules,string condition)
    {
        var param = Expression.Parameter(typeof(NgnPacket), "x");
        var rawExp = CreateCondition(rules, condition, param);
        var logic = new NgnMonitoringLogic();
        var exp = Expression.Lambda<Func<NgnPacket, bool>>(rawExp, param);
        var g = logic.GetData(exp);
        return Content(g == null ? "0" : g.Count().ToString());
    }

    private Expression CreateCondition(List<Rule> rules, string conditionOperation, ParameterExpression param)
    {
        Expression temp = null;
        foreach (var rule in rules)
        {
            if (rule.Rules != null && rule.Rules.Any())
            {
                var innerExpression = CreateCondition(rule.Rules, rule.Condition, param);
                if (innerExpression == null) continue;
                if (temp != null)
                {
                    var ruleCondition = string.IsNullOrEmpty(rule.Condition) ? conditionOperation : rule.Condition;
                    temp = ruleCondition.ToLower() == "and"
                              ? Expression.And(temp, innerExpression)
                              : Expression.Or(temp, innerExpression);
                }
                else
                {
                    temp = innerExpression;
                }
            }
            else
            {

                Expression lowValue = null;
                Expression highValue = null;
                Expression lowCondition;
                Expression highCondition;

                var property = Expression.Property(param, rule.Id);
                object rawValue = null;
                switch (rule.Type.ToLower())
                {
                    case "string":
                        rawValue = rule.Value.First();
                        break;
                    case "integer":

                        if (rule.Value.Count()==1)
                        {
                            if (property.Type == typeof(int))
                            {
                                rawValue = Convert.ToInt32(rule.Value.First());

                            }
                            else if (property.Type == typeof(long))
                            {
                                rawValue = Convert.ToInt64(rule.Value.First());

                            }
                        }
                        else
                        {
                            if (property.Type == typeof (int))
                            {
                                lowValue = Expression.Constant(Convert.ToInt32(rule.Value[0]));
                                highValue = Expression.Constant(Convert.ToInt32(rule.Value[1]));
                            }
                            else if (property.Type == typeof (long))
                            {
                                lowValue = Expression.Constant(Convert.ToInt64(rule.Value[0]));
                                highValue = Expression.Constant(Convert.ToInt64(rule.Value[1]));
                            }
                        }
                        break;
                    case "double":
                        if (rule.Value.Count() == 1)
                        {
                            if (property.Type == typeof(double))
                            {
                                rawValue = Convert.ToDouble(rule.Value.First());

                            }
                            else if (property.Type == typeof(decimal))
                            {
                                rawValue = Convert.ToDecimal(rule.Value.First());

                            }
                        }
                        else
                        {
                            if (property.Type == typeof(double))
                            {
                                lowValue = Expression.Constant(Convert.ToDouble(rule.Value[0]));
                                highValue = Expression.Constant(Convert.ToDouble(rule.Value[1]));
                            }
                            else if (property.Type == typeof(decimal))
                            {
                                lowValue = Expression.Constant(Convert.ToDecimal(rule.Value[0]));
                                highValue = Expression.Constant(Convert.ToDecimal(rule.Value[1]));
                            }
                        }
                        break;
                    case "date":
                    case "time":
                    case "datetime":
                        if (rule.Value.Count() == 1)
                        {

                                rawValue = Convert.ToDateTime(rule.Value.First());


                        }
                        else
                        {

                                lowValue = Expression.Constant(Convert.ToDateTime(rule.Value[0]));
                                highValue = Expression.Constant(Convert.ToDateTime(rule.Value[1]));


                        }
                        break;                         
                    case "boolean":

                        rawValue = Convert.ToBoolean(rule.Value);

                        break;
                }
                var value = Expression.Constant(rawValue);
                Expression condition = null;
                string m = null;

                var containsMethod = rule.Type == "string" ? typeof(string).GetMethod("Contains", new[] { typeof(string) }) : typeof(IList).GetMethod("Contains", new[] { typeof(string) });


                switch (rule.Operator.ToLower())
                {
                    case "equal":
                        condition = Expression.Equal(property, value);
                        break;
                    case "not_equal":
                        condition = Expression.NotEqual(property, value);
                        break;
                    case "in":
                    case "contains":
                        condition = Expression.Call(property, containsMethod, value);
                        break;
                    case "not_in":
                    case "not_contains":
                        condition = Expression.Not(Expression.Call(property, containsMethod, value));
                        break;
                    case "less":
                        condition = Expression.LessThan(property, value);
                        break;
                    case "less_or_equal":
                        condition = Expression.LessThanOrEqual(property, value);
                        break;
                    case "greater":
                        condition = Expression.GreaterThan(property, value);
                        break;
                    case "greater_or_equal":
                        condition = Expression.GreaterThanOrEqual(property, value);
                        break;
                    case "between":
                        lowCondition = Expression.GreaterThanOrEqual(property, lowValue);
                        highCondition = Expression.LessThanOrEqual(property, highValue);
                        condition = Expression.AndAlso(lowCondition, highCondition);
                        break;
                    case "not_between":
                        lowCondition = Expression.GreaterThanOrEqual(property, lowValue);
                        highCondition = Expression.LessThanOrEqual(property, highValue);
                        condition = Expression.Not(Expression.AndAlso(lowCondition, highCondition));
                        break;
                    case "begins_with":
                        throw new Exception("Operation BeginWith is not implemented..");
                    case "not_begins_with":
                        throw new Exception("Operation Not BeginWith is not implemented..");
                    case "ends_with":
                        throw new Exception("Operation Not ends_with is not implemented..");
                    case "not_ends_with":
                        throw new Exception("Operation Not not_ends_with is not implemented..");
                    case "is_empty":
                        condition = Expression.Equal(property, Expression.Constant(""));
                        break;
                    case "is_not_empty":
                        condition = Expression.NotEqual(property, Expression.Constant(""));
                        break;
                    case "is_null":
                        condition = Expression.Equal(property, Expression.Constant(null));
                        break;
                    case "is_not_null":
                        condition = Expression.NotEqual(property, Expression.Constant(null));
                        break;
                }
                if (condition == null) continue;
                ;
                if (temp != null)
                {
                    var ruleCondition = string.IsNullOrEmpty(rule.Condition) ? conditionOperation : rule.Condition;
                    temp = ruleCondition.ToLower() == "and"
                        ? Expression.And(temp, condition)
                        : Expression.Or(temp, condition);
                }
                else
                {
                    temp = condition;
                }
            }
        }
        return temp;


    }

NgnPacket和NgnMonitoringLogic是什么? - Calvin
@calvin NgnPacket是我的实体,而NgnMonitoringLogic是业务逻辑层。 - Mahdi Farhani
@MahdiFarhani 我正在使用SqlCommand()。这个函数能否在文本中生成条件,或者可以与select语句一起使用的东西? - Priyanka Rathee
@Prince Rathee,这个函数无法生成SQL命令,但是Jquery Query Builder有一个插件可以生成SQL文本命令。 - Mahdi Farhani
@MahdiFarhani 噢,不幸的是,在前端使用Jquery暴露SQL是不安全的。还有其他方法吗?这是我的问题链接http://stackoverflow.com/questions/38823665/dynamically-build-a-sql-where-clause-from-c-sharp-objects/38823947 - Priyanka Rathee

-1
我想建议您使用MVC 5 Api。它的绑定引擎足够智能,可以将Json映射为视图模型对象(它是表示json对象的类),然后您可以使用AutoMapper库将该对象处理为服务器实体并将其发送到DAL,EF将完成工作。

OP的问题是:“我怎样将这个JSON转换为EF仓储模式?” - CodeCaster
@CodeCaster,我提出了一种方法。他可以使用ViewModel类将插件中的json数据进行包装,然后按照自己的意愿使用它们。至于来自jquery的绑定,我建议使用asp.net mvc 5 api,它可以完美地绑定json数据。 - Def

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接