in src/Core/Resolvers/MsSqlQueryBuilder.cs [272:403]
public string Build(SqlUpsertQueryStructure structure)
{
SourceDefinition sourceDefinition = structure.GetUnderlyingSourceDefinition();
bool isUpdateTriggerEnabled = sourceDefinition.IsUpdateDMLTriggerEnabled;
string tableName = $"{QuoteIdentifier(structure.DatabaseObject.SchemaName)}.{QuoteIdentifier(structure.DatabaseObject.Name)}";
// Predicates by virtue of PK.
string pkPredicates = JoinPredicateStrings(Build(structure.Predicates));
// Predicates by virtue of PK + database policy.
string updatePredicates = JoinPredicateStrings(pkPredicates, structure.GetDbPolicyForOperation(EntityActionOperation.Update));
string updateOperations = Build(structure.UpdateOperations, ", ");
string columnsToBeReturned =
MakeOutputColumns(structure.OutputColumns, isUpdateTriggerEnabled ? string.Empty : OutputQualifier.Inserted.ToString());
string queryToGetCountOfRecordWithPK = $"SELECT COUNT(*) as {COUNT_ROWS_WITH_GIVEN_PK} FROM {tableName} WHERE {pkPredicates}";
// Query to get the number of records with a given PK.
string prefixQuery = $"DECLARE @ROWS_TO_UPDATE int;" +
$"SET @ROWS_TO_UPDATE = ({queryToGetCountOfRecordWithPK}); " +
$"{queryToGetCountOfRecordWithPK};";
// Final query to be executed for the given PUT/PATCH operation.
StringBuilder upsertQuery = new(prefixQuery);
// Query to update record (if there exists one for given PK).
StringBuilder updateQuery = new(
$"IF @ROWS_TO_UPDATE = 1 " +
$"BEGIN " +
$"UPDATE {tableName} " +
$"SET {updateOperations} ");
if (isUpdateTriggerEnabled)
{
// If a trigger is enabled on the entity, we cannot use OUTPUT clause to return the record.
// In such a case, we will use a subsequent select query to get the record. By the time the subsequent select executes,
// the trigger would have already executed and we get the data as it is present in the table.
updateQuery.Append($"WHERE {updatePredicates};");
string subsequentSelect = $"SELECT {columnsToBeReturned} FROM {tableName} WHERE {updatePredicates};";
updateQuery.Append(subsequentSelect);
}
else
{
updateQuery.Append($"OUTPUT {columnsToBeReturned} WHERE {updatePredicates};");
}
// End the IF block.
updateQuery.Append("END ");
// Append the update query to upsert query.
upsertQuery.Append(updateQuery);
if (!structure.IsFallbackToUpdate)
{
// Append the conditional to check if the insert query is to be executed or not.
// Insert is only attempted when no record exists corresponding to given PK.
upsertQuery.Append("ELSE BEGIN ");
// Columns which are assigned some value in the PUT/PATCH request.
string insertColumns = Build(structure.InsertColumns);
// Predicates added by virtue of database policy for create operation.
string createPredicates = JoinPredicateStrings(structure.GetDbPolicyForOperation(EntityActionOperation.Create));
// Query to insert record (if there exists none for given PK).
StringBuilder insertQuery = new($"INSERT INTO {tableName} ({insertColumns}) ");
bool isInsertTriggerEnabled = sourceDefinition.IsInsertDMLTriggerEnabled;
// We can only use OUTPUT clause to return inserted data when there is no trigger enabled on the entity.
if (!isInsertTriggerEnabled)
{
if (isUpdateTriggerEnabled)
{
// This is just an optimisation. If update trigger is enabled, then this build method had created
// columnsToBeReturned without the Inserted prefix.
columnsToBeReturned = MakeOutputColumns(structure.OutputColumns, OutputQualifier.Inserted.ToString());
}
insertQuery.Append($"OUTPUT {columnsToBeReturned}");
}
// If an insert trigger is enabled but there was no update trigger enabled,
// we need to generate columnsToBeReturned without the 'Inserted' prefix on each column.
else if (!isUpdateTriggerEnabled)
{
// This is again just an optimisation. If update trigger was enabled, then the columnsToBeReturned would
// have already been created without any prefix.
columnsToBeReturned = MakeOutputColumns(structure.OutputColumns, string.Empty);
}
// Query to fetch the column values to be inserted into the entity.
string fetchColumnValuesQuery = BASE_PREDICATE.Equals(createPredicates) ?
$"VALUES({string.Join(", ", structure.Values)});" :
$"SELECT {insertColumns} FROM (VALUES({string.Join(", ", structure.Values)})) T({insertColumns}) WHERE {createPredicates};";
// Append the values to be inserted to the insertQuery.
insertQuery.Append(fetchColumnValuesQuery);
if (isInsertTriggerEnabled)
{
// Since a trigger is enabled, a subsequent select query is to be executed to get the inserted record.
// By the time the subsequent select executes, the trigger would have already executed and we get the data as it is present in the table.
string subsequentSelect = $"SELECT {columnsToBeReturned} from {tableName} WHERE {pkPredicates};";
insertQuery.Append(subsequentSelect);
}
// Append the insert query to the upsert query.
upsertQuery.Append(insertQuery.ToString());
// End the ELSE block.
upsertQuery.Append("END");
}
/* An example final upsert query on a table with update/insert triggers enabled would look like:
* DECLARE @ROWS_TO_UPDATE int;
* SET @ROWS_TO_UPDATE = (SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[table] WHERE [dbo].[table].[pkField1] = @param0 AND [dbo].[table].[pkField2] = @param1);
* SELECT COUNT(*) as cnt_rows_to_update FROM [dbo].[table] WHERE [dbo].[table].[pkField1] = @param0 AND [dbo].[table].[pkField2] = @param1;
* IF @ROWS_TO_UPDATE = 1
* BEGIN UPDATE [dbo].[table]
* SET [dbo].[table].[field3] = @param2, [dbo].[table].[field4] = @param3
* WHERE [dbo].[table].[pkField1] = @param0 AND [dbo].[table].[pkField2] = @param1;
* -- Subsequent select query.
* SELECT [pkField1] AS [pkField1], [pkField2] AS [pkField2], [field3] AS [field3], [field4] AS [field4] FROM [dbo].[table]
* WHERE [dbo].[table].[pkField1] = @param0 AND [dbo].[table].[pkField2] = @param1;
* END
* ELSE BEGIN
* INSERT INTO [dbo].[table] ([pkField1], [pkField2], [field3]) VALUES(@param0, @param1, @param2);
* -- Subsequent select query.
* SELECT [pkField1] AS [pkField1], [pkField2] AS [pkField2], [field3] AS [field3], [field4] AS [field4] from [dbo].[table]
* WHERE [dbo].[table].[pkField1] = @param0 AND [dbo].[table].[pkField2] = @param1;
* END
*/
return upsertQuery.ToString();
}