Working with SqlKata

First we need to create instances of the compiler and the queryfactory like this:

using Microsoft.Data.SqlClient;
using SqlKata.Compilers;
using SqlKata.Execution;
using System.Data;

namespace MyDemoNamespace;

public interface IDbInstance
{
    IDbConnection GetConnection();

    Compiler Compiler { get; }

    QueryFactory QueryFactory { get; }
}

public class DbInstance(IConfiguration configuration) : IDbInstance
{
    private readonly Compiler _compiler = new SqlServerCompiler();

    public IDbConnection GetConnection()
    {
        SqlConnection sqlConnection = new(configuration.GetConnectionString("Connection"));

        return sqlConnection;
    }

    public QueryFactory QueryFactory
    {
        get
        {
            QueryFactory queryFactory = new(GetConnection(), _compiler);

            return queryFactory;
        }
    }

    public Compiler Compiler => _compiler;
}

Here is how to update data

int res = await QueryFactory
        .Query("MyTable")
        .Where("Id", signer.Id)   // IMPORTANT: specify the key
        .UpdateAsync(signer);

Here is how to insert data

long signerId = await QueryFactory.Query("MyTable").InsertGetIdAsync<long>(myTable);

Querying and joining tables

OtherTable? otherTable = await QueryFactory.Query("MyTable")
    .LeftJoin("OtherTable", "MyTable.Id", "MyTable.OtherTableId")
    .Where("MyTable.SomeProp", "SomePropValue")
    .Select("OtherTable.*")
    .FirstOrDefaultAsync<OtherTable?>();