Search code examples
c#lambdasqlkata

SQLKata Dynamic Where Clause


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

Solution

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