Chain When Clause's using SqlKata

Question

Using the SqlKata query builder, I'd like to build up an update query based on values I have. Given this code:

   var query = new Query("Users");

   var user = new User{Age = 28, Name = "John"};


   query.When(user.Age != null, q => q.AsUpdate(new {Age = user.Age}))
       .When(user.Name != null, q => q.AsUpdate(new {Name = user.Name}));

the generated sql only has the last truthy clause.

Answer 1

this is the expected, your code is similar to the following

if(age != null)
{
    query.AsUpdate(new {Age = age})   
}

if(name != null)
{
    query.AsUpdate(new {Name = name})   
}

in this case, the last truthy statement will always get evaluated.

If you want to use the .When methods at least you have to use the 2nd parameter the WhenNot to evaluate the else part.

var query = new Query("Users").When(age != null, 
    q => q.AsUpdate(new {Age = age}),
    q => q.When(name != null,  q2 => q2.AsUpdate(new {Name = name}))
);

Check this example on the playground here: WhenNot example

But for a more flexible way, I recommend using a dictionary object and filter out the nullable values.

Something similar to (link)

using System.Collections.Generic;
using System.Linq;

var values = new Dictionary<string, object> {
    {"Age", null},
    {"Name", "ahmad"}
}.Where(x => x.Value != null).ToDictionary(x => x.Key, x => x.Value);

var query = new Query("Users").AsInsert(values);