SQLKata Dynamic Where Clause

Question

I am using SQLKata to build a SQL statement in C# and found some code posted on the Github forum but it does not compile. I need help getting it to compile.

I am receiving two errors on this line query.Where(q =>

Compilation error (line 17, col 16): Not all code paths return a value in lambda expression of type 'System.Func'

Compilation error (line 17, col 16): Cannot convert lambda expression to type 'object' because it is not a delegate 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 is here

Github Post here

UPDATE I've updated the fiddle based on Alex's answer. I was looking for a clause, so the expected output is to have the OR statements surrounded by parenthesis. It's mostly working now as expected except each group should be within it's own parens, like below:

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

Final Update Figured it out. Gives the above expected output. Thanks.

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

you should post your solution as an answer and mark it as solved — amd, May 27 '19 at 08:10

score 3 · Accepted Answer · answered Jun 07 '19 at 15:38

I've updated the code to fix the errors.

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

CSDev · Answer 2 · 2019-05-26T20:22:51.623

Try this:

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

Output is:

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

Update:

I inspected documentation and tests and didn't found anything better than the following. To get the output you need use:

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

instead of:

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

.NETFiddle

Thanks for taking the time on this. It's almost what I am looking for. I want to create a "clause" so I want all the OR statements to be within parenthesis. I have updated my .NET fiddle based on your answer and it is almost working as expected. Please take a look at it and update if needed. I want multiple clauses, one for each group. So basically parens around each group. — Rick S, May 26 '19 at 16:25
@RickS, What is required to supply parameter values i.e. p0 = Group1Value1, p1=Group1Value2? — Maulik Modi, May 02 '21 at 12:06
I took a look at the docs and don't really see anything about parameterized queries. The closet thing would be something like this: 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, May 05 '21 at 00:31

SQLKata Dynamic Where Clause

2 Answers2