The purpose of this blog is to outline the high level steps that we needed to implement in order to allow the MERGE INTO statement to execute.
The changes were really concentrated into a SQL statement constructor utility class as follows…
/// <summary>
/// This class holds definitions for common SQL statements to be executed
/// against the DB directly
/// </summary>
public static class SqlStatementConstructor
{
/// <summary>
/// Creates the TSQL statement to adds the items to custom list via a MERGE operation.
/// </summary>
/// <param name="customListId">The custom list id.</param>
/// <param name="selectionQuery">The selection query.</param>
/// <param name="customListType">Type of the custom list.</param>
/// <returns>
/// A TSQL statement that can be executed against the DB.
/// </returns>
public static string AddItemsToCustomList(int customListId, DetachedCriteria selectionQuery, ListType customListType)
{
// First, add the ID projection to the selection query, there's no need to pull any other fields.
selectionQuery.SetProjection(Projections.Id());
// Prepare the selectionQuery and extract the NH name of the ID column.
// The format of the initial selected field (which is the one we're after) will be:
// SELECT this_.ID as XXXX FROM... its very likely that NH will always use the variable y0_ as the ID
// but we always extract rather than assume.
string itemSelectionQuery = selectionQuery.ToSql();
int endOfFirstAs = itemSelectionQuery.IndexOf(" as ") + 4;
int lengthOfIdFieldName = itemSelectionQuery.IndexOf(" FROM ") - endOfFirstAs;
string selectionQueryIdName = itemSelectionQuery.Substring(endOfFirstAs, lengthOfIdFieldName);
string customListField = GetCustomListFieldName(customListType);
// Now add our custom list id into the select in the correct place very simply by inserting after the initial SELECT.
itemSelectionQuery = itemSelectionQuery.Insert(6, string.Format(" {0} as ListID, ", customListId));
StringBuilder sb = new StringBuilder();
sb.Append("MERGE dbo.CustomListValue AS Target");
sb.Append(" USING (");
sb.Append(itemSelectionQuery);
sb.Append(") AS Source");
sb.Append(string.Format(" ON (Target.CustomListID = Source.ListID AND Target.{0} = Source.{1})", customListField, selectionQueryIdName));
sb.Append(" WHEN MATCHED THEN");
Read more: EMC Consulting