Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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");
        }
Exemplo n.º 3
0
        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");
                }
            }
        }
Exemplo n.º 4
0
        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);

        }
Exemplo n.º 5
0
        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));
        }
Exemplo n.º 6
0
        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 }");
        }
Exemplo n.º 7
0
        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 }");
        }
Exemplo n.º 8
0
        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();
        }
Exemplo n.º 9
0
        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;
 }
Exemplo n.º 11
0
        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;
 }
Exemplo n.º 13
0
        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();
        }