public long UpdateAddressWithExecuteSqlInterpolated(AmazonCodeFirstDbContext amazonCodeFirstContext, Address address)
{
var updateAddressSql = GetUpdateAddressSql(address);
var stopwatch = new Stopwatch();
stopwatch.Start();
amazonCodeFirstContext.Database.ExecuteSqlInterpolated(updateAddressSql);
stopwatch.Stop();
return(stopwatch.ElapsedMilliseconds);
}
public long UpdateAddressWithDbSetWithAddRange(AmazonCodeFirstDbContext amazonCodeFirstContext, IEnumerable <Address> address)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
amazonCodeFirstContext.Address.UpdateRange(address);
amazonCodeFirstContext.SaveChanges();
stopwatch.Stop();
return(stopwatch.ElapsedMilliseconds);
}
public long UpdateAddressWithDbContext(AmazonCodeFirstDbContext amazonCodeFirstContext, Address address)
{
var stopwatch = new Stopwatch();
stopwatch.Start();
amazonCodeFirstContext.Update(address);
amazonCodeFirstContext.SaveChanges();
stopwatch.Stop();
return(stopwatch.ElapsedMilliseconds);
}
public void TrySaveWithStateUnchanged(Customer customer)
{
// EntityState Unchanged — The entity exists in the database and hasn’t been modified on the client. SaveChanges ignores it.
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonCodeFirstContext.Add(customer);
// required set id to avoid exception below
// Either set a permanent value explicitly or ensure that the database is configured to generate values for this property.
customer.Id = 1;
amazonCodeFirstContext.Entry(customer).State = Microsoft.EntityFrameworkCore.EntityState.Unchanged;
amazonCodeFirstContext.SaveChanges();
}
}
public long UpdateAddressWithBulkOperation(IList <Address> addresses)
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var stopwatch = new Stopwatch();
stopwatch.Start();
amazonCodeFirstContext.BulkUpdate(addresses);
stopwatch.Stop();
return(stopwatch.ElapsedMilliseconds);
}
}
public void Save(Customer customer)
{
try
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonCodeFirstContext.Customer.Add(customer);
amazonCodeFirstContext.SaveChanges();
}
}
catch (Exception ex)
{
throw;
}
}
public IEnumerable <string> GetCustomersNamesWithDistinct()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = (from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
select new { customer.Name }).Distinct();
var data = query.ToList();
return(data.Select(d => d.Name));
}
}
public IEnumerable <Customer> GetCustomersWithAddressExemplifyingOneToOneRelationship()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
select new
{
customer, address
};
var data = query.ToList();
return(data.Select(d => d.customer));
}
}
public IEnumerable <KeyValuePair <int, DateTime> > GetCustomersIdsAndCartPurchaseDateWithGROUPBYMIN()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from cart in amazonCodeFirstContext.Cart
group cart by cart.CustomerId into groupingCart
select new KeyValuePair <int, DateTime>
(
groupingCart.Key,
groupingCart.Min(g => g.PurchaseDate)
);
var data = query.ToList();
return(data);
}
}
public int GetCustomerIdWithMIN()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
select new
{
customer, address, cart
};
var data = query.Min(x => x.customer.Id);
return(data);
}
}
public IEnumerable <Customer> GetCustomersWithAddressAndCartsBasedInInnerJoin()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
select new
{
customer, address, cart
};
var data = query.ToList();
return(data.Select(d => d.customer).Distinct());
}
}
public IEnumerable <KeyValuePair <int, int> > GetCartIdsAndQuantityItemsWithGROUPBYSUM()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from cart in amazonCodeFirstContext.Cart
join cartProduct in amazonCodeFirstContext.CartProduct
on cart.Id equals cartProduct.CartId
group cartProduct by cartProduct.CartId into groupingCartProduct
select new KeyValuePair <int, int>
(
groupingCartProduct.Key,
groupingCartProduct.Sum(g => g.Quantity)
);
var data = query.ToList();
return(data);
}
}
public IEnumerable <ProductShippingRate> GetProductsWithShippingRatesExemplifyingManyToManyRelationship()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from productShippingRate in amazonCodeFirstContext.ProductShippingRate
join product in amazonCodeFirstContext.Product
on productShippingRate.ProductId equals product.Id
join shippingRate in amazonCodeFirstContext.ShippingRate
on productShippingRate.ShippingRateId equals shippingRate.Id
select new
{
product, productShippingRate, shippingRate
};
var data = query.ToList();
return(data.Select(d => d.productShippingRate));
}
}
public IEnumerable <Cart> GetCartsWithProductsExemplifyingOneToManyRelationship()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from cart in amazonCodeFirstContext.Cart
join cartProduct in amazonCodeFirstContext.CartProduct
on cart.Id equals cartProduct.CartId
join product in amazonCodeFirstContext.Product
on cartProduct.ProductId equals product.Id
select new
{
cart, cartProduct, product
};
var data = query.ToList();
return(data.Select(d => d.cart).Distinct());
}
}
public IEnumerable <Customer> GetCustomersWithAddressAndCartsBasedInWhereWithOperatorLIKE()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
where customer.Name.StartsWith("Sta") || customer.Name.Contains("wel") || customer.Name.EndsWith("nes")
select new
{
customer, address, cart
};
var data = query.ToList();
return(data.Select(d => d.customer).Distinct());
}
}
public void FullPopulateDatabase(DTOAmazonDatabaseData amazonDatabaseData)
{
using (var amazonCodeFirstDbContext = new AmazonCodeFirstDbContext())
{
amazonCodeFirstDbContext.Address.AddRange(amazonDatabaseData.Adresses);
amazonCodeFirstDbContext.Product.AddRange(amazonDatabaseData.Products);
amazonCodeFirstDbContext.ShippingRate.AddRange(amazonDatabaseData.ShippingRates);
amazonCodeFirstDbContext.Customer.AddRange(amazonDatabaseData.Customers);
amazonCodeFirstDbContext.Cart.AddRange(amazonDatabaseData.Carts);
amazonCodeFirstDbContext.CartProduct.AddRange(amazonDatabaseData.CartProducts);
amazonCodeFirstDbContext.SaveChanges();
}
using (var amazonCodeFirstDbContext = new AmazonCodeFirstDbContext())
{
amazonCodeFirstDbContext.ProductShippingRate.AddRange(amazonDatabaseData.ProductsShippingRates);
amazonCodeFirstDbContext.SaveChanges();
}
}
public IEnumerable <KeyValuePair <int, int> > GetCustomersIdsAndCartsQuantityWithGROUPBY()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
group customer by customer.Id into groupingCustomer
select new KeyValuePair <int, int>
(
groupingCustomer.Key,
groupingCustomer.Count()
);
var data = query.ToList();
return(data);
}
}
public IEnumerable <Customer> GetCustomersWithAddressAndCartsBasedInLeftJoin()
{
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId into groupingCart
from cart in groupingCart.DefaultIfEmpty()
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id into groupingAddress
from address in groupingAddress.DefaultIfEmpty()
select new
{
customer, address, cart
};
var data = query.ToList();
return(data.Select(d => d.customer).Distinct());
}
}
public decimal GetUpdateTimeStatisticsAddRange(bool useDbSetToSave)
{
var fifteenThousandAddressWithoutId = MakeFifteenThousandAddress(generateIncrementalId: false);
var fifteenThousandAddress = MakeFifteenThousandAddress(generateIncrementalId: true);
var amazonAddressInsertLabMapper = new AmazonAddressInsertLabMapper();
var amazonAddressUpdateLabMapper = new AmazonAddressUpdateLabMapper();
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonAddressInsertLabMapper.CleanAddressData(amazonCodeFirstContext);
amazonAddressInsertLabMapper.InsertAddressWithDbSetWithAddRange(fifteenThousandAddressWithoutId);
var updateTimeAllRecords = useDbSetToSave
? amazonAddressUpdateLabMapper.UpdateAddressWithDbSetWithAddRange(fifteenThousandAddress)
: amazonAddressUpdateLabMapper.UpdateAddressWithDbContextWithAddRange(fifteenThousandAddress);
var updateTime = decimal.Divide(updateTimeAllRecords, fifteenThousandAddress.Count());
return(updateTime);
}
}
public IEnumerable <Customer> GetCustomersWithAddressAndCartsBasedInWhereWithOperatorBETWEEN()
{
var startDate = new DateTime(2020, 03, 01);
var endDate = new DateTime(2020, 04, 30);
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
var query = from customer in amazonCodeFirstContext.Customer
join cart in amazonCodeFirstContext.Cart
on customer.Id equals cart.CustomerId
join address in amazonCodeFirstContext.Address
on customer.AddressId equals address.Id
where cart.PurchaseDate >= startDate && cart.PurchaseDate <= endDate
select new
{
customer, address, cart
};
var data = query.ToList();
return(data.Select(d => d.customer).Distinct());
}
}
public InsertTimeStatistics GetInsertTimeStatisticsWithExecuteSqlInterpolated()
{
var insertTimeStatistics = new InsertTimeStatistics();
var rowsInserted = 0;
var fifteenThousandAddress = MakeFifteenThousandAddress();
var rowCutOffToEmptyTable = Faker.RandomNumber.Next(5, 100);
var rowCutOffToTableWithFiveThousandRows = Faker.RandomNumber.Next(6_000, 9_000);
var rowCutOffToTableWithTenThousandRows = Faker.RandomNumber.Next(11_000, 14_000);
var tenInsertTimes = new List <long>();
var amazonAddressInsertLabMapper = new AmazonAddressInsertLabMapper();
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonAddressInsertLabMapper.CleanAddressData(amazonCodeFirstContext);
foreach (var address in fifteenThousandAddress)
{
var insertTime = amazonAddressInsertLabMapper.InsertAddressWithExecuteSqlInterpolated(amazonCodeFirstContext, address);
rowsInserted++;
if (IsRowToBeComputed(rowsInserted, rowCutOffToEmptyTable))
{
tenInsertTimes.Add(insertTime);
if (tenInsertTimes.Count == _tenRegisters)
{
var insertTimesAverage = Enumerable.Average(tenInsertTimes);
insertTimeStatistics.MillisecondsAverageBasedOnTenInsertsWithEmptyTable = insertTimesAverage;
tenInsertTimes.Clear();
}
}
if (IsRowToBeComputed(rowsInserted, rowCutOffToTableWithFiveThousandRows))
{
tenInsertTimes.Add(insertTime);
if (tenInsertTimes.Count == _tenRegisters)
{
var insertTimesAverage = Enumerable.Average(tenInsertTimes);
insertTimeStatistics.MillisecondsAverageBasedOnTenInsertsWithTableWithFiveThousandsRows = insertTimesAverage;
tenInsertTimes.Clear();
}
}
if (IsRowToBeComputed(rowsInserted, rowCutOffToTableWithTenThousandRows))
{
tenInsertTimes.Add(insertTime);
if (tenInsertTimes.Count == _tenRegisters)
{
var insertTimesAverage = Enumerable.Average(tenInsertTimes);
insertTimeStatistics.MillisecondsAverageBasedOnTenInsertsWithTableWithTenThousandsRows = insertTimesAverage;
tenInsertTimes.Clear();
}
}
}
}
return(insertTimeStatistics);
}
public DeleteTimeStatistics GetDeleteTimeStatisticsWithExecuteSqlInterpolated()
{
var deleteTimeStatistics = new DeleteTimeStatistics();
var rowsDeleted = 0;
var fifteenThousandAddressWithoutId = MakeFifteenThousandAddress(generateIncrementalId: false);
var fifteenThousandAddress = MakeFifteenThousandAddress(generateIncrementalId: true);
var rowCutOffToEmptyTable = Faker.RandomNumber.Next(5, 100);
var rowCutOffToTableWithFiveThousandRows = Faker.RandomNumber.Next(6_000, 9_000);
var rowCutOffToTableWithTenThousandRows = Faker.RandomNumber.Next(11_000, 14_000);
var tenDeleteTimes = new List <long>();
var amazonAddressInsertLabMapper = new AmazonAddressInsertLabMapper();
var amazonAddressDeleteLabMapper = new AmazonAddressDeleteLabMapper();
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonAddressInsertLabMapper.CleanAddressData(amazonCodeFirstContext);
amazonAddressInsertLabMapper.InsertAddressWithDbSetWithAddRange(fifteenThousandAddressWithoutId);
foreach (var address in fifteenThousandAddress)
{
var deleteTime = amazonAddressDeleteLabMapper.DeleteAddressWithExecuteSqlInterpolated(amazonCodeFirstContext, address);
rowsDeleted++;
if (IsRowToBeComputed(rowsDeleted, rowCutOffToEmptyTable))
{
tenDeleteTimes.Add(deleteTime);
if (tenDeleteTimes.Count == _tenRegisters)
{
var deleteTimesAverage = Enumerable.Average(tenDeleteTimes);
deleteTimeStatistics.MillisecondsAverageBasedOnTenDeletesWithEmptyTable = deleteTimesAverage;
tenDeleteTimes.Clear();
}
}
if (IsRowToBeComputed(rowsDeleted, rowCutOffToTableWithFiveThousandRows))
{
tenDeleteTimes.Add(deleteTime);
if (tenDeleteTimes.Count == _tenRegisters)
{
var deleteTimesAverage = Enumerable.Average(tenDeleteTimes);
deleteTimeStatistics.MillisecondsAverageBasedOnTenDeletesWithTableWithFiveThousandsRows = deleteTimesAverage;
tenDeleteTimes.Clear();
}
}
if (IsRowToBeComputed(rowsDeleted, rowCutOffToTableWithTenThousandRows))
{
tenDeleteTimes.Add(deleteTime);
if (tenDeleteTimes.Count == _tenRegisters)
{
var deleteTimesAverage = Enumerable.Average(tenDeleteTimes);
deleteTimeStatistics.MillisecondsAverageBasedOnTenDeletesWithTableWithTenThousandsRows = deleteTimesAverage;
tenDeleteTimes.Clear();
}
}
}
}
return(deleteTimeStatistics);
}
public void CleanAddressData(AmazonCodeFirstDbContext amazonCodeFirstContext)
{
amazonCodeFirstContext.Database.ExecuteSqlInterpolated($"delete from common.Address where Id > 3");
amazonCodeFirstContext.Database.ExecuteSqlInterpolated($"DBCC CHECKIDENT ('common.Address', RESEED, 3)");
}
public UpdateTimeStatistics GetUpdateTimeStatisticsWithDbContextRecycle(bool useDbSetToSave)
{
var updateTimeStatistics = new UpdateTimeStatistics();
var rowsUpdated = 0;
var fifteenThousandAddressWithoutId = MakeFifteenThousandAddress(generateIncrementalId: false);
var fifteenThousandAddress = MakeFifteenThousandAddress(generateIncrementalId: true);
var rowCutOffToEmptyTable = Faker.RandomNumber.Next(5, 100);
var rowCutOffToTableWithFiveThousandRows = Faker.RandomNumber.Next(6_000, 9_000);
var rowCutOffToTableWithTenThousandRows = Faker.RandomNumber.Next(11_000, 14_000);
var tenUpdateTimes = new List <long>();
var amazonAddressInsertLabMapper = new AmazonAddressInsertLabMapper();
var amazonAddressUpdateLabMapper = new AmazonAddressUpdateLabMapper();
using (var amazonCodeFirstContext = new AmazonCodeFirstDbContext())
{
amazonAddressInsertLabMapper.CleanAddressData(amazonCodeFirstContext);
amazonAddressInsertLabMapper.InsertAddressWithDbSetWithAddRange(fifteenThousandAddressWithoutId);
foreach (var address in fifteenThousandAddress)
{
address.Street = "Updated Street";
var updateTime = useDbSetToSave
? amazonAddressUpdateLabMapper.UpdateAddressWithDbSet(address)
: amazonAddressUpdateLabMapper.UpdateAddressWithDbContext(address);
rowsUpdated++;
if (IsRowToBeComputed(rowsUpdated, rowCutOffToEmptyTable))
{
tenUpdateTimes.Add(updateTime);
if (tenUpdateTimes.Count == _tenRegisters)
{
var updateTimesAverage = Enumerable.Average(tenUpdateTimes);
updateTimeStatistics.MillisecondsAverageBasedOnTenUpdatesWithEmptyTable = updateTimesAverage;
tenUpdateTimes.Clear();
}
}
if (IsRowToBeComputed(rowsUpdated, rowCutOffToTableWithFiveThousandRows))
{
tenUpdateTimes.Add(updateTime);
if (tenUpdateTimes.Count == _tenRegisters)
{
var updateTimesAverage = Enumerable.Average(tenUpdateTimes);
updateTimeStatistics.MillisecondsAverageBasedOnTenUpdatesWithTableWithFiveThousandsRows = updateTimesAverage;
tenUpdateTimes.Clear();
}
}
if (IsRowToBeComputed(rowsUpdated, rowCutOffToTableWithTenThousandRows))
{
tenUpdateTimes.Add(updateTime);
if (tenUpdateTimes.Count == _tenRegisters)
{
var updateTimesAverage = Enumerable.Average(tenUpdateTimes);
updateTimeStatistics.MillisecondsAverageBasedOnTenUpdatesWithTableWithTenThousandsRows = updateTimesAverage;
tenUpdateTimes.Clear();
}
}
}
}
return(updateTimeStatistics);
}