public async Task RemovesSqlComposite()
{
var toDelete = SqlCompositeIds.Take(5).ToList();
using (var db = new woodmanContext())
{
var entities = await db.EfCoreTestChild
.Join(toDelete)
.ToListAsync();
Assert.NotEmpty(entities);
}
using (var db = new woodmanContext())
{
var result = await db.EfCoreTestChild
.Where(e => e.Name.Contains(nameof(BulkRemoveTests)))
.BulkRemoveAsync(toDelete);
Assert.Equal(5, result);
}
using (var db = new woodmanContext())
{
var entities = await db.EfCoreTestChild
.Join(toDelete)
.ToListAsync();
Assert.Empty(entities);
}
}
public async Task RemovesWithoutKeysSql()
{
var toDelete = SqlIds.Skip(5).Take(5).ToList();
using (var db = new woodmanContext())
{
var entities = await db.EfCoreTest
.Where(e => toDelete.Contains(e.Id))
.ToListAsync();
Assert.NotEmpty(entities);
}
using (var db = new woodmanContext())
{
var result = await db.EfCoreTest
.Where(e => toDelete.Contains(e.Id))
.BulkRemoveAsync();
Assert.Equal(5, result);
}
using (var db = new woodmanContext())
{
var entities = await db.EfCoreTest
.Where(e => toDelete.Contains(e.Id))
.ToListAsync();
Assert.Empty(entities);
}
}
public async Task RemovesInMem()
{
var toDelete = InMemIds.Take(5).ToList();
using (var db = new woodmanContext(InMemDbOpts))
{
var entities = await db.EfCoreTest
.Where(e => toDelete.Contains(e.Id))
.ToListAsync();
Assert.NotEmpty(entities);
}
using (var db = new woodmanContext(InMemDbOpts))
{
var result = await db.EfCoreTest
.Where(e => e.Name.Contains(nameof(BulkRemoveTests)))
.BulkRemoveAsync(toDelete);
Assert.Equal(5, result);
}
using (var db = new woodmanContext(InMemDbOpts))
{
var entities = await db.EfCoreTest
.Where(e => toDelete.Contains(e.Id))
.ToListAsync();
Assert.Empty(entities);
}
}
public async Task UpdatesInMem()
{
var toUpdate = InMemIds.Take(2)
.ToDictionary(key => key, value => DateTime.UtcNow.AddDays(value));
using (var db = new woodmanContext(InMemDbOpts))
{
var result = await db.EfCoreTest.BulkUpdateAsync(toUpdate.Select(x => x.Key), id => new EfCoreTest
{
ModifiedDate = toUpdate[id]
});
Assert.Equal(toUpdate.Count, result);
}
using (var db = new woodmanContext(InMemDbOpts))
{
var updated = await db.EfCoreTest
.Where(x => toUpdate.Select(y => y.Key).Contains(x.Id))
.ToListAsync();
Assert.Equal(toUpdate.Count, updated.Count);
foreach (var u in updated)
{
Assert.Contains(u.Id, toUpdate.Select(x => x.Key));
var expected = toUpdate[u.Id];
var actual = u.ModifiedDate;
Assert.Equal(expected.ToString("G"), actual.ToString("G"));
}
}
}
public async Task CreatesInMem()
{
var toCreate = new List <EfCoreTest>();
for (var i = 0; i < 10; i++)
{
toCreate.Add(new EfCoreTest
{
Id = i + 1,
Name = i == 0 ? null : $"{nameof(BulkAddTests)}_{i}",
CreatedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
});
}
using (var db = new woodmanContext(InMemDbOpts))
{
await db.EfCoreTest.BulkAddAsync(toCreate);
foreach (var e in toCreate)
{
var added = await db.EfCoreTest.FindAsync(e.Id);
Assert.NotNull(added);
Assert.Equal(e.Name, added.Name);
}
}
}
public async Task MergesNonValueTypeSqlComposite()
{
List <EfCoreTestNonValueTypeKeys> entities;
using (var db = new woodmanContext())
{
entities = await db.EfCoreTestNonValueTypeKeys
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.ToListAsync();
}
const int numUpdate = 5;
var toMerge = entities
.Take(numUpdate)
.Select(x =>
{
x.ModifiedDate = DateTime.UtcNow.AddDays(10);
x.IsArchived = true;
return(x);
})
.ToList();
var numDelete = entities.Count - toMerge.Count;
var numAdd = 2;
for (var i = 0; i < numAdd; i++)
{
toMerge.Add(new EfCoreTestNonValueTypeKeys
{
Id = toMerge[i].Id,
Tier2Id = Guid.NewGuid().ToString(),
Tier1Id = Guid.NewGuid().ToString(),
Name = $"{nameof(BulkMergeTests)}_insert_{i}",
ModifiedDate = DateTime.Now,
CreatedDate = DateTime.Now
});
}
var expectedRecordsAffected = numUpdate + numDelete + numAdd;
using (var db = new woodmanContext())
{
var numRowsAffected = await db.EfCoreTestNonValueTypeKeys
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.BulkMergeAsync(toMerge, e => new
{
e.CreatedDate
}, BulkMergeNotMatchedBehavior.DoNothing);
Assert.Equal(toMerge.Count, numRowsAffected);
}
}
private static void Init()
{
using (var db = new woodmanContext())
{
var childEntities = db.EfCoreTestChild
.Where(e => e.Name == null || e.Name.Contains(nameof(BulkAddTests)))
.ToList();
if (childEntities.Count > 0)
{
db.RemoveRange(childEntities);
db.SaveChanges();
}
var entities = db.EfCoreTest
.Where(e => e.Name == null || e.Name.Contains(nameof(BulkAddTests)))
.ToList();
if (entities.Count > 0)
{
db.RemoveRange(entities);
db.SaveChanges();
}
}
using (var db = new postgresContext())
{
var childEntities = db.Efcoretestchild
.Where(e => e.Name == null || e.Name.Contains(nameof(BulkAddTests)))
.ToList();
if (childEntities.Count > 0)
{
db.RemoveRange(childEntities);
db.SaveChanges();
}
var entities = db.Efcoretest
.Where(e => e.Name == null || e.Name.Contains(nameof(BulkAddTests)))
.ToList();
if (entities.Count > 0)
{
db.RemoveRange(entities);
db.SaveChanges();
}
}
}
public async Task UpdatesSqlCompositeNonValueType()
{
using (var db = new woodmanContext())
{
var toUpdate = await db.EfCoreTestNonValueTypeKeys.Take(2).ToListAsync();
var keys = toUpdate.Select(x => new object[] { x.Id, x.Tier1Id, x.Tier2Id });
var result = await db.EfCoreTestNonValueTypeKeys.BulkUpdateAsync(keys, id => new EfCoreTestNonValueTypeKeys
{
ModifiedDate = DateTime.UtcNow
});
Assert.Equal(toUpdate.Count, result);
}
}
private static void Init()
{
using (var db = new woodmanContext())
{
var entities = db.EfCoreTest.Where(e => e.Name.Contains(nameof(BulkTransactionTests))).ToList();
if (entities.Count > 0)
{
db.RemoveRange(entities);
}
for (var i = 0; i < InitialCount; i++)
{
db.Add(new EfCoreTest
{
Name = nameof(BulkTransactionTests),
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now
});
}
db.SaveChanges();
}
using (var db = new postgresContext())
{
var entities = db.Efcoretest.Where(e => e.Name.Contains(nameof(BulkTransactionTests))).ToList();
if (entities.Count > 0)
{
db.RemoveRange(entities);
}
for (var i = 0; i < InitialCount; i++)
{
db.Add(new Efcoretest
{
Name = nameof(BulkTransactionTests),
Createddate = DateTime.Now,
Modifieddate = DateTime.Now
});
}
db.SaveChanges();
}
}
public async Task CreatesInMemComposite()
{
var parent = new EfCoreTest
{
Id = 99999,
Name = $"{nameof(BulkAddTests)}_Composite",
CreatedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
};
using (var db = new woodmanContext(InMemDbOpts))
{
await db.AddAsync(parent);
await db.SaveChangesAsync();
}
var toCreate = new List <EfCoreTestChild>();
for (var i = 0; i < 10; i++)
{
toCreate.Add(new EfCoreTestChild
{
Id = i + 1,
EfCoreTestId = parent.Id,
Name = i == 0 ? null : $"{nameof(BulkAddTests)}_{i}",
CreatedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
});
}
using (var db = new woodmanContext(InMemDbOpts))
{
await db.EfCoreTestChild.BulkAddAsync(toCreate);
foreach (var e in toCreate)
{
var added = await db.EfCoreTestChild.FindAsync(e.Id, e.EfCoreTestId);
Assert.NotNull(added);
Assert.Equal(e.Name, added.Name);
}
}
}
protected virtual void InitiEfCoreTestNonValueTypeKeys(woodmanContext db, int iteration)
{
var name = $"{Name}_{iteration}";
var existing = db.EfCoreTestNonValueTypeKeys
.Where(e => e.Name == name)
.FirstOrDefault();
if (existing == null)
{
var inserted = db.Add(new EfCoreTestNonValueTypeKeys
{
Tier1Id = Guid.NewGuid().ToString(),
Tier2Id = Guid.NewGuid().ToString(),
Name = name,
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now,
IsArchived = false
});
db.SaveChanges();
}
}
public async Task JoinsByCompositeKeySql()
{
var expectedIds = SqlCompositeIds.Take(5).ToList();
List <object[]> actualIds;
using (var db = new woodmanContext())
{
actualIds = await db.EfCoreTestChild
.Join(expectedIds)
.Select(e => new object[] { e.Id, e.EfCoreTestId })
.ToListAsync();
}
Assert.Equal(expectedIds.Count, actualIds.Count);
foreach (var expected in expectedIds)
{
Assert.Contains(expected, actualIds);
}
}
public async Task JoinsByPrimaryKeyInMem()
{
var expectedIds = InMemIds.Take(5).ToList();
List <int> actualIds;
using (var db = new woodmanContext(InMemDbOpts))
{
actualIds = await db.EfCoreTest
.Join(expectedIds)
.Select(e => e.Id)
.ToListAsync();
}
Assert.Equal(expectedIds.Count, actualIds.Count);
foreach (var expected in expectedIds)
{
Assert.Contains(expected, actualIds);
}
}
public async Task SqlRollback()
{
using (var db = new woodmanContext())
using (var scope = db.Database.CurrentTransaction ?? db.Database.BeginTransaction())
{
var toAdd = new EfCoreTest
{
Name = $"{nameof(BulkTransactionTests)}_Added",
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now
};
await db.EfCoreTest.AddAsync(toAdd);
await db.SaveChangesAsync(false);
var ids = await db.EfCoreTest
.Where(x => x.Name.Contains(nameof(BulkTransactionTests)))
.Select(x => x.Id)
.ToListAsync();
await db.EfCoreTest
.Where(x => x.Name.Contains(nameof(BulkTransactionTests)))
.BulkRemoveAsync(ids);
scope.Rollback();
}
using (var db = new woodmanContext())
{
var count = await db.EfCoreTest
.Where(x => x.Name.Contains(nameof(BulkTransactionTests)))
.CountAsync();
Assert.Equal(InitialCount, count);
}
}
public async Task UpdatesWithoutKeysInMem()
{
var createdDate = DateTime.UtcNow;
var toUpdate = InMemIds.Take(2).ToList();
using (var db = new woodmanContext(InMemDbOpts))
{
var result = await db.EfCoreTest
.Where(x => toUpdate.Contains(x.Id))
.BulkUpdateAsync(() => new EfCoreTest
{
CreatedDate = createdDate
});
Assert.Equal(toUpdate.Count, result);
}
using (var db = new woodmanContext(InMemDbOpts))
{
var updated = await db.EfCoreTest
.Where(x => toUpdate.Contains(x.Id))
.ToListAsync();
Assert.Equal(toUpdate.Count, updated.Count);
foreach (var u in updated)
{
Assert.Contains(u.Id, toUpdate);
var expected = createdDate;
var actual = u.CreatedDate;
Assert.Equal(expected.ToString("G"), actual.ToString("G"));
}
}
}
public async Task UpdatesInMemComposite()
{
var toUpdate = InMemCompositeIds.Take(2)
.ToDictionary(key => key, value => DateTime.UtcNow.AddDays(3));
using (var db = new woodmanContext(InMemDbOpts))
{
var result = await db.EfCoreTestChild.BulkUpdateAsync(toUpdate.Select(x => x.Key), id => new EfCoreTestChild
{
ModifiedDate = toUpdate.First(u => Enumerable.SequenceEqual(id, u.Key)).Value
});
Assert.Equal(toUpdate.Count, result);
}
using (var db = new woodmanContext(InMemDbOpts))
{
var updated = await db.EfCoreTestChild
.Join(toUpdate.Select(y => y.Key))
.ToListAsync();
Assert.Equal(toUpdate.Count, updated.Count);
foreach (var u in updated)
{
var keyExists = toUpdate.Keys.Any(k => Enumerable.SequenceEqual(new object[] { u.Id, u.EfCoreTestId }, k));
Assert.True(keyExists);
var expected = toUpdate.First(x => Enumerable.SequenceEqual(new object[] { u.Id, u.EfCoreTestId }, x.Key)).Value;
var actual = u.ModifiedDate;
Assert.Equal(expected.ToString("G"), actual.ToString("G"));
}
}
}
public async Task MergesInMemComposite()
{
List <EfCoreTestChild> entities;
using (var db = new woodmanContext(InMemDbOpts))
{
entities = await db.EfCoreTestChild
.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 numDelete = entities.Count - toMerge.Count;
var numAdd = 2;
for (var i = 0; i < numAdd; i++)
{
toMerge.Add(new EfCoreTestChild
{
Id = 12341235 - i,
EfCoreTestId = toMerge[0].EfCoreTestId,
Name = $"{nameof(BulkMergeTests)}_insert_{i}",
ModifiedDate = DateTime.Now,
CreatedDate = DateTime.Now
});
}
var expectedRecordsAffected = numUpdate + numDelete + numAdd;
using (var db = new woodmanContext(InMemDbOpts))
{
var numRowsAffected = await db.EfCoreTestChild
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.BulkMergeAsync(toMerge, notMatchedBehavior: BulkMergeNotMatchedBehavior.Delete);
Assert.Equal(expectedRecordsAffected, numRowsAffected);
var dbCount = await db.EfCoreTestChild
.Where(e => e.Name.Contains(nameof(BulkMergeTests)))
.CountAsync();
Assert.Equal(toMerge.Count, dbCount);
foreach (var m in toMerge)
{
var dbEntity = await db.EfCoreTestChild.FindAsync(m.Id, m.EfCoreTestId);
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"));
}
}
}
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 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"));
}
}
}
protected virtual void InitSqlDb()
{
using (var db = new woodmanContext())
{
for (var i = 1; i <= 10; i++)
{
var name = $"{Name}_{i}";
int efCoreTestId;
InitiEfCoreTestNonValueTypeKeys(db, i);
var existing = db.EfCoreTest
.Where(e => e.Name == name)
.FirstOrDefault();
if (existing == null)
{
var inserted = db.Add(new EfCoreTest
{
Name = name,
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now
});
db.SaveChanges();
efCoreTestId = inserted.Entity.Id;
SqlIds.Add(inserted.Entity.Id);
}
else
{
efCoreTestId = existing.Id;
SqlIds.Add(existing.Id);
}
if (i > 5)
{
continue;
}
for (var j = 1; j <= 4; j++)
{
var childName = $"{Name}_{i}_{j}";
var existingChild = db.EfCoreTestChild
.Where(e => e.Name == childName)
.FirstOrDefault();
if (existingChild == null)
{
var inserted = db.Add(new EfCoreTestChild
{
EfCoreTestId = efCoreTestId,
Name = name,
CreatedDate = DateTime.Now,
ModifiedDate = DateTime.Now
});
db.SaveChanges();
SqlCompositeIds.Add(new object[] { inserted.Entity.Id, efCoreTestId });
}
else
{
SqlCompositeIds.Add(new object[] { existing.Id, efCoreTestId });
}
}
}
}
}
