private static int SecondInsert()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
int id;
using (var context = new BluewindsEntities())
{
var customer = new Customer()
{
City = "Barcelona",
Country = "Spain",
FirstName = "Dario",
LastName = "Griffo",
Phone = "123456789"
};
context.Customers.Add(customer);
context.SaveChanges();
id = customer.Id;
}
return(id);
}
static void Main(string[] args)
{
using (var context = new BluewindsEntities())
{
//var lastWeekOrders2 = context
// .Orders
// .Where(order => order.CustomerId == 1) // sobre el customerID
// .OrderBy(x => x.TotalAmount)
// .ToList();
//var lastWeekOrders1 = context
// .Orders
// .Where(order => order.Customer.Id == 1) // sobre el Id del customer
// .OrderBy(x => x.TotalAmount)
// .ToList();
var lastWeekOrders = context
.Orders
.Where(order => order.Customer.FirstName == "Ana" && order.Customer.LastName == "Trujillo")
.OrderBy(x => x.TotalAmount)
.ToList();
foreach (var order in lastWeekOrders)
{
Console.WriteLine($"{order.Id} + Date: {order.OrderDate} + Amount: {order.TotalAmount}");
}
}
}
private static void QueryWithLazyLoadingDisabled()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var customer = context
.Customers
.FirstOrDefault(x => x.FirstName == "Ana" && x.LastName == "Trujillo");
Console.WriteLine($"Customer: {customer.Id}");
var orders = context.Orders.Where(x => x.CustomerId == customer.Id).ToList();
foreach (var order in customer.Orders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
foreach (var item in order.OrderItems)
{
Console.WriteLine($"Item: {item.Product.ProductName} + Amount: {item.Quantity}");
}
}
}
}
private static void TransactionedInsert()
{
using (var context = new BluewindsEntities())
{
context.Database.Log = Console.Write;
var customer = new Customer()
{
City = "Barcelona",
Country = "Spain",
FirstName = "Lio",
LastName = "Messi",
Phone = "123456789"
};
using (var transaction = context.Database.BeginTransaction())
{
try
{
context.Customers.Add(customer);
context.SaveChanges();
var order = new Order()
{
OrderDate = DateTime.Today,
CustomerId = customer.Id,
TotalAmount = 100,
OrderNumber = "123456"
};
context.Orders.Add(order);
context.SaveChanges();
var item = new OrderItem()
{
Product = context.Products.First(x => x.Id == 1),
OrderId = order.Id,
Quantity = 10,
UnitPrice = 50
};
context.OrderItems.Add(item);
context.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine(ex);
}
}
}
}
static void Main(string[] args)
{
var from = DateTime.Parse("2012-01-01");
var to = from.AddYears(1);
using (var context = new BluewindsEntities())
{
var lastWeekOrders = context
.Orders
.Where(x => x.OrderDate >= from && x.OrderDate < to)
.ToList();
foreach (var order in lastWeekOrders)
{
Console.WriteLine(order.Id);
}
}
using (var context = new BluewindsEntities())
{
var lastWeekOrders = context
.Orders
.Where(x => x.OrderDate >= from && x.OrderDate < to)
.GroupBy(x => x.OrderDate.Month)
.ToList();
foreach (var monthlyOrders in lastWeekOrders)
{
Console.WriteLine($"Mes: {monthlyOrders.Key}: Total; {monthlyOrders.Count()}");
foreach (var order in monthlyOrders)
{
Console.WriteLine(order.Id);
}
}
}
using (var context = new BluewindsEntities())
{
var lastWeekOrders = context
.Orders
.Where(x => x.CustomerId == 2)
.OrderBy(x => x.TotalAmount)
.ToList();
foreach (var order in lastWeekOrders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
}
}
}
private static void DeleteCustomerWithId(int id)
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
var customer = context.Customers.First(x => x.Id == id);
Console.WriteLine($"DELETE Customer id: {customer.Id}");
context.Customers.Remove(customer);
context.SaveChanges();
}
}
private static int ThirdInsert()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
int id;
using (var context = new BluewindsEntities())
{
var customer = new Customer()
{
City = "Barcelona",
Country = "Spain",
FirstName = "Dario",
LastName = "Griffo",
Phone = "123456789"
};
var order = new Order()
{
OrderDate = DateTime.Today,
Customer = customer,
TotalAmount = 100,
OrderNumber = "123456"
};
var item = new OrderItem()
{
Product = context.Products.First(x => x.Id == 1),
Order = order,
Quantity = 10,
UnitPrice = 50
};
order.OrderItems.Add(item);
customer.Orders.Add(order);
context.Customers.Add(customer);
Console.WriteLine($"INSERT WITH ORDER AND ITEMS Customer id: {customer.Id} + Order id: {order.Id}");
context.SaveChanges();
id = customer.Id;
}
return(id);
}
static void Main(string[] args)
{
using (var context = new BluewindsEntities())
{
var lastWeekOrders = context
.Orders
.Where(x => x.Customer.FirstName == "Ana" && x.Customer.LastName == "Trujillo")
.OrderBy(x => x.TotalAmount)
.ToList();
foreach (var order in lastWeekOrders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
}
}
}
private static void UpdateWithAsNoTrackingOrderForCustomerWithId(int id)
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
var orders = context.Orders.AsNoTracking().Where(x => x.CustomerId == id).ToList();
foreach (var order in orders)
{
order.TotalAmount = order.TotalAmount * (decimal)0.75;
}
var savedOrdersCount = context.SaveChanges();
Console.WriteLine($"UPDATED Orders count: {savedOrdersCount}");
}
}
// }
// private static int FirstInsert()
// {
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// int i;
// using (var context = new BluewindsEntities())
// {
// var customer = new Customer()
// {
// City = "Barcelona",
// Country = "Spain",
// FirstName = "Dario",
// LastName = "Griffo",
// Phone = "123456789"
// };
// context.Customers.Add(customer);
// i = customer.Id;
// }
// return i;
// }
// private static int SecondInsert()
// {
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// int id;
// using (var context = new BluewindsEntities())
// {
// var customer = new Customer() // crea objeto en memoria
// {
// City = "Barcelona",
// Country = "Spain",
// FirstName = "Dario",
// LastName = "Griffo",
// Phone = "123456789"
// };
// context.Customers.Add(customer);
// context.SaveChanges(); // esto hace el commit transaction
// id = customer.Id;
// }
// return id;
// }
// private static int ThirdInsert()
// {
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// int id;
// using (var context = new BluewindsEntities()) // extablece la conexion
// {
// var customer = new Customer() //insertar customer
// {
// City = "Barcelona",
// Country = "Spain",
// FirstName = "Dario",
// LastName = "Griffo",
// Phone = "123456789"
// };
// var order = new Order() //crear ordern
// {
// OrderDate = DateTime.Today,
// Customer = customer,
// TotalAmount = 100,
// OrderNumber = "123456"
// };
// var item = new OrderItem() //agregar item
// {
// Product = context.Products.First(x => x.Id == 1),
// Order = order,
// Quantity = 10,
// UnitPrice = 50
// };
// order.OrderItems.Add(item); //agregar ites a la orden
// customer.Orders.Add(order);
// context.Customers.Add(customer);
// Console.WriteLine($"INSERT WITH ORDER AND ITEMS Customer id: {customer.Id} + Order id: {order.Id}");
// context.SaveChanges();
// id = customer.Id;
// }
// return id;
// }
// private static void DeleteCustomerWithId(int id)
// {
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// using (var context = new BluewindsEntities())
// {
// var customer = context.Customers.First(x => x.Id == id);
// Console.WriteLine($"DELETE Customer id: {customer.Id}");
// context.Customers.Remove(customer); //este remove indica que hay que borrarlo pero no se hace hasta el savechanges
// context.SaveChanges();
// }
// }
// private static void ShowOrdersForCustomerWithId(int id)
// {
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// using (var context = new BluewindsEntities())
// {
// context.Configuration.LazyLoadingEnabled = false;
// var customer = context
// .Customers
// .Include(c => c.Orders)
// .Include(c => c.Orders.Select(o => o.OrderItems.Select(oi => oi.Product)))
// .FirstOrDefault(x => x.Id == id);
// foreach (var order in customer.Orders)
// {
// Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
// foreach (var item in order.OrderItems)
// {
// Console.WriteLine($"Item: {item.Product.ProductName} + Amount: {item.Quantity}");
// }
// }
// }
// }
//private static void UpdateOrderForCustomerWithId(int id)
//{
// Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
// Console.WriteLine("==================\n");
// using (var context = new BluewindsEntities())
// {
// var orders = context.Orders.Where(x => x.CustomerId == id).ToList();
// foreach (var order in orders)
// {
// order.TotalAmount = order.TotalAmount * (decimal)0.75;
// }
// var savedOrdersCount = context.SaveChanges(); // devuelve las entidades o registros que fueron actualizados o insertados
// Console.WriteLine($"UPDATED Orders count: {savedOrdersCount}");
// }
//}
private static void UpdateWithAsNoTrackingOrderForCustomerWithId(int id)
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
var orders = context.Orders.AsNoTracking().Where(x => x.CustomerId == id).ToList(); // as no tracking, dice a la entity framework que las entidades que se devuelvan que no se guarden en memoria para gastar menos recursos yq que vaya más rápido el programa. Pero no se modificarán las changes.
foreach (var order in orders)
{
order.TotalAmount = order.TotalAmount * (decimal)0.75;
}
var savedOrdersCount = context.SaveChanges();
Console.WriteLine($"UPDATED Orders count: {savedOrdersCount}");
}
}
private static void DemoInsert()
{
using (var context = new BluewindsEntities())
{
context.Database.Log = Console.Write;
var customer = new Customer()
{
City = "Barcelona",
Country = "Spain",
FirstName = "Dario",
LastName = "Griffo",
Phone = "123456789"
};
context.Customers.Add(customer);
context.SaveChanges();
var order = new Order()
{
OrderDate = DateTime.Today,
CustomerId = customer.Id,
TotalAmount = 100,
OrderNumber = "123456"
};
context.Orders.Add(order);
context.SaveChanges();
var item = new OrderItem()
{
Product = context.Products.First(x => x.Id == 1),
OrderId = order.Id,
Quantity = 10,
UnitPrice = 50
};
context.OrderItems.Add(item);
context.SaveChanges();
}
}
private static void SelectProperty()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var orders = context
.Customers
.Where(x => x.FirstName == "Ana" && x.LastName == "Trujillo")
.SelectMany(x => x.Orders);
foreach (var order in orders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
foreach (var item in order.OrderItems)
{
Console.WriteLine($"Item: {item.Product.ProductName} + Amount: {item.Quantity}");
}
}
}
}
private static void ShowOrdersForCustomerWithId(int id)
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var customer = context
.Customers
.Include(c => c.Orders)
.Include(c => c.Orders.Select(o => o.OrderItems.Select(oi => oi.Product)))
.FirstOrDefault(x => x.Id == id);
foreach (var order in customer.Orders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
foreach (var item in order.OrderItems)
{
Console.WriteLine($"Item: {item.Product.ProductName} + Amount: {item.Quantity}");
}
}
}
}
// Lazy loading disabled with includes
private static void QueryWithLazyLoadingDisabledWithIncludes()
{
Console.WriteLine(System.Reflection.MethodBase.GetCurrentMethod().Name);
Console.WriteLine("==================\n");
using (var context = new BluewindsEntities())
{
context.Configuration.LazyLoadingEnabled = false;
var customer = context
.Customers
.Include(c => c.Orders) // info de todas las ordenes
.Include(c => c.Orders.Select(o => o.OrderItems.Select(oi => oi.Product))) // todos los productos se cargan en memoria
.FirstOrDefault(x => x.FirstName == "Ana" && x.LastName == "Trujillo"); // select de ana
foreach (var order in customer.Orders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
foreach (var item in order.OrderItems) // aqui la orden de items no está cargada en memoria sin el 2o include, irá a la base de datos
{
Console.WriteLine($"Item: {item.Product.ProductName} + Amount: {item.Quantity}");
}
}
}
}
private static void TransactionedInsertV2()
{
using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["BluewindsEntities"].ConnectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
var customerId = 0;
var orderId = 0;
using (var context = new BluewindsEntities(connection, false))
{
context.Database.UseTransaction(transaction);
context.Database.Log = Console.Write;
var customer = new Customer()
{
City = "Barcelona",
Country = "Spain",
FirstName = "Dario",
LastName = "Griffo",
Phone = "123456789"
};
context.Customers.Add(customer);
context.SaveChanges();
}
using (var context = new BluewindsEntities(connection, false))
{
context.Database.UseTransaction(transaction);
context.Database.Log = Console.Write;
var order = new Order()
{
OrderDate = DateTime.Today,
CustomerId = customerId,
TotalAmount = 100,
OrderNumber = "123456"
};
context.Orders.Add(order);
context.SaveChanges();
}
using (var context = new BluewindsEntities(connection, false))
{
context.Database.UseTransaction(transaction);
context.Database.Log = Console.Write;
var item = new OrderItem()
{
Product = context.Products.First(x => x.Id == 1),
OrderId = orderId + 1,
Quantity = 10,
UnitPrice = 50
};
context.OrderItems.Add(item);
context.SaveChanges();
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine(ex);
}
}
}
}
static void Main(string[] args)
{
var from = DateTime.Parse("2012-01-01");
var to = from.AddYears(1);
// primer query
//using (var database = new BluewindsEntities()) // cómo crear nuestro objeto
//{
// var query = database // se hace la consulta
// .Orders
// // .Where(x => x.OrderDate >= from && x.OrderDate < to && x.TotalAmount > 100);
// .Where(x => x.OrderDate >= from)
// .Where(x => x.OrderDate < to)
// .Where(x => x.TotalAmount > 100);
// // para hacer un AND
// query = query.Where(x => x.OrderDate < to);
// query = query.Where(x => x.TotalAmount > 100);
// var twentyTwelveOrders = query.ToList(); // obtener el resultado
// //var any = twentyTwelveOrders.Any(); //materializa una vez los que cumplan
// // var quantityOfOrders = twentyTwelveOrders.Count(); // cuantos
// var firstOrder = twentyTwelveOrders.First(); // trae top 1 del query que hacemos, da or sentado que al menos 1 cumple condición, sino dará una excepción NULL que no dará error.
// foreach (var order in twentyTwelveOrders)
// {
// Console.WriteLine(order.Id);
// }
//}
// segundo query groupby. Groupby generará una lista de listas
//using (var context = new BluewindsEntities())
//{
// var monthlyOrdersGroups = context
// .Orders
// .Where(x => x.OrderDate >= from && x.OrderDate < to)
// .GroupBy(x => x.OrderDate.Month)
// .ToList();
// foreach (var monthlyOrders in monthlyOrdersGroups)
// {
// Console.WriteLine($"Mes: {monthlyOrders.Key}: Total; {monthlyOrders.Count()}");
// foreach (var order in monthlyOrders)
// {
// Console.WriteLine(order.Id);
// }
// }
//}
// tercer query de orden por total amount.
using (var context = new BluewindsEntities())
{
var lastWeekOrders = context // lista en memoria
.Orders
.Where(x => x.CustomerId == 2)
.OrderByDescending(x => x.TotalAmount)
.ToList();
var lastweekOrders1 = lastWeekOrders // lista en memoria....
.Where(x => x.CustomerId == 2)
.OrderByDescending(x => x.TotalAmount)
.ToList();
foreach (var order in lastWeekOrders)
{
Console.WriteLine($"Date: {order.OrderDate} + Amount: {order.TotalAmount}");
}
}
}