public async Task <int> BulkMergeAsync(
IQueryable <TEntity> queryable,
List <TEntity> current,
Func <TEntity, object> updateColumnsToExlcude = null,
BulkMergeNotMatchedBehavior notMatchedBehavior = BulkMergeNotMatchedBehavior.DoNothing,
Expression <Func <TEntity> > whenNotMatched = null)
{
var previous = await queryable.ToListAsync();
var notMatched = previous
.Where(p => current.All(c => !PrimaryKeyEquals(c, p)))
.ToList();
var toAdd = current
.Where(c => previous.All(p => !PrimaryKeyEquals(c, p)))
.ToList();
var toUpdate = previous
.Where(p => current.Any(c => PrimaryKeyEquals(c, p)))
.ToList();
switch (notMatchedBehavior)
{
case BulkMergeNotMatchedBehavior.Delete:
DbContext.RemoveRange(notMatched);
break;
case BulkMergeNotMatchedBehavior.Update when whenNotMatched != null:
foreach (var entity in notMatched)
{
var notMatchedProps = whenNotMatched.GetSetPropertyNames();
var notMatchedEntity = whenNotMatched.Compile().Invoke();
foreach (var property in Properties.Where(x => notMatchedProps.Contains(x.Name)))
{
property.PropertyInfo.SetValue(entity, property.PropertyInfo.GetValue(notMatchedEntity));
}
}
break;
}
await DbContext.AddRangeAsync(toAdd);
foreach (var entity in toUpdate)
{
var updatedEntity = current.First(c => PrimaryKeyEquals(c, entity));
foreach (var property in Properties)
{
property.PropertyInfo.SetValue(entity, property.PropertyInfo.GetValue(updatedEntity));
}
}
return(await DbContext.SaveChangesAsync());
}
public static async Task <int> BulkMergeAsync <TEntity>(
this IQueryable <TEntity> queryable,
IEnumerable <TEntity> entities,
Func <TEntity, object> updateColumnsToExlcude = null,
BulkMergeNotMatchedBehavior notMatchedBehavior = BulkMergeNotMatchedBehavior.DoNothing,
Expression <Func <TEntity> > whenNotMatched = null)
where TEntity : class
{
var current = entities?.ToList() ?? new List <TEntity>();
if (current.Count == 0)
{
return(0);
}
whenNotMatched?.EnsureMemberInitExpression();
return(await queryable
.BuildBulkExecutor()
.BulkMergeAsync(queryable, current, updateColumnsToExlcude, notMatchedBehavior, whenNotMatched));
}
public async Task <int> BulkMergeAsync(
IQueryable <TEntity> queryable,
List <TEntity> current,
Func <TEntity, object> updateColumnsToExclude = null,
BulkMergeNotMatchedBehavior notMatchedBehavior = BulkMergeNotMatchedBehavior.DoNothing,
Expression <Func <TEntity> > whenNotMatched = null)
{
var records = current.Select(entity =>
{
return($@"
({string.Join(", ", PropertyMappings.Select(m => $"{m.GetDbValue(entity)}"))})");
});
var qrySql = queryable.ToSql(out IReadOnlyList <SqlParameter> parameters);
Func <RelationalPropertyMapping, bool> updatePropertyPredicate = p => !p.IsGenerated;
string updateColumnSql = string.Empty;
var defaultConstructor = typeof(TEntity).GetConstructor(Type.EmptyTypes);
if (defaultConstructor != null && updateColumnsToExclude != null)
{
var instance = Activator.CreateInstance <TEntity>();
var properties = updateColumnsToExclude(instance).GetType().GetProperties().Select(x => x.Name).ToList();
updatePropertyPredicate = p => !p.IsGenerated && !properties.Contains(p.PropertyName);
}
updateColumnSql = string.Join(",", PropertyMappings.Where(updatePropertyPredicate).Select(p => $@"
TARGET.{p.ColumnName} = SOURCE.{p.ColumnName}"));
var insertColumnSql = string.Join(",", PropertyMappings.Where(p => !p.IsGenerated).Select(p => $@"
{p.ColumnName}"));
var insertSourceColumnSql = string.Join(",", PropertyMappings.Where(p => !p.IsGenerated).Select(p => $@"
SOURCE.{p.ColumnName}"));
var notMatchedSelector = string.Empty;
var notMatchedAction = string.Empty;
switch (notMatchedBehavior)
{
case BulkMergeNotMatchedBehavior.Delete:
notMatchedAction = "DELETE";
notMatchedSelector = "WHEN NOT MATCHED BY SOURCE THEN";
break;
case BulkMergeNotMatchedBehavior.Update when whenNotMatched != null:
var notMatchedVals = whenNotMatched.Compile().Invoke();
var propNames = whenNotMatched.GetSetPropertyNames();
if (propNames.Count == 0)
{
break;
}
var propertyMappings = PropertyMappings.Where(x => !x.IsGenerated && propNames.Contains(x.PropertyName));
var notMatchedUpdateColumnSql = string.Join(",", propertyMappings.Select(p => $@"
TARGET.{p.ColumnName} = {p.GetDbValue(notMatchedVals)}"));
notMatchedAction = $"UPDATE SET {notMatchedUpdateColumnSql}";
notMatchedSelector = "WHEN NOT MATCHED BY SOURCE THEN";
break;
}
var sqlCmd = $@"
DECLARE @merge TABLE ({string.Join(", ", PropertyMappings.Select(m => $"{m.ColumnName} {m.ColumnType}"))})
SET NOCOUNT ON
{ CreateBatchInsertStatement($"INSERT INTO @merge VALUES", records) }
SET NOCOUNT OFF
;
WITH tgt_cte AS (
{qrySql}
)
MERGE tgt_cte AS TARGET
USING @merge AS SOURCE ON (
{string.Join(" AND ", PrimaryKey.Keys.Select(k => $@"TARGET.{k.ColumnName} = SOURCE.{k.ColumnName}"))}
)
WHEN MATCHED THEN
UPDATE SET {updateColumnSql}
WHEN NOT MATCHED BY TARGET THEN
INSERT ({insertColumnSql})
VALUES ({insertSourceColumnSql})
{notMatchedSelector}
{notMatchedAction}
OUTPUT
$action AS action, {string.Join(",", PrimaryKey.Keys.Select(k => $@"
inserted.{k.ColumnName}"))};";
return(await ExecuteSqlCommandAsync(sqlCmd, current, parameters.Select(p => new SqlParameter(p.ParameterName, p.Value))));
}
public async Task MergesInMem(BulkMergeNotMatchedBehavior notMatchedBehavior)
{
List <EfCoreTest> entities;
using (var db = new woodmanContext(InMemDbOpts))
{
entities = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.ToListAsync();
}
const int numUpdate = 5;
var toMerge = entities
.Take(numUpdate)
.Select(x =>
{
x.ModifiedDate = DateTime.UtcNow.AddDays(x.Id);
return(x);
})
.ToList();
var numUnmatched = entities.Count - toMerge.Count;
var numAdd = 2;
for (var i = 0; i < numAdd; i++)
{
var inlineInterval = ((int)notMatchedBehavior + 1) * 4;
toMerge.Add(new EfCoreTest
{
Id = InMemId - i - 1 - inlineInterval,
Name = $"{nameof(BulkMergeTests)}_insert_{i}",
ModifiedDate = DateTime.Now,
CreatedDate = DateTime.Now
});
}
var expectedRecordsAffected = notMatchedBehavior == BulkMergeNotMatchedBehavior.DoNothing
? numUpdate + numAdd
: numUpdate + numUnmatched + numAdd;
using (var db = new woodmanContext(InMemDbOpts))
{
var numRowsAffected = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.BulkMergeAsync(
toMerge,
notMatchedBehavior: notMatchedBehavior,
whenNotMatched: () => new EfCoreTest {
ModifiedDate = DateTime.UtcNow
});
try
{
Assert.Equal(expectedRecordsAffected, numRowsAffected);
}
catch
{
throw;
}
var dbCount = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.CountAsync();
if (notMatchedBehavior == BulkMergeNotMatchedBehavior.Delete)
{
Assert.Equal(toMerge.Count, dbCount);
}
else
{
Assert.NotEqual(toMerge.Count, dbCount);
}
foreach (var m in toMerge)
{
var dbEntity = await db.EfCoreTest.FindAsync(m.Id);
Assert.NotNull(dbEntity);
Assert.Equal(m.Name, dbEntity.Name);
Assert.Equal(m.ModifiedDate.ToString("G"), dbEntity.ModifiedDate.ToString("G"));
}
}
}
public async Task MergesSql(BulkMergeNotMatchedBehavior notMatchedBehavior)
{
List <EfCoreTest> entities;
var dontDelete = new EfCoreTest
{
Name = "dont delete",
CreatedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
};
using (var db = new woodmanContext())
{
entities = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.ToListAsync();
var added = await db.AddAsync(dontDelete);
await db.SaveChangesAsync();
dontDelete.Id = added.Entity.Id;
}
const int numUpdate = 5;
var toMerge = entities
.Take(numUpdate)
.Select(x =>
{
x.ModifiedDate = DateTime.UtcNow.AddDays(x.Id);
return(x);
})
.ToList();
var numUnmatched = entities.Count - toMerge.Count;
var numAdd = 2;
for (var i = 0; i < numAdd; i++)
{
toMerge.Add(new EfCoreTest
{
Name = $"{nameof(BulkMergeTests)}_insert_{i}",
ModifiedDate = DateTime.Now,
CreatedDate = DateTime.Now
});
}
var expectedRecordsAffected = notMatchedBehavior == BulkMergeNotMatchedBehavior.DoNothing
? numUpdate + numAdd
: numUpdate + numUnmatched + numAdd;
using (var db = new woodmanContext())
{
var numRowsAffected = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.BulkMergeAsync(toMerge,
notMatchedBehavior: notMatchedBehavior,
whenNotMatched: () => new EfCoreTest {
Name = $"{nameof(BulkMergeTests)}__Archived__"
});
Assert.Equal(expectedRecordsAffected, numRowsAffected);
var dbCount = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.CountAsync();
if (notMatchedBehavior == BulkMergeNotMatchedBehavior.Delete)
{
Assert.Equal(toMerge.Count, dbCount);
}
else
{
Assert.NotEqual(toMerge.Count, dbCount);
}
foreach (var m in toMerge)
{
var dbEntity = await db.EfCoreTest.FindAsync(m.Id);
Assert.NotNull(dbEntity);
Assert.Equal(m.Name, dbEntity.Name);
Assert.Equal(m.ModifiedDate.ToString("G"), dbEntity.ModifiedDate.ToString("G"));
Assert.Equal(m.CreatedDate.ToString("G"), dbEntity.CreatedDate.ToString("G"));
}
var dontDeleteEntity = await db.EfCoreTest.FindAsync(dontDelete.Id);
Assert.NotNull(dontDeleteEntity);
}
}
public async Task <int> BulkMergeAsync(
IQueryable <TEntity> queryable,
List <TEntity> current,
Func <TEntity, object> updateColumnsToExlcude = null,
BulkMergeNotMatchedBehavior notMatchedBehavior = BulkMergeNotMatchedBehavior.DoNothing,
Expression <Func <TEntity> > whenNotMatched = null)
{
var tableVar = $"_m_{typeof(TEntity).Name}";
var deltaSql = string.Join(",", current.Select(entity =>
{
return($@"
({string.Join(", ", PropertyMappings.Select(m => $"{m.GetDbValue(entity)}"))})");
}));
var qrySql = queryable.ToSql(out IReadOnlyList <NpgsqlParameter> parameters);
Func <RelationalPropertyMapping, bool> updatePropertyPredicate = p => !p.IsGenerated;
var defaultConstructor = typeof(TEntity).GetConstructor(Type.EmptyTypes);
if (defaultConstructor != null && updateColumnsToExlcude != null)
{
var instance = Activator.CreateInstance <TEntity>();
var properties = updateColumnsToExlcude(instance).GetType().GetProperties().Select(x => x.Name).ToList();
updatePropertyPredicate = p => !p.IsGenerated && properties.Contains(p.PropertyName);
}
var upsertProps = PropertyMappings.Where(updatePropertyPredicate).ToList();
var insertColumnSql = $@"{string.Join(",", upsertProps.Select(p => $@"
{p.ColumnName}"))}";
var insertSelectColumnSql = $@"{string.Join(",", upsertProps.Select(p => $@"
i_m.{p.ColumnName}"))}";
var notMatchedSql = string.Empty;
if (notMatchedBehavior == BulkMergeNotMatchedBehavior.Delete)
{
notMatchedSql = $@"
WITH _out AS
(
DELETE FROM {TableName} d
USING (
{qrySql}
) AS d_q
LEFT JOIN {tableVar} AS d_m ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_q.{k.ColumnName} = d_m.{k.ColumnName}"))}
WHERE {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_q.{k.ColumnName} = d.{k.ColumnName}"))}
AND {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_m.{k.ColumnName} IS NULL"))}
RETURNING {string.Join(", ", PropertyMappings.Select(m => $"0 as {m.ColumnName}"))}, '{MergeActions.Delete}' AS action
)
INSERT INTO _m_results SELECT {string.Join(", ", PrimaryKey.Keys.Select(m => m.ColumnName))}, action FROM _out;";
}
else if (notMatchedBehavior == BulkMergeNotMatchedBehavior.Update && whenNotMatched != null)
{
var props = whenNotMatched.GetSetPropertyNames();
var notMatchedEntity = whenNotMatched.Compile().Invoke();
var notMatchedProps = PropertyMappings.Where(x => !x.IsGenerated && props.Contains(x.PropertyName));
if (props.Count > 0)
{
notMatchedSql = $@"
WITH _out AS
(
UPDATE {TableName} AS d
SET
{string.Join(",", notMatchedProps.Select(prop => $@"
{prop.ColumnName} = {prop.GetDbValue(notMatchedEntity)}"))}
FROM (
{qrySql}
) AS d_q
LEFT JOIN {tableVar} AS d_m ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_q.{k.ColumnName} = d_m.{k.ColumnName}"))}
WHERE {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_q.{k.ColumnName} = d.{k.ColumnName}"))}
AND {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" d_m.{k.ColumnName} IS NULL"))}
RETURNING {string.Join(", ", PropertyMappings.Select(m => $"0 as {m.ColumnName}"))}, '{MergeActions.Update}' as action
)
INSERT INTO _m_results SELECT {string.Join(", ", PrimaryKey.Keys.Select(m => m.ColumnName))}, action FROM _out;";
}
}
var sqlCmd = $@"
CREATE TEMP TABLE {tableVar} ({string.Join(", ", PropertyMappings.Select(m => $"{m.ColumnName} {m.ColumnType}"))});
CREATE TEMP TABLE _m_results ({string.Join(", ", PrimaryKey.Keys.Select(k => $"{k.ColumnName} {k.ColumnType}"))}, action varchar(10)) ;
INSERT INTO {tableVar} VALUES
{deltaSql};
{notMatchedSql}
WITH _out AS
(
UPDATE {TableName} AS u
SET
{string.Join(",", upsertProps.Select(prop => $@"
{prop.ColumnName} = u_m.{prop.ColumnName}"))}
FROM (
{qrySql}
) as u_q
JOIN {tableVar} u_m ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" u_m.{k.ColumnName} = u_q.{k.ColumnName}"))}
WHERE {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" u_q.{k.ColumnName} = u.{k.ColumnName}"))}
RETURNING {string.Join(", ", PropertyMappings.Select(m => $"0 as {m.ColumnName}"))}, '{MergeActions.Update}' as action
)
INSERT INTO _m_results SELECT {string.Join(", ", PrimaryKey.Keys.Select(m => m.ColumnName))}, action FROM _out;
WITH _out AS
(
INSERT INTO {TableName}
({insertColumnSql})
SELECT
{insertSelectColumnSql}
FROM {tableVar} i_m
LEFT JOIN {TableName} q_m ON {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" q_m.{k.ColumnName} = i_m.{k.ColumnName}"))}
WHERE {string.Join(@"
AND", PrimaryKey.Keys.Select(k => $@" q_m.{k.ColumnName} IS NULL"))}
RETURNING {string.Join(", ", PropertyMappings.Select(m => $"{TableName}.{m.ColumnName} as {m.ColumnName}"))}, '{MergeActions.Insert}' as action
)
INSERT INTO _m_results SELECT {string.Join(", ", PrimaryKey.Keys.Select(m => m.ColumnName))}, action FROM _out;
SELECT action, {string.Join(", ", PrimaryKey.Keys.Select(m => m.ColumnName))} FROM _m_results;";
return(await ExecuteSqlCommandAsync(sqlCmd, current, parameters.Select(p => new NpgsqlParameter(p.ParameterName, p.Value))));
}
