static void LINQToEntities() { using (NorthwindContext db = new NorthwindContext()) { // Filtro var q1 = from c in db.Customers where c.Country == "Mexico" select c; var q1x = db.Customers. Where(c => c.Country == "Mexico"); //var result = q1.Count(); //var any = result.Any(); foreach (var item in q1x) { Console.WriteLine($"{item.CustomerID} {item.CompanyName} {item.ContactName} {item.Country}"); } } // * Proyecciones using (NorthwindContext db = new NorthwindContext()) { var q2 = from c in db.Customers select c.Country; var q2x = db.Customers.Select(c => c.Country); // Usando tipos anónimos var q2y = from c in db.Customers select new { c.CustomerID, c.ContactName }; var q2z = db.Customers.Select(c => new { Id = c.CustomerID, c.ContactName }); var q2a = from c in db.Customers select new Category() { CategoryName = c.ContactName }; var q2w = db.Customers.Select(c => new Category() { CategoryName = c.ContactName }); Console.Clear(); foreach (var item in q2z) { Console.WriteLine($"{item.Id}, {item.ContactName}"); } } // SelectMany using (NorthwindContext db = new NorthwindContext()) { var q4 = db.Customers. Where(c => c.Country == "Mexico"). SelectMany(c => c.Orders); // Esto aplica si solo se usar .Select //foreach (var c in q4) //{ // foreach (var o in c) // { // } //} var q4x = db.Orders. Where(o => o.Customer.Country == "Mexico"); Console.Clear(); foreach (var item in q4x) { Console.WriteLine($"{item.CustomerID}, {item.OrderID}"); } } // * Ordenamiento using (NorthwindContext db = new NorthwindContext()) { var q5 = from c in db.Customers where c.Orders.Count > 5 orderby c.Country descending select c; var q5x = db.Customers. Where(c => c.Orders.Count > 5). OrderByDescending(c => c.Country); Console.Clear(); foreach (var item in q5) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } var q6 = from c in db.Customers orderby c.CompanyName, c.ContactTitle, c.ContactName select c; var q6x = db.Customers.OrderBy(c => new { c.CompanyName, c.ContactTitle }). ThenBy(c => c.ContactName); Console.Clear(); foreach (var item in q6) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } } // Agrupamiento using (NorthwindContext db = new NorthwindContext()) { var q7 = from c in db.Customers group c by c.Country into CustByCountry select CustByCountry; var q7x = db.Customers.GroupBy(c => c.Country); Console.Clear(); foreach (var grupo in q7) { Console.WriteLine($"{grupo.Key}, {grupo.Count()}"); foreach (var c in grupo) { Console.WriteLine($"\t{c.ContactName}"); } } var q7y = from c in db.Customers group c by new { c.Country, c.City } into CountryCity where CountryCity.Count() > 1 select new { Country = CountryCity.Key.Country, City = CountryCity.Key.City, Count = CountryCity.Count(), Items = CountryCity }; var q7y2 = db.Customers.GroupBy(c => new { c.Country, c.City }). Where(g => g.Count() > 1). Select(g => new { Country = g.Key.Country, City = g.Key.City, Count = g.Count(), Items = g }); Console.Clear(); foreach (var item in q7y) { Console.WriteLine($"{item.Country}, {item.City}, {item.Count}"); foreach (var c in item.Items) { Console.WriteLine($"\t{c.ContactName}"); } } } // Join using (NorthwindContext db = new NorthwindContext()) { var q8 = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID select new { c, o }; // new { c.CustomerID, c.Country } //equals new { o.CustomerID, Country = o.ShipCountry } var q8x = db.Customers.Join( db.Orders, c => c.CustomerID, o => o.CustomerID, (c, o) => new { c, o }); Console.Clear(); foreach (var item in q8) { Console.WriteLine($"{item.c.CustomerID}, {item.o.OrderID}"); } // Join agrupado var q8y = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into CustomerOrders select new { c, Orders = CustomerOrders }; //select CustomerOrders; foreach (var ordenes in q8y) { //foreach (var orden in ordenes) //{ //} } // Left Ourter Join var q8z = from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into CustomerOrders from detalle in CustomerOrders.DefaultIfEmpty() select new { Customer = c, Order = detalle }; foreach (var item in q8z) { if (item.Order == null) { Console.WriteLine($"Customer {item.Customer.CustomerID} with NO orders!"); } } } // Conjuntos using (NorthwindContext db = new NorthwindContext()) { var q9 = db.Customers. Select(c => c.Country).Distinct(); var q10 = db.Customers.Except( db.Customers.Where( c => c.Country == "Mexico")). Select(c => c.Country).Distinct(); var q11 = db.Customers.Where(c => c.Country == "Germany").Union(db.Customers.Where(c => c.Country == "Italy")); Console.Clear(); foreach (var item in q11) { Console.WriteLine($"{item.ContactName} {item.Country}"); } } // * Partición (paginación) using (NorthwindContext db = new NorthwindContext()) { var q11 = db.Customers. OrderBy(c => c.CustomerID). Skip(10); // Tomar los primero 10 elementos var q12 = db.Customers. OrderBy(c => c.CustomerID). Take(10); // Segunda página de 5 elementos var q13 = db.Customers. OrderBy(c => c.CustomerID). Skip(5).Take(5); // SELECT* // FROM[dbo].[Products] AS[Extent1] // ORDER BY row_number() OVER(ORDER BY[Extent1].[ProductID] ASC) // OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY var q14 = db.Customers. OrderBy(c => c.CustomerID). TakeWhile(c => c.Country == "Mexico"); Console.Clear(); foreach (var item in q13) { Console.WriteLine($"{item.CustomerID}, {item.CompanyName}"); } } // * Modificación de consulta using (NorthwindContext db = new NorthwindContext()) { var q14 = db.Customers. Where(c => c.Orders.Count > 5); Console.Clear(); Console.WriteLine(q14.Count()); q14 = q14.Where(c => c.Country == "Mexico"); Console.WriteLine(q14.Count()); q14 = q14.OrderByDescending(c => c.ContactName); foreach (var item in q14) { Console.WriteLine(item.ContactName); } } // * Métodos útiles using (NorthwindContext db = new NorthwindContext()) { var o1 = db.Customers.First(); o1 = db.Customers.FirstOrDefault(); o1 = db.Customers.Where(c => c.CustomerID == "ALFKI") .Single(); o1 = db.Customers.Where(c => c.CustomerID == "ALFKI"). SingleOrDefault(); o1 = db.Customers.SingleOrDefault(c => c.CustomerID == "ALFKI"); var o2 = db.Customers.All(c => c.Orders.Count > 5 && c.Country == "Mexico"); o2 = db.Customers. Any(c => c.Orders.Count > 5); var sum = db.OrderDetails. Sum(od => od.Quantity * od.UnitPrice); var avg = db.OrderDetails. Average(od => od.Quantity * od.UnitPrice); } }
static void LINQToEntities() { using (NWContext db = new NWContext()) { // Filtro var q1 = from c in db.Customers where c.Country == "Mexico" select c; var q1x = db.Customers. Where(c => c.Country == "Mexico"); //var result = q1.Count(); //var any = result.Any(); foreach (var item in q1x) { Console.WriteLine($"{item.CustomerId} {item.CompanyName} {item.ContactName} {item.Country}"); } } // Proyecciones using (NWContext db = new NWContext()) { var q2 = from c in db.Customers select c.Country; var q2x = db.Customers.Select(c => c.Country); var q2y = from c in db.Customers select new { c.CustomerId, c.ContactName }; var q2z = db.Customers.Select(c => new { Id = c.CustomerId, c.ContactName }); var q2w = db.Customers.Select(c => new Categories() { CategoryName = c.ContactName }); Console.Clear(); foreach (var item in q2z) { Console.WriteLine($"{item.Id}, {item.ContactName}"); } } // SelectMany using (NWContext db = new NWContext()) { var q4 = db.Customers. Where(c => c.Country == "Mexico"). SelectMany(c => c.Orders); var q4x = db.Orders. Where(o => o.Customer.Country == "Mexico"); Console.Clear(); foreach (var item in q4) { Console.WriteLine($"{item.CustomerId}, {item.OrderId}"); } } // Ordenamiento using (NWContext db = new NWContext()) { var q5 = from c in db.Customers where c.Orders.Count > 5 orderby c.Country descending select c; var q5x = db.Customers. Where(c => c.Orders.Count > 5). OrderByDescending(c => c.Country); Console.Clear(); foreach (var item in q5) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } var q6 = from c in db.Customers orderby c.CompanyName, c.ContactTitle, c.ContactName select c; var q6x = db.Customers.OrderBy(c => new { c.CompanyName, c.ContactTitle }). ThenBy(c => c.ContactName); Console.Clear(); foreach (var item in q6) { Console.WriteLine($"{item.CompanyName}, {item.Country}"); } } // Agrupamiento using (NWContext db = new NWContext()) { var q7 = from c in db.Customers group c by c.Country into CustByCountry select CustByCountry; var q7x = db.Customers.GroupBy(c => c.Country); Console.Clear(); foreach (var item in q7) { Console.WriteLine($"{item.Key}, {item.Count()}"); foreach (var c in item) { Console.WriteLine($"\t{c.ContactName}"); } } var q7y = from c in db.Customers group c by new { c.Country, c.City } into CountryCity where CountryCity.Count() > 1 select new { Country = CountryCity.Key.Country, City = CountryCity.Key.City, Count = CountryCity.Count(), Items = CountryCity }; var q7y2 = db.Customers.GroupBy(c => new { c.Country, c.City }). Where(g => g.Count() > 1). Select(g => new { Country = g.Key.Country, City = g.Key.City, Count = g.Count(), Items = g }); Console.Clear(); foreach (var item in q7y) { Console.WriteLine($"{item.Country}, {item.City}, {item.Count}"); foreach (var c in item.Items) { Console.WriteLine($"\t{c.ContactName}"); } } } // Join using (NWContext db = new NWContext()) { var q8 = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId select new { c, o }; // new { c.CustomerID, c.Country } //equals new { o.CustomerID, Country = o.ShipCountry } var q8x = db.Customers.Join( db.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { c, o }); Console.Clear(); foreach (var item in q8) { Console.WriteLine($"{item.c.CustomerId}, {item.o.OrderId}"); } // Join agrupado var q8y = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId into CustomerOrders select new { c, Orders = CustomerOrders }; //select CustomerOrders; foreach (var ordenes in q8y) { //foreach (var orden in ordenes) //{ //} } // Left Ourter Join var q8z = from c in db.Customers join o in db.Orders on c.CustomerId equals o.CustomerId into CustomerOrders from detalle in CustomerOrders.DefaultIfEmpty() select new { Customer = c, Order = detalle }; foreach (var item in q8z) { if (item.Order == null) { Console.WriteLine($"Customer {item.Customer.CustomerId} with NO orders!"); } } } // Conjuntos using (NWContext db = new NWContext()) { var q9 = db.Customers. Select(c => c.Country).Distinct(); var q10 = db.Customers.Except( db.Customers.Where( c => c.Country == "Mexico")). Select(c => c.Country).Distinct(); Console.Clear(); foreach (var item in q10) { Console.WriteLine($"{item}"); } } // Partición (paginación) using (NWContext db = new NWContext()) { var q11 = db.Customers. OrderBy(c => c.CustomerId). Skip(10); // Tomar los primero 10 elementos var q12 = db.Customers. OrderBy(c => c.CustomerId). Take(10); // Segunda página de 10 elementos var q13 = db.Customers. OrderBy(c => c.CustomerId). Skip(10).Take(10); Console.Clear(); foreach (var item in q13) { Console.WriteLine($"{item.CustomerId}, {item.CompanyName}"); } } // Modificación de consulta using (NWContext db = new NWContext()) { var q14 = db.Customers. Where(c => c.Orders.Count > 5); Console.Clear(); Console.WriteLine(q14.Count()); q14 = q14.Where(c => c.Country == "Mexico"); Console.WriteLine(q14.Count()); q14 = q14.OrderByDescending(c => c.ContactName); foreach (var item in q14) { Console.WriteLine(item.ContactName); } } // Métodos útiles using (NWContext db = new NWContext()) { var o1 = db.Customers.First(); o1 = db.Customers.FirstOrDefault(); o1 = db.Customers.Where(c => c.CustomerId == "ALFKI") .Single(); o1 = db.Customers.Where(c => c.CustomerId == "ALFKI"). SingleOrDefault(); var o2 = db.Customers.All(c => c.Orders.Count > 5 && c.Country == "Mexico"); o2 = db.Customers. Any(c => c.Orders.Count > 5); var sum = db.OrderDetails. Sum(od => od.Quantity * od.UnitPrice); } }