sqlkata
adding InsertOrUpdate
Update data if certain conditions are met, insert the data otherwise.
[ ] MySql: on duplicate key update [ ] SqlServer: #93 [ ] Postgres: (don't know)
MySQL: ON DUPLICATE KEY UPDATE
Sqlite: uses a special ON CONFLICT clause, added in version 3.24.0 (2018-06-04).
Postgres: supports ON CONFLICT and ON DUPLICATE KEY UPDATE which are different.
Firebird: has UPDATE OR INSERT.
SQL Server: doesn't have a upsert, but MERGE can be used to the same effect.
Oracle: doesn't appear to have upsert, but does have MERGE, with some caveats.
MERGE is also supported by Postgres and is more powerful so might be worth investigating as a separate command as well.
We are looking for something similar. Currently we are using SQL Server. This would allow use to use an IF EXISTS ... THEN ... ELSE ... construct. I believe at least Postgres supports something like this as well. An example declaration would be:
Program.cs
using SqlKata;
using SqlKata.Compilers;
//*****
var compiler = new SqlServerCompiler();
//*****
var upsertQuery = new Query("dbo.People")
.Where("Id", Guid.NewGuid(), true)
.Where("Version", "<", 10) //***** Concurrency;
.AsUpsert(new Dictionary<string, object>
{
{ "FirstName", "John" },
{ "LastName", "Doe" }
});
//*****
Console.WriteLine(compiler.Compile(upsertQuery).Sql);
//*****
Console.Read();
Result
IF EXISTS (SELECT TOP 1 1 FROM [dbo].[People] WHERE [Id] = @p0)
UPDATE [dbo].[People] SET [FirstName] = @p1, [LastName] = @p2 WHERE [Id] = @p3 AND [Version] < @p4
ELSE
INSERT INTO [dbo].[People] ([FirstName], [LastName], [Id], [Version]) VALUES (@p5, @p6, @p7, @p8)
This implementation allows for reusing all the existing components. The only required "tweak" is the addition of the IsKey property to the AbstractCondition to allow for conditions to be recognized as a condition used for matching a key field. In the example above the Version condition is added as a concurrency condition to be used in the where clause of the update statement, but not for the initial select.
It required the following changes:
SqlKata.Executions\Query.Extensions.cs
public static int Upsert(this Query query, IEnumerable<KeyValuePair<string, object>> values, IDbTransaction transaction = null, int? timeout = null)
{
return CreateQueryFactory(query).Execute(query.AsUpsert(values), transaction, timeout);
}
QueryBuilder_Query.Upsert.cs_
public Query AsUpsert(IEnumerable<KeyValuePair<string, object>> values)
{
if (values == null || values.Any() == false)
{
throw new InvalidOperationException($"{values} cannot be null or empty");
}
Method = "upsert";
ClearComponent("upsert").AddComponent("upsert", new InsertClause
{
Columns = values.Select(x => x.Key).ToList(),
Values = values.Select(x => x.Value).ToList(),
});
return this;
}
QueryBuilder\Clauses\ConditionClause.cs
public abstract class AbstractCondition : AbstractClause
{
public bool IsOr { get; set; } = false;
public bool IsNot { get; set; } = false;
/// <summary>
/// Set True if the condition describes a key field being matched for equality.
/// </summary>
public bool IsKey { get; set; } = false;
}
QueryBuilder\Base.Where.cs
public abstract partial class BaseQuery<Q>
{
public Q Where(string column, string op, object value, bool isKey = false)
{
// omitted
return AddComponent("where", new BasicCondition
{
Column = column,
Operator = op,
Value = value,
IsOr = GetOr(),
IsNot = GetNot(),
IsKey = isKey
});
}
// omitted
}
QueryBuilder\Compilers\Compiler.cs
protected virtual SqlResult CompileRaw(Query query)
{
SqlResult ctx;
if (query.Method == "upsert")
{
ctx = CompileUpsertQuery(query);
}
// omitted
}
protected virtual SqlResult CompileUpsertQuery(Query query)
{
//*****
var ctx = new SqlResult { Query = query };
//***** From;
if (!(ctx.Query.GetOneComponent<AbstractFrom>("from", EngineCode) is FromClause fromClause))
throw new InvalidOperationException("No table set to upsert");
//***** Select;
var selectQuery = new Query(fromClause.Table);
var updateQuery = new Query(fromClause.Table);
var insertQuery = new Query(fromClause.Table);
//***** Conditions for select and update;
if (!ctx.Query.HasComponent("where", EngineCode))
throw new InvalidOperationException("No where conditions set. Requires at least one key column");
var conditions = ctx.Query.GetComponents<AbstractCondition>("where", EngineCode);
// for insert requires to be set as insert parameters.
var selectQueryConditions = new List<AbstractCondition>();
var updateQueryConditions = new List<AbstractCondition>();
foreach (var condition in conditions)
{
if (condition.IsKey) selectQueryConditions.Add((AbstractCondition)condition.Clone());
updateQueryConditions.Add((AbstractCondition)condition.Clone());
}
//*****
selectQuery.Clauses.AddRange(selectQueryConditions);
updateQuery.Clauses.AddRange(updateQueryConditions);
//*****
var updateInsertClause = ctx.Query.GetOneComponent<InsertClause>("upsert", EngineCode);
updateInsertClause = (InsertClause)updateInsertClause.Clone(); //***** Doesn't do deep clone;
updateInsertClause.Columns = new List<string>(updateInsertClause.Columns);
updateInsertClause.Values = new List<object>(updateInsertClause.Values);
updateInsertClause.Component = "update";
updateQuery.Clauses.Add(updateInsertClause);
//***** Add where as insert columns;
var insertInsertClause = ctx.Query.GetOneComponent<InsertClause>("upsert", EngineCode);
insertInsertClause = (InsertClause)insertInsertClause.Clone();
insertInsertClause.Columns = new List<string>(insertInsertClause.Columns);
insertInsertClause.Values = new List<object>(insertInsertClause.Values);
insertInsertClause.Component = "insert";
//***** Add conditions as insert values;
foreach (var condition in conditions)
if (condition is BasicCondition basicCondition)
{
insertInsertClause.Columns.Add(basicCondition.Column);
insertInsertClause.Values.Add(basicCondition.Value);
}
insertQuery.Clauses.Add(insertInsertClause);
//*****
selectQuery.SelectRaw("TOP 1 1");
//*****
var selectCtx = CompileSelectQuery(selectQuery);
var updateCtx = CompileUpdateQuery(updateQuery);
var insertCtx = CompileInsertQuery(insertQuery);
//*****
ctx.Bindings.AddRange(selectCtx.Bindings);
ctx.Bindings.AddRange(updateCtx.Bindings);
ctx.Bindings.AddRange(insertCtx.Bindings);
//*****
ctx.RawSql = $"IF EXISTS ({selectCtx.RawSql})\r\n\t{updateCtx.RawSql}\r\nELSE\r\n\t{insertCtx.RawSql}";
//*****
return ctx;
}
Curious on your thoughts. Does anyone else have more experience with the other database systems for supporting this or similar fashion?
