protected virtual string MergeFromStagingSql()
{
string whenNotMatchedStatement = $@"WHEN NOT MATCHED BY TARGET THEN
INSERT ({string.Join(", ", PropertiesToInsert.Select(i => i.SqlServer().ColumnName))})
VALUES ({string.Join(", ", PropertiesToInsert.Select(i => $"S.{i.SqlServer().ColumnName}"))})";
string whenMatchedStatement = PropertiesToUpdate.Count == 0 ? "" : $@"WHEN MATCHED THEN
UPDATE SET {string.Join(", ", PropertiesToUpdate.Select(i => $"T.{i.SqlServer().ColumnName} = S.{i.SqlServer().ColumnName}"))}";
return($@"MERGE {SqlTargetTable} WITH (HOLDLOCK) AS T
USING {SqlStagingTableName} AS S
ON {string.Join(" AND ", PropertiesForPivot.Select(i => CreateEqualityConditionSql("T", "S", i)))}
{whenNotMatchedStatement}
{whenMatchedStatement}
OUTPUT INSERTED.*, S.{TempColumnNumOrderName}
INTO {SqlOutputStagingTableName};");
}
=> $@"sp_executesql N'set nocount on; select * from {SqlOutputStagingTableName} order by {TempColumnNumOrderName}';"; //the sp_execute is to prevent EF core to wrap the query into another subquery that will involve a different sorting depending in the situtation (not too proud of this solution, but I really didn't find better)
protected virtual string GetOutputStagingForComputedColumnsSql()
=> $@"sp_executesql N'set nocount on; select T.* from {SqlStagingTableName} as S left join {SqlTargetTable} as T on {string.Join(" AND ", PropertiesForPivot.Select(i => CreateEqualityConditionSql("T", "S", i)))} order by S.{TempColumnNumOrderName}';";