SQLKata 动态 Where 子句

Question

我正在使用SQLKata在C#中构建一个SQL语句，并在Github论坛上找到了一些代码，但它无法编译。我需要帮忙让它能够编译。

我在这一行收到两个错误：query.Where(q =>

编译错误（第17行，第16列）：类型为'System.Func'的lambda表达式中不是所有的代码路径都返回一个值

编译错误（第17行，第16列）：无法将lambda表达式转换为'type'因为它不是委托类型

class Group
{
   public List<Condition> Conditions {get; set;}
}

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

var query = new Query("Countries");

foreach(Group group in groups) 
{
    query.Where(q =>
    {
        foreach(Condition c in group.Conditions)
        {
            q.OrWhere(c.Field, c.Operator, c.Value);
        }
    });
}

.NET Fiddle在这里

Github帖子在这里

更新： 根据Alex的回答，我已经更新了示例。我正在寻找一个语句，所以期望的输出是将OR语句用括号括起来。现在它基本上已经按预期工作了，除了每个组应该在自己的括号内，像下面这样：

SELECT * FROM [Countries] WHERE ([Group1Field1] = @p0 OR [Group1Field2] > @p1 OR [Group1Field3] < @p2 OR [Group1Field4] = @p3) OR ([Group2Field1] = @p4 OR [Group2Field2] >= @p5 OR [Group2Field3] <= @p6) AND [Id] = @p7

最终更新： 解决了。提供了以上期望的输出。谢谢。

var query = new Query("Countries");

    foreach (Group group in groups)
    {
        query.OrWhere(q => {
        foreach (Condition c in group.Conditions)
        {
            q.OrWhere(c.Field, c.Operator, c.Value);
        }
        return q;

        });

    }
query.Where("Id", "=", 10);

- Rick S

---

你期望在 Console.WriteLine(sql); 中输出什么？ - CSDev

你应该将你的解决方案发布为答案并标记为已解决。 - amd

Answer 1

我已经更新了代码，以修复错误。

var query = new Query("Countries");

foreach (Group group in groups)
{
    query.OrWhere(q => {
    foreach (Condition c in group.Conditions)
    {
        q.OrWhere(c.Field, c.Operator, c.Value);
    }
    return q;

    });

}
query.Where("Id", "=", 10);

- Rick S

Answer 2

试试这个：

List<Group> groups = new List<Group>
{
    new Group
    {
        Conditions = new List<Condition>
        {
            new Condition {Field = "Group1Field1", Operator = "=", Value="Group1Value1"},
            new Condition {Field = "Group1Field2", Operator = ">", Value="Group1Value2"},
            new Condition {Field = "Group1Field3", Operator = "<", Value="Group1Value3"},
            new Condition {Field = "Group1Field4", Operator = "=", Value="Group1Value4"}
        }
    },
    new Group
    {
        Conditions = new List<Condition>
        {
            new Condition {Field = "Group2Field1", Operator = "=", Value="Group2Value1"},
            new Condition {Field = "Group2Field2", Operator = ">=", Value="Group2Value2"},
            new Condition {Field = "Group2Field3", Operator = "<=", Value="Group2Value3"}
        }
    }
};

var query = new Query("Countries");
foreach (Group group in groups)
    foreach (Condition c in group.Conditions)
        query.OrWhere(c.Field, c.Operator, c.Value);

Console.WriteLine(new SqlServerCompiler().Compile(query).Sql);

输出结果为：

SELECT * FROM [Countries] WHERE [Group1Field1] = @p0 OR [Group1Field2] > @p1 OR [Group1Field3] < @p2 OR [Group1Field4] = @p3 OR [Group2Field1] = @p4 OR [Group2Field2] >= @p5 OR [Group2Field3] <= @p6

.NET Fiddle

更新：

我检查了文档和测试，没有比以下更好的东西。 要获取输出，您需要使用：

foreach (var group in groups)
{
    if (!group.Conditions.Any())
        continue;

    if (group.Conditions.Count == 1)
    {
        var single = group.Conditions.Single();
        query.OrWhereRaw($"([{single.Field}] {single.Operator} ?)", single.Value);
        continue;
    }

    var first = group.Conditions.First();
    var last = group.Conditions.Last();
    var others = group.Conditions.Skip(1).Take(group.Conditions.Count - 2);

    query.OrWhereRaw($"([{first.Field}] {first.Operator} ?", first.Value);
    foreach (var c in others)
        query.OrWhere(c.Field, c.Operator, c.Value);
    query.OrWhereRaw($"[{last.Field}] {last.Operator} ?)", last.Value);
}

query.Where("Id", "=", 10);

改为：

foreach (Group group in groups)
    foreach (Condition c in group.Conditions)
        query.OrWhere(c.Field, c.Operator, c.Value);

.NETFiddle

的中文翻译为：

{{链接1：.NETFiddle}}

- CSDev

---

感谢您抽出时间来处理这个问题。这几乎是我想要的。我想创建一个“子句”，所以我希望所有的OR语句都在括号内。我已经根据您的答案更新了我的.NET fiddle，它几乎按预期工作。请查看并在需要时进行更新。我想要多个子句，每个组一个。因此，基本上是每个组周围的括号。 - Rick S

@RickS，需要提供哪些参数值，例如p0 = Group1Value1，p1 = Group1Value2？ - Maulik Modi

我查看了文档，没有找到关于参数化查询的任何内容。最接近的可能是像这样的东西：https://sqlkata.com/playground?code=var%20query%20%3D%20new%20Query(%22Posts%22).WhereRaw(%22lower(%5BTitle%5D)%20%3D%20%3F%22%2C%20%22sql%22)%3B - Rick S