protected virtual void GenerateRootTableCopy()
{
var insertColumns = RootTable.Columns
.Where(c => c.IsCopyable)
.Where(c => !ExcludedColumns.Contains(c));
var insertColumnNames = insertColumns
.Select(c => $"[{c.Name}]");
var insertColumnValues = insertColumns
.Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]");
string insertClause = !insertColumns.Any()
? @"INSERT DEFAULT VALUES"
: $@"INSERT (
{string.Join(Separators.Cnlw8, insertColumnNames)})
VALUES (
{string.Join(Separators.Cnlw8, insertColumnValues)})";
string setStatement = PrimaryKeyOutputParameterName != null
? $@"
SET {PrimaryKeyOutputParameterName} = SCOPE_IDENTITY();"
: "";
ProcedureBody.AppendLine($@"
MERGE INTO [{RootTable.Schema.Name}].[{RootTable.Name}] AS Target
USING (
SELECT *
FROM [{RootTable.Schema.Name}].[{RootTable.Name}]
WHERE [{RootTable.PrimaryKey.Column.Name}] = {PrimaryKeyParameterName}
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
{insertClause}{GenerateOutputClause(RootTable)}{setStatement}");
}
/// <summary>
/// Returns the list of columns the materializer would like to have.
/// </summary>
/// <returns>IReadOnlyList<System.String>.</returns>
/// <exception cref="InvalidOperationException">Cannot specify both included and excluded columns/properties.</exception>
public override IReadOnlyList <string> DesiredColumns()
{
if (IncludedColumns != null && ExcludedColumns != null)
{
throw new InvalidOperationException("Cannot specify both included and excluded columns/properties.");
}
if (IncludedColumns != null)
{
return(IncludedColumns);
}
if (ExcludedColumns != null)
{
var result = CommandBuilder.TryGetColumns();
if (result.Count == 0)
{
throw new InvalidOperationException("Cannot exclude columns with this command builder.");
}
result = result.Where(x => !ExcludedColumns.Contains(x.SqlName)).ToList();
if (result.Count == 0)
{
throw new MappingException("All columns were excluded. The available columns were " + string.Join(", ", CommandBuilder.TryGetColumns().Select(x => x.SqlName)));
}
return(result.Select(x => x.SqlName).ToList());
}
return(AllColumns);
}
/// <summary>
/// Returns the list of columns the result materializer would like to have.
/// </summary>
/// <returns>IReadOnlyList<System.String>.</returns>
/// <exception cref="MappingException">
/// Cannot find a constructor on {desiredType.Name} with the types [{types}]
/// </exception>
/// <remarks>
/// If AutoSelectDesiredColumns is returned, the command builder is allowed to choose which
/// columns to return. If NoColumns is returned, the command builder should omit the
/// SELECT/OUTPUT clause.
/// </remarks>
public override IReadOnlyList <string> DesiredColumns()
{
if (Constructor == null)
{
if (IncludedColumns != null && ExcludedColumns != null)
{
throw new InvalidOperationException("Cannot specify both included and excluded columns/properties.");
}
if (IncludedColumns != null)
{
return(IncludedColumns);
}
IReadOnlyList <string> result = ObjectMetadata.ColumnsFor;
if (ExcludedColumns != null)
{
result = result.Where(x => !ExcludedColumns.Contains(x)).ToList();
}
return(result);
}
if (IncludedColumns != null)
{
throw new NotImplementedException("Cannot specify included columns/properties with constructors. See #295");
}
if (ExcludedColumns != null)
{
throw new InvalidOperationException("Cannot specify excluded columns/properties with constructors.");
}
return(Constructor.ParameterNames);
}
protected virtual void GenerateDependentTableCopy(ReferenceGraph.Vertex vertex)
{
var table = vertex.Table;
var dependentReferences = vertex.DependentReferences
// Check constraints can lead to nullable dependent reference columns. However, rows needs to be dependent
// on something copied, so if there's only one dependency we might as well inner join on it regardless.
.Select(r => new { r.ParentColumn, r.ReferencedTable, UseLeftJoin = r.ParentColumn.IsNullable && vertex.DependentReferences.Count > 1 })
.ToReadOnlyList();
var selectColumnNames = dependentReferences
.Select((r, i) => $"j{i}.InsertedID j{i}InsertedID");
// If necessary (it usually isn't), make sure something in the row is dependent on something copied. Faster to
// or together where-ins than it is to coalesce on all left joined columns and perform a not null check.
string fromClause = dependentReferences.All(r => r.UseLeftJoin)
? $@"FROM (
SELECT *
FROM [{table.Schema}].[{table.Name}]
WHERE {string.Join($"{Separators.Nlw16}OR ", dependentReferences.Select(r => $"[{r.ParentColumn.Name}] IN (SELECT ExistingID FROM {TableVariableNames[r.ReferencedTable]})"))}
) AS copy"
: $@"FROM [{table.Schema.Name}].[{table.Name}] copy";
var joinClauses = dependentReferences
.Select((r, i) => $"{(r.UseLeftJoin ? "LEFT " : "")}JOIN {TableVariableNames[r.ReferencedTable]} j{i}{Separators.Nlw12}ON copy.[{r.ParentColumn.Name}] = j{i}.ExistingID");
var dependentInsertColumnNames = dependentReferences
.Select(r => $"[{r.ParentColumn.Name}]");
var dependentInsertColumnValues = dependentReferences
// A potential left join should leave InsertedID null only if the original value was null, since this is a root copy.
.Select((r, i) => $"j{i}InsertedID");
var nonDependentInsertColumns = table.Columns
.Where(c => c.IsCopyable)
.Where(c => !ExcludedColumns.Contains(c))
.Where(c => !dependentReferences.Select(r => r.ParentColumn).Contains(c));
var nonDependentInsertColumnNames = nonDependentInsertColumns
.Select(c => $"[{c.Name}]");
var nonDependentInsertColumnValues = nonDependentInsertColumns
.Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]");
ProcedureBody.AppendLine($@"
MERGE INTO [{table.Schema.Name}].[{table.Name}] AS Target
USING (
SELECT
copy.*,
{string.Join(Separators.Cnlw12, selectColumnNames)}
{fromClause}
{string.Join(Separators.Nlw8, joinClauses)}
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
{string.Join(Separators.Cnlw8, dependentInsertColumnNames.Concat(nonDependentInsertColumnNames))})
VALUES (
{string.Join(Separators.Cnlw8, dependentInsertColumnValues.Concat(nonDependentInsertColumnValues))}){GenerateOutputClause(table)}");
}
protected override void GenerateDependentTableCopy(ReferenceGraph.Vertex vertex)
{
var table = vertex.Table;
var dependentReferences = vertex.DependentReferences;
// Rows needs to be dependent on something copied, so if there's only one dependency we might as well inner join on it.
bool useLeftJoin = dependentReferences.Count > 1;
var selectColumnNames = dependentReferences
.Select((r, i) => $"j{i}.InsertedID j{i}InsertedID");
// If necessary (it often is), make sure something in the row is dependent on something copied. Faster to
// or together where-ins than it is to coalesce on all left joined columns and perform a not null check.
string fromClause = useLeftJoin
? $@"FROM (
SELECT *
FROM [{table.Schema}].[{table.Name}]
WHERE {string.Join($"{Separators.Nlw16} OR ", dependentReferences.Select(r => $"[{r.ParentColumn.Name}] IN (SELECT ExistingID FROM {TableVariableNames[r.ReferencedTable]})"))}
) AS copy"
: $@"FROM [{table.Schema.Name}].[{table.Name}] copy";
var joinClauses = dependentReferences
.Select((r, i) => new { r.ParentColumn, r.ReferencedTable, JoinString = $"{(useLeftJoin ? "LEFT " : "")}JOIN" })
.Select((r, i) => $"{r.JoinString} {TableVariableNames[r.ReferencedTable]} j{i}{Separators.Nlw12}ON copy.[{r.ParentColumn.Name}] = j{i}.ExistingID");
var dependentInsertColumnNames = dependentReferences
.Select(r => $"[{r.ParentColumn.Name}]");
var dependentInsertColumnValues = dependentReferences
.Select((r, i) => useLeftJoin ? $"COALESCE(j{i}InsertedID, [{r.ParentColumn.Name}])" : $"j{i}InsertedID");
var nonDependentInsertColumns = table.Columns
.Where(c => c.IsCopyable)
.Where(c => !ExcludedColumns.Contains(c))
.Where(c => !dependentReferences.Select(r => r.ParentColumn).Contains(c));
var nonDependentInsertColumnNames = nonDependentInsertColumns
.Select(c => $"[{c.Name}]");
var nonDependentInsertColumnValues = nonDependentInsertColumns
.Select(c => UpdateParameterNames.ContainsKey(c) ? UpdateParameterNames[c] : $"Source.[{c.Name}]");
ProcedureBody.AppendLine($@"
MERGE INTO [{table.Schema.Name}].[{table.Name}] AS Target
USING (
SELECT
copy.*,
{string.Join(Separators.Cnlw12, selectColumnNames)}
{fromClause}
{string.Join(Separators.Nlw8, joinClauses)}
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
{string.Join(Separators.Cnlw8, dependentInsertColumnNames.Concat(nonDependentInsertColumnNames))})
VALUES (
{string.Join(Separators.Cnlw8, dependentInsertColumnValues.Concat(nonDependentInsertColumnValues))}){GenerateOutputClause(table)}");
}
/// <summary>
/// Returns the list of columns the result materializer would like to have.
/// </summary>
/// <returns>IReadOnlyList<System.String>.</returns>
/// <exception cref="MappingException">
/// Cannot find a constructor on {desiredType.Name} with the types [{types}]
/// </exception>
/// <remarks>
/// If AutoSelectDesiredColumns is returned, the command builder is allowed to choose which
/// columns to return. If NoColumns is returned, the command builder should omit the
/// SELECT/OUTPUT clause.
/// </remarks>
public override IReadOnlyList <string> DesiredColumns()
{
//We need to pick the constructor now so that we have the right columns in the SQL.
//If we wait until materialization, we could have missing or extra columns.
if (Constructor == null && !ObjectMetadata.Constructors.HasDefaultConstructor)
{
Constructor = InferConstructor();
}
if (Constructor == null)
{
IReadOnlyList <string> result = ObjectMetadata.ColumnsFor;
if (result.Count == 0)
{
throw new MappingException($"Type {typeof(TObject).Name} has no writable properties. Please use the InferConstructor option or the WithConstructor method.");
}
if (IncludedColumns != null && ExcludedColumns != null)
{
throw new InvalidOperationException("Cannot specify both included and excluded columns/properties.");
}
if (IncludedColumns != null)
{
return(IncludedColumns);
}
if (ExcludedColumns != null)
{
result = result.Where(x => !ExcludedColumns.Contains(x)).ToList();
}
return(result);
}
if (IncludedColumns != null)
{
throw new NotImplementedException("Cannot specify included columns/properties with non-default constructors. See #295");
}
if (ExcludedColumns != null)
{
throw new InvalidOperationException("Cannot specify excluded columns/properties with non-default constructors.");
}
return(Constructor.ParameterNames);
}