/// <summary>
/// Conditionally updates the given <paramref name="source"/> transcationally.
/// </summary>
/// <typeparam name="TEntity">The type of entity to update.</typeparam>
/// <param name="dbContext">The EF <see cref="DbContext"/>.</param>
/// <param name="source">The local source of the update. These entities will update their corresponding match in the database if one exists.</param>
/// <param name="condition">
/// Optional: The condition on which to go through with an update on an entity level. You can use this to perform checks against
/// the version of the entity that's in the database and determine if you want to update it. For example, you could
/// update only if the version you're trying to update with is newer:
/// <c>updateEntry => updateEntry.Current.Version < updateEntry.Incoming.Version</c>.
/// </param>
/// <param name="clusivityBuilder">
/// A builder for included/excluded properties to update. This is used to target which properties to update. If no builder
/// is supplied, all non-primary key properties will be updated.
/// </param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>
/// A collection of the entities which were updated. If for example an entity is missing in the DB,
/// or it is not matching a the given condition, it will not be upate and not be included here.
/// </returns>
public static Task <IReadOnlyCollection <TEntity> > UpdateAsync <TEntity>(
this DbContext dbContext,
IReadOnlyCollection <TEntity> source,
Expression <Func <UpdateEntry <TEntity>, bool> > condition = null,
IClusivityBuilder <TEntity> clusivityBuilder = null,
CancellationToken cancellationToken = default)
where TEntity : class
{
if (dbContext == null)
{
throw new ArgumentNullException(nameof(dbContext));
}
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
return(UpdateInternalAsync(dbContext, source, condition, clusivityBuilder, cancellationToken));
}
/// <summary>
/// Syncs the <paramref name="source"/> entities into the <paramref name="target"/> queryable. This entails:
/// 1. Inserting any entities which exist in source, but not in target, into target
/// 2. Updating the properties of any entities which exist in both source and target to the values found in source
/// 3. Deleting any entities in target which do not exist in source.
///
/// This operation performs a full sync (also known as MERGE), and is to be used in scenarios where a target should replicate
/// the source. For situations that do not require deletion, use
/// <see cref="UpsertAsync{TEntity}(DbContext, IReadOnlyCollection{TEntity}, IClusivityBuilder{TEntity}, IClusivityBuilder{TEntity}, CancellationToken)"/>
/// and for scenarios which do not require updates, use <see cref="SyncWithoutUpdateAsync{TEntity}(DbContext, IQueryable{TEntity}, IReadOnlyCollection{TEntity}, IClusivityBuilder{TEntity}, CancellationToken)"/>.
/// </summary>
/// <remarks>
/// The <paramref name="target"/> should be selected with care as any entities not matched in <paramref name="source"/> will be deleted from the target.
/// If you're only interesting in syncing a subset, make sure to filter/scope down the <paramref name="target"/> to meet your needs before passing it in.
/// If you want to sync an entire table, simply pass the <see cref="DbSet{TEntity}"/> for your entity type, but note that this will delete any entities not found in source.
/// </remarks>
/// <typeparam name="TEntity">The entity type.</typeparam>
/// <param name="dbContext">EF Db Context</param>
/// <param name="target">The target to be synced to. Specify a queryable based on the <see cref="DbSet{TEntity}"/> of your entity type. Specify with care.</param>
/// <param name="source">The collection of entities to sync from.</param>
/// <param name="insertClusivityBuilder">The clusivity builder for entities which will be inserted.</param>
/// <param name="updateClusivityBuilder">The clusivity builder for entities which will be updated. You may use this to only update a subset of properties.</param>
/// <param name="cancellationToken">Cancellation token.</param>
/// <returns>The result of the sync, containing which entities were inserted, updated, and deleted.</returns>
public static async Task <ISyncResult <TEntity> > SyncAsync <TEntity>(this DbContext dbContext, IQueryable <TEntity> target, IReadOnlyCollection <TEntity> source, IClusivityBuilder <TEntity> insertClusivityBuilder = null, IClusivityBuilder <TEntity> updateClusivityBuilder = null, CancellationToken cancellationToken = default)
where TEntity : class, new()
=> await SyncInternalAsync(
private static async Task <IReadOnlyCollection <TEntity> > UpdateInternalAsync <TEntity>(
DbContext dbContext,
IReadOnlyCollection <TEntity> source,
Expression <Func <UpdateEntry <TEntity>, bool> > condition,
IClusivityBuilder <TEntity> clusivityBuilder,
CancellationToken cancellationToken)
where TEntity : class
{
if (source.Count == 0)
{
// Nothing to do
return(Array.Empty <TEntity>());
}
ManipulationExtensionsConfiguration configuration = dbContext.GetConfiguration();
var stringBuilder = new StringBuilder(1000);
IEntityType entityType = dbContext.Model.FindEntityType(typeof(TEntity));
string tableName = entityType.GetSchemaQualifiedTableName();
IKey primaryKey = entityType.FindPrimaryKey();
IProperty[] properties = entityType.GetProperties().ToArray();
IProperty[] nonPrimaryKeyProperties = properties.Except(primaryKey.Properties).ToArray();
IProperty[] propertiesToUpdate = clusivityBuilder == null ? nonPrimaryKeyProperties : clusivityBuilder.Build(nonPrimaryKeyProperties);
var parameters = new List <object>();
bool isSqlite = dbContext.Database.IsSqlite();
if (isSqlite)
{
string incomingInlineTableCommand = new StringBuilder().AppendSelectFromInlineTable(properties, source, parameters, "x", sqliteSyntax: true).ToString();
IQueryable <TEntity> incoming = CreateIncomingQueryable(dbContext, incomingInlineTableCommand, condition, parameters);
(string sourceCommand, IReadOnlyCollection <SqlParameter> sourceCommandParameters) = incoming.ToSqlCommand(filterCompositeRelationParameter: true);
parameters.AddRange(sourceCommandParameters);
const string TempDeleteTableName = "EntityFrameworkManipulationUpdate";
// Create temp table with the applicable items to be update
stringBuilder.AppendLine("BEGIN TRANSACTION;")
.Append("DROP TABLE IF EXISTS ").Append(TempDeleteTableName).AppendLine(";")
.Append("CREATE TEMP TABLE ").Append(TempDeleteTableName).Append(" AS ")
.Append(sourceCommand).AppendLine(";");
// Update the target table from the temp table
stringBuilder.Append("UPDATE ").Append(tableName).AppendLine(" SET")
.AppendJoin(",", propertiesToUpdate.Select(property => FormattableString.Invariant($"{property.Name}=incoming.{property.Name}"))).AppendLine()
.Append("FROM ").Append(TempDeleteTableName).AppendLine(" AS incoming")
.Append("WHERE ").AppendJoinCondition(primaryKey, tableName, "incoming").AppendLine("; ");
// Select the latest state of the affected rows in the table
stringBuilder
.Append("SELECT target.* FROM ").Append(tableName).AppendLine(" AS target")
.Append(" JOIN ").Append(TempDeleteTableName).Append(" AS source ON ").AppendJoinCondition(primaryKey).AppendLine(";")
.Append("COMMIT;");
}
else
{
bool outputInto = configuration.SqlServerConfiguration.DoesEntityHaveTriggers <TEntity>();
string userDefinedTableTypeName = null;
if (configuration.SqlServerConfiguration.ShouldUseTableValuedParameters(properties, source) || outputInto)
{
userDefinedTableTypeName = await dbContext.Database.CreateUserDefinedTableTypeIfNotExistsAsync(entityType, configuration.SqlServerConfiguration, cancellationToken);
}
if (outputInto)
{
stringBuilder.AppendOutputDeclaration(userDefinedTableTypeName);
}
string incomingInlineTableCommand = userDefinedTableTypeName != null ?
new StringBuilder().Append("SELECT * FROM ").AppendTableValuedParameter(userDefinedTableTypeName, properties, source, parameters).ToString()
:
new StringBuilder().AppendSelectFromInlineTable(properties, source, parameters, "x").ToString();
IQueryable <TEntity> incoming = CreateIncomingQueryable(dbContext, incomingInlineTableCommand, condition, parameters);
(string sourceCommand, IReadOnlyCollection <SqlParameter> sourceCommandParameters) = incoming.ToSqlCommand(filterCompositeRelationParameter: true);
parameters.AddRange(sourceCommandParameters);
// Here's where we have to cheat a bit to get an efficient query. If we were to place the sourceCommand in a CTE,
// then join onto that CTE in the UPADATE, then the query optimizer can't handle mergining the looksups, and it will do two lookups,
// one for the CTE and one for the UPDATE JOIN. Instead, we'll just pick everything put the SELECT part of the sourceCommand and
// attach it to the UPDATE command, which works since it follows the exact format of a SELECT, except for the actual selecting of properties.
string fromJoinCommand = sourceCommand[sourceCommand.IndexOf("FROM")..];
