/// <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")..];