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(); }