static void TestBulkWhere()
{
var context = new BulkContext("DefaultConnection");
var baseQuery = context.Addresses2
.Select(x => new { StreetName = x.StreetName, x.HouseNumber, x.CreatedAt });
var queries = baseQuery.Take(50000).Select(x => new { StreetName = x.StreetName, x.HouseNumber }).ToList();
// Filter by unique subcollections
var sw = Stopwatch.StartNew();
var uniqueStreetname = queries.Select(x => x.StreetName).Distinct().ToList();
var uniqueHouseNumbers = queries.Select(x => x.HouseNumber).Distinct().ToList();
var result = baseQuery.Where(x => uniqueStreetname.Contains(x.StreetName) &&
uniqueHouseNumbers.Contains(x.HouseNumber)).ToList();
result = (from r in result
join q in queries on new { r.StreetName, r.HouseNumber } equals new { q.StreetName, q.HouseNumber }
select r).ToList();
sw.Stop();
Console.WriteLine($"Filter by unique collections method extracted {result.Count} records for {sw.Elapsed }");
Console.WriteLine();
// Union approach
sw = Stopwatch.StartNew();
var currentQuery = queries[0];
var query = baseQuery.Where(x => x.StreetName == currentQuery.StreetName &&
x.HouseNumber == currentQuery.HouseNumber);
for (var i = 1; i < 100; i++)
{
var cq = queries[i];
query = query.Union(baseQuery.Where(x => x.StreetName == cq.StreetName &&
x.HouseNumber == cq.HouseNumber));
}
result = query.ToList();
sw.Stop();
Console.WriteLine($"Union extracted {result.Count} records for {sw.Elapsed }");
Console.WriteLine();
// Bulk Select approach
sw = Stopwatch.StartNew();
result = baseQuery.BulkSelect(x => new { x.StreetName, x.HouseNumber }, queries);
sw.Stop();
Console.WriteLine($"BulkSelect extracted {result.Count} records for {sw.Elapsed }");
Console.WriteLine();
// Bulk Select (Contains) approach
sw = Stopwatch.StartNew();
result = baseQuery.BulkSelect(x => new { x.HouseNumber },
queries.Select(x => new { x.HouseNumber }).Distinct().ToList());
sw.Stop();
Console.WriteLine($"BulkSelect (Contains) extracted {result.Count} records for {sw.Elapsed }");
Console.WriteLine();
}
static async Task TestAsync(BulkContext context, NpgsqlBulkUploader uploader, List <Address> data)
{
Console.WriteLine("");
Console.WriteLine("ASYNC version...");
Console.WriteLine("");
var sw = Stopwatch.StartNew();
await uploader.InsertAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
Trace.Assert(context.Addresses.Count() == data.Count);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
await uploader.UpdateAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
await uploader.ImportAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
await uploader.InsertAsync(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
await uploader.UpdateAsync(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
// partial update 1
sw = Stopwatch.StartNew();
data.ForEach(x => x.StreetName = x.StreetName + " upd");
await uploader.UpdateAsync(data, x => x.StreetName);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (partial 1) Async");
}
private static void CompareValues(List<Address> data, BulkContext bulkContext)
{
var dbData = bulkContext.Addresses.OrderBy(x => x.AddressId).ToArray();
var local = data.OrderBy(x => x.AddressId).ToArray();
for (var i = 0; i < dbData.Length; i++)
{
var dbItem = JsonConvert.SerializeObject(dbData[i]);
var locItem = JsonConvert.SerializeObject(local[i]);
if (dbItem != locItem)
{
Console.WriteLine("Oops");
}
}
}
static void TestDerived(BulkContext context)
{
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2EF()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
LocalizedName = streets[i % streets.Length],
Index2 = i
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Derived: dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// Trace.Assert(context.Addresses.Count() == data.Count);
uploader.Insert(data.Take(100), InsertConflictAction.UpdateProperty<Address2EF>(
x => x.AddressId, x => x.PostalCode));
uploader.Insert(data.Take(100), InsertConflictAction.UpdateProperty<Address2EF>(
x => x.AddressId, x => x.Index2));
Console.WriteLine($"Derived: derived objects are inserted");
var data2 = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2EF()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
LocalizedName = streets[i % streets.Length],
Index2 = i
}).ToList();
uploader.Update(data2);
}
static void TestInsertPartialUpdateAndIndsert(BulkContext context, List <Address> data)
{
Console.WriteLine("");
Console.WriteLine("TestInsertPartialUpdateAndIndsert...");
Console.WriteLine("");
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var uploader = new NpgsqlBulkUploader(context);
uploader.Insert(data.Take(1000));
uploader.Update(data.Take(1000), x => x.Duration, x => x.ExtraHouseNumber);
uploader.Insert(data.Skip(1000).Take(1000));
}
static void TestInheritanceCase()
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var context = new BulkContext("DefaultConnection");
context.Database.ExecuteSqlCommand("DELETE FROM addresses2");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
LocalizedName = streets[i % streets.Length],
Index1 = i,
Index2 = i
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// checking consitency
foreach (var addr in data)
{
if (addr.Index1 != addr.Index2)
{
Console.WriteLine($"INCONSITENT! Id: {addr.AddressId}/{addr.Address2Id}, {addr.Index1} != {addr.Index2}");
}
}
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
}
static void TestPlainCase()
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var context = new BulkContext("DefaultConnection");
context.Database.ExecuteSqlCommand("TRUNCATE addresses cascade");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
var sw = Stopwatch.StartNew();
HardcodedInsert(data, context);
sw.Stop();
Console.WriteLine($"Hardcoded solution inserted {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
}
static void Main(string[] args)
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var optionsBuilder = new DbContextOptionsBuilder <BulkContext>();
optionsBuilder.UseNpgsql("server=localhost;user id=postgres;password=qwerty;database=copy");
var context = new BulkContext(optionsBuilder.Options);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address2()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
Console.ReadLine();
}
static void Main(string[] args)
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var optionsBuilder = new DbContextOptionsBuilder <BulkContext>();
optionsBuilder.UseNpgsql(Configuration.ConnectionString);
var context = new BulkContext(optionsBuilder.Options);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange <DateTime>(DateTime.Now, DateTime.Now)
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Attach(data[0]);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
Trace.Assert(context.Addresses.Count() == data.Count);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
TestViaInterfaceCase(data, context);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
Console.ReadLine();
}
public RegularInserter(BulkContext context)
{
this._context = context;
}
static void Main(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder<BulkContext>();
optionsBuilder.UseNpgsql(Configuration.ConnectionString);
var context = new BulkContext(optionsBuilder.Options);
context.Database.Migrate();
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Duration = new NpgsqlTypes.NpgsqlRange<DateTime>(DateTime.Now, DateTime.Now),
AddressType = i % 2 == 0 ? AddressType.Type1 : AddressType.Type2,
AddressTypeInt = i % 2 == 0 ? AddressTypeInt.First : AddressTypeInt.Second,
UnmappedEnum = i % 2 == 0 ? UnmappedEnum.A : UnmappedEnum.B,
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Attach(data[0]);
data[0].AddressId = 11;
data[1].CreatedAt = DateTime.Now;
//context.Add(data[0]);
//context.Add(data[1]);
//context.SaveChanges();
// data.ForEach(x => x.StreetName = null);
var sw = Stopwatch.StartNew();
uploader.Insert(data);
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
// CompareValues(data, new BulkContext(optionsBuilder.Options));
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
TestViaInterfaceCase(data, context);
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlRaw("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
// Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
TestDerived(context);
Console.WriteLine();
Console.WriteLine("Time to press any key...");
Console.ReadLine();
}
public BulkInserter(BulkContext context)
{
this._context = context;
}
static void TestPlainCase()
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var addressTypes = new AddressType?[] { null, AddressType.Type1, AddressType.Type2 };
var dates = new DateTime?[] { null, DateTime.Now };
var guids = new Guid?[] { null, Guid.Empty };
var decimals = new decimal?[] { null, decimal.Zero };
var context = new BulkContext("DefaultConnection");
context.Database.ExecuteSqlCommand("TRUNCATE addresses cascade");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Type = addressTypes[i % addressTypes.Length],
Date = dates[i % dates.Length],
Guid = guids[i % guids.Length],
Dec = decimals[i % decimals.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
var sw = Stopwatch.StartNew();
HardcodedInsert(data, context);
sw.Stop();
Console.WriteLine($"Hardcoded solution inserted {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
sw = Stopwatch.StartNew();
uploader.Insert(data, InsertConflictAction.UpdateProperty <Address>(x => x.AddressId, x => x.Dec));
uploader.Insert(data, InsertConflictAction.DoNothing());
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
TestViaInterfaceCase(data, context);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
}