/// <summary>
/// Use sql merge to get all of the generated values back for all rows AND be able to map them to the correct objects
/// </summary>
/// <param name="mergeSerializer">When this method returns, contains a <see cref="Contracts.IMergeSerializer"/> that can be used to serialize collections into what's expected by the @serialized parameter</param>
/// <param name="mergeDefinition">Should inserts/updates/deletes be included in the merge statement? Should there be additional filtering?</param>
/// <param name="tableMap">Optionally specify a table map to be used to build out the query</param>
/// <remarks>
/// Note: The OPENJSON function is available only under compatibility level 130 or higher.
/// If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function.
/// </remarks>
/// <seealso cref="https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql"/>
/// <returns>
/// Creates sql which expects a @serialized parameter whose value is a json or xml array string; if xml, the main and child nodes are named '_'.
/// The properties of the collection items are named _0 through _z, _10 through _zz, _100 through _zzz, and so-on.
/// A collection item property '_' is expected, which contains the index of the item within the array.
/// </returns>
public string Merge <T>(out Contracts.IMergeSerializer <T> mergeSerializer, MergeDefinition <T> mergeDefinition, ITableMap <T>?tableMap = null, bool useJson = false)
{
//--Build a table map and figure out which columns should be serialized
tableMap ??= GetTableMap <T>();
//--Build a merge serializer and the core of the merge statement
mergeSerializer = useJson ? new JsonMergeSerializer <T>(tableMap) : new XmlMergeSerializer <T>(tableMap);
var outputColumns = BuildOutputColumns(mergeDefinition, tableMap);
var merge = new System.Text.StringBuilder();
merge.AppendLine("declare @outputResult table(");
merge.AppendLine(string.Join($",{Environment.NewLine}", outputColumns.Select(column => $" {column.columnIdentifier} {column.sqlType}")));
merge.AppendLine(");");
merge.AppendLines(BuildCoreMergeLines(mergeSerializer, mergeDefinition, tableMap));
//--Build the components of the merge
merge.AppendLines(BuildInsertLines(mergeDefinition, tableMap));
merge.AppendLines(BuildUpdateLines(mergeDefinition, tableMap));
merge.AppendLines(BuildDeleteLines(mergeDefinition));
//--Build the merge's output statement so we can determine which records were inserted/updated/deleted
merge.AppendLine($"output {string.Join(", ", outputColumns.Select(oc => oc.value))} into @outputResult({string.Join(", ", outputColumns.Select(column => column.columnIdentifier))});");
merge.Append("select * from @outputResult;");
return(merge.ToString());
}