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); }
public async Task Insert_RecordIsInserted() { const string streetName = "Just inserted street name"; await DeleteIfExistsByName(streetName); var address = new Address { StreetName = streetName, HouseNumber = 320, PostalCode = "Some postal code", ExtraHouseNumber = 1456, Duration = new NpgsqlTypes.NpgsqlRange <DateTime>(DateTime.Now, DateTime.Now) }; var bulkImport = new NpgsqlBulkUploader(CreateContext()); bulkImport.Insert(new[] { address }); var assertContext = CreateContext(); var actualAddress = await assertContext.Addresses .SingleAsync(x => x.StreetName == streetName); using (assertContext) { actualAddress.StreetName.Should().Be(address.StreetName); actualAddress.HouseNumber.Should().Be(address.HouseNumber); actualAddress.PostalCode.Should().Be(address.PostalCode); actualAddress.ExtraHouseNumber.Should().Be(address.ExtraHouseNumber); actualAddress.Duration.ToString().Should().Be(address.Duration.ToString()); } }
public static void BulkInsert <TEntity>(this DbContext dbContext, IEnumerable <TEntity> entities) where TEntity : Entity { var bulk = new NpgsqlBulkUploader(dbContext); bulk.Insert(entities); }
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)); }
public override void Save(IList <RowData> rowsData) { using (EventLogContext _context = EventLogContext.Create(_databaseOptions, _databaseActions)) { if (_maxPeriodRowData == DateTime.MinValue) { _maxPeriodRowData = _context.GetRowsDataMaxPeriod(_system); } List <Database.Models.RowData> newEntities = new List <Database.Models.RowData>(); foreach (var itemRow in rowsData) { if (itemRow == null) { continue; } if (_maxPeriodRowData != DateTime.MinValue && itemRow.Period.DateTime <= _maxPeriodRowData) { if (_context.RowDataExistOnDatabase(_system, itemRow)) { continue; } } newEntities.Add(new Database.Models.RowData(_system, itemRow, _referencesCache)); } var bulkUploader = new NpgsqlBulkUploader(_context); bulkUploader.Insert(newEntities); } }
static void TestViaInterfaceCase <T>(IEnumerable <T> data, DbContext context) where T : IHasId { var uploader = new NpgsqlBulkUploader(context); var properties = data .First() .GetType() .GetProperties() .Where(x => x.GetCustomAttribute <ColumnAttribute>() != null) .ToArray(); uploader.Insert(data, InsertConflictAction.UpdateProperty <T>(x => x.AddressId, properties)); }
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(); }
static BenchmarkContext CreateContextAndSeed() { var ctx = CreateContext(); if (!ctx.Prices.Any()) { ctx.Database.SetCommandTimeout(TimeSpan.FromHours(1)); Console.WriteLine("Seeding.. This will take some TIME! Better not to interrupt this process.."); var securitesCount = 4_000; var priceSources = new List <PriceSource>() { new PriceSource() { PriceSourceId = 1, Name = "Default" }, new PriceSource() { PriceSourceId = 2, Name = "ThirdParty" }, new PriceSource() { PriceSourceId = 3, Name = "User" }, new PriceSource() { PriceSourceId = 4, Name = "Counterparty" } }; var securities = new List <Security>(); for (var i = 1; i <= securitesCount; i++) { securities.Add(new Security() { SecurityId = i, Ticker = $"Ticker_{i}", Description = $"Security of ticker_{i}" }); } var uploader = new NpgsqlBulkUploader(ctx); uploader.Insert(priceSources); uploader.Insert(securities); var rnd = new Random(); var prices = new List <Price>(); var tradedOn = new DateTime(2018, 01, 01); var tradedOnMin = new DateTime(1988, 01, 01); for (var i = 1; i <= 10_000_000; i++) { prices.Add(new Price() { PriceId = i, PriceSourceId = (i % priceSources.Count) + 1, TradedOn = tradedOn, SecurityId = (i % securitesCount) + 1, ClosePrice = 100 + rnd.Next(20), OpenPrice = 100 + rnd.Next(20) }); tradedOn = tradedOn.AddDays(-1); if (tradedOn < tradedOnMin) { tradedOn = new DateTime(2018, 01, 01); } if (prices.Count % 10_000 == 0) { uploader.Insert(prices); prices.Clear(); } } uploader.Insert(prices); prices.Clear(); Console.WriteLine("Seeding is finished.."); } return(ctx); }
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(); }