动态LINQ查询多个/标准不明 [英] Dynamic linq query with multiple/unknown criteria

问题描述

我期待实现一个系统，由此采用了建设的条件，然后返回结果数据从数据库返回的。在present，有一个存储过程，而动态生成SQL并执行它。这是我想删除某个特定问题。

I am looking to implement a system whereby a use that 'build' conditions and then return the resulting data back from the database. At present, there is a stored procedure which generates SQL on the fly and executes it. This is a particular issue that I want to remove.

我的问题是从我可以我的标准范围内有多个字段，并且对于每个字段，有可能是1个或更多的值，用不同的潜在运营商的事实来。

My problem is coming from the fact that I can have multiple fields within my criteria, and for each of these fields, there could be 1 or more values, with different potential operators.

例如，

from t in Contacts 
where t.Email == "[e-mail]" || t.Email.Contains ("mydomain")
where t.Field1 == "valuewewant"
where t.Field2 != "valuewedontwant"
select t

领域，标准和运营商都存储在数据库中（和列表＆LT; FieldCriteria＆GT; ），并会是这样的一些东西（基于以上）;

The field, criteria and operator are stored in the database (and List<FieldCriteria>) and would be some thing like this (based on above);

Email, Equals, "[e-mail]"
Email, Contains, "mydomain" Field1,
Equals, "valuewewant" Field2,
DoesNotEqual, "valuewedontwant"

或

new FieldCriteria
{
FieldName = "Email",
Operator = 1, 
Value = "[e-mail]"
}

因此​​，使用我有，我希望能够建立一个查询任意数量的条件的信息。我见过previous链接动态LINQ和predicateBuilder，但我无法想象这是一个解决方案，以我自己的问题。

So using the information that I have, I want to be able to build a query with any number of conditions. I have seen previous links to Dynamic Linq and PredicateBuilder, but am not able to visualise this as a solution to my own problem.

任何建议将是AP preciated。

Any suggestions would be appreciated.

更新

从大约动态的LINQ的建议继，我想出了一个非常基本的解决方案，使用单算，有2场和多个标准。有点粗，此刻在LinqPad codeD，但结果正是我想要的;

Following on from the suggestion about Dynamic Linq, I came up with a very basic solution, using a Single Operator, with 2 Fields and multiple Criteria. A little crude at the moment as coded in LinqPad, but the results are exactly what I wanted;

enum Operator
{
    Equals = 1,
}

class Condition
{
    public string Field { get; set; }
    public Operator Operator { get; set;}
    public string Value { get; set;}
}

void Main()
{
    var conditions = new List<Condition>();

    conditions.Add(new Condition {
        Field = "Email",
        Operator = Operator.Equals,
        Value = "[e-mail]"
    });

    conditions.Add(new Condition {
        Field = "Email",
        Operator = Operator.Equals,
        Value = "[e-mail]"
    });

    conditions.Add(new Condition {
        Field = "Field1",
        Operator = Operator.Equals,
        Value = "Chris"
    });

    var statusConditions = "Status = 1";

    var emailConditions = from c in conditions where c.Field == "Email" select c;
    var field1Conditions = from c in conditions where c.Field == "Field1" select c;


    var emailConditionsFormatted = from c in emailConditions select string.Format("Email=\"{0}\"", c.Value);
    var field1ConditionsFormatted = from c in field1Conditions select string.Format("Field1=\"{0}\"", c.Value);

    string[] conditionsArray = emailConditionsFormatted.ToArray();
    var emailConditionsJoined = string.Join("||", conditionsArray);
    Console.WriteLine(String.Format("Formatted Condition For Email: {0}",emailConditionsJoined));

    conditionsArray = field1ConditionsFormatted.ToArray();
    var field1ConditionsJoined = string.Join("||", conditionsArray);
    Console.WriteLine(String.Format("Formatted Condition For Field1: {0}",field1ConditionsJoined));



    IQueryable results = ContactView.Where(statusConditions);

    if (emailConditions != null)
    {
        results = results.Where(emailConditionsJoined);
    }

    if (field1Conditions != null)
    {
        results = results.Where(field1ConditionsJoined);
    }

    results = results.Select("id");

    foreach (int id in results)
    {
        Console.WriteLine(id.ToString());
    }
}

随着生成的SQL;

With an SQL generated of;

-- Region Parameters
DECLARE @p0 VarChar(1000) = 'Chris'
DECLARE @p1 VarChar(1000) = '[e-mail]'
DECLARE @p2 VarChar(1000) = '[e-mail]'
DECLARE @p3 Int = 1
-- EndRegion
SELECT [t0].[id]
FROM [Contacts].[ContactView] AS [t0]
WHERE ([t0].[field1] = @p0) AND (([t0].[email] = @p1) OR ([t0].[email] = @p2)) AND ([t0].[status] = @p3)

和控制台输出：

Formatted Condition For Email: Email="[e-mail]"||Email="[e-mail]"
Formatted Condition For Field1: Field1="Chris"

只需要打扫一下，并添加其他运营商，它看起来不错。

Just need clean this up and add the other Operators and it is looking good.

如果任何人有至今对这个有任何意见，任何输入将是pciated AP $ P $

If anyone has any comments on this so far, any input would be appreciated

推荐答案

我觉得动态LINQ将是选项之一。 DLINQ允许你指定的LINQ查询为串和DLINQ的一部分，那么编译该字符串前pression树，以便传递给底层的LINQ提供程序。您的需要就是你也需要在运行时创建防爆pression树木一样即。

I think Dynamic LINQ will be one of option. DLINQ allows you to specify part of the LINQ query as "string" and DLINQ then compiles that string to Expression tree so that be passed to the underlying LINQ provider. Your need is also same i.e you need to create Expression trees at runtime.

我会建议你做的 FieldCriteria 属性操作为枚举从而重新present所需的所有操作（等于，小于等等）。然后，你将需要编写一个函数， FieldCriteria 的列表，并返回前pression的字符串，然后可以被送入DLINQ获得前pression树。

I would suggest you to make the property Operator in FieldCriteria as an Enum which represent all the required operations (equals, less then etc). Then you will need to write a function that takes a list of FieldCriteria and return a "expression" string which then can be fed into DLINQ to get the expression tree.

这篇关于动态LINQ查询多个/标准不明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！