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);
}
}