Capitulo 4: LINQ to SQL (Parte 2)

Lección 2: Ejecutando de consultas con LINQ to SQL
Consultas básicas con filtro y Orden 
Las consultas básicas usando las clases LINQ to SQL son muy limpias y legibles. Además, las clases LINQ to SQL recuperan sólo los datos que solicitamos.
El siguiente código de ejemplo lista los clientes que contengan la palabra "restaurante" en el nombre de la empresa, ordenados en el código postal:

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = From c In ctx.Customers _
Where c.CompanyName.Contains("Restaurant")Order By c.PostalCodec dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
where c.CompanyName.Contains("Restaurant")
orderby c.PostalCode
select c;
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());
 

la consulta genera el  siguiente sql:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],

[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].
[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[PostalCode]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%Restaurant%]

Proyecciones
Uno de los problemas de la consulta anterior es que se devuelven todas las columnas de la tabla Customer, pero podría necesitarse sólo CustomerID, CompanyName y PostalCode. Se puede utilizar una proyección para limitar los valores de columna devueltos desde SQL Server:

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = From c In ctx.Customers
Where c.CompanyName.Contains("Restaurant")
Order By c.PostalCode
Select New With {c.CustomerID, c.CompanyName, c.PostalCode}
dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
where c.CompanyName.Contains("Restaurant")
orderby c.PostalCode
select new
{
c.CustomerID,
c.CompanyName,
c.PostalCode
};
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());

Inner joins
Una combinación interna produce la salida sólo cuando las dos tablas que se unencoinciden en la clave única para extranjeros key.The siguiente consulta LINQ produce un inner join de la tabla Customers a la tabla Orders y recupera CustomerID CompanyName,OrderID y OrderDate.

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = ctx.Customers.Join(ctx.Orders,
Function(c) c.CustomerID,
Function(o) o.CustomerID,
Function(c, o) New With
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
}) _
.OrderBy(Function(r) r.CustomerID) _
.ThenBy(Function(r) r.OrderID)
dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = ctx.Customers.Join(
ctx.Orders,
c => c.CustomerID,
o => o.CustomerID,
(c, o) => new
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
})
.OrderBy(r=>r.CustomerID)
.ThenBy((r=>r.OrderID));
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());

y usando linQ:

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = From c In ctx.Customers
Join o In ctx.Orders
On c.CustomerID Equals o.CustomerID
Order By c.CustomerID, o.OrderID
Select New With
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
}
dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
join o in ctx.Orders
on c.CustomerID equals o.CustomerID
orderby c.CustomerID, o.OrderID
select new
{
c.CustomerID,
c.CompanyName,
o.OrderID,
o.OrderDate
};
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());

se genera el siguiente sql:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

Outer Joins

Un outer join produce una salida, incluso si el elemento de la tabla externa no coincide con la tabla interna. Para realizar una outer join, se debe indicar que aún desea la fila de la tabla externa, incluso si no hay ninguna coincidencia con la tabla interna.Se pueden realizar combinaciones externas mediante el método de extensión GroupJoin:

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = ctx.Customers.GroupJoin(ctx.Orders, _
Function(c) c.CustomerID, _
Function(o) o.CustomerID, _
Function(c, o) New With
{
c.CustomerID,
c.CompanyName,
.Orders = o
}) _
.SelectMany(Function(t) t.Orders.DefaultIfEmpty().Select( _
Function(ord) New With
{
t.CompanyName,
t.CustomerID,
.OrderID = CType(ord.OrderID, Nullable(Of Integer)),
.OrderDate = CType(ord.OrderDate, Nullable(Of DateTime))
})) _
.OrderBy(Function(r) r.CustomerID) _
.ThenBy(Function(r) r.OrderID)
dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = ctx.Customers.GroupJoin(
ctx.Orders,
c => c.CustomerID,
o => o.CustomerID,
(c, o) => new
{
c.CustomerID,
c.CompanyName,
Orders = o
})
.SelectMany(t=>t.Orders.DefaultIfEmpty().Select(ord=>
new
{
t.CompanyName,
t.CustomerID,
OrderID=(int?)ord.OrderID,
OrderDate=(DateTime?) ord.OrderDate}))
.OrderBy(r => r.CustomerID).ThenBy((r => r.OrderID));
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());

También se puede realizar un outer join mediante una consulta LINQ con la palabra clave join:

-----------------------------------VB------------------------------------

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim customers = From c In ctx.Customers
Group Join o In ctx.Orders
On c.CustomerID Equals o.CustomerID Into InJoin = Group
From outJoin In InJoin.DefaultIfEmpty()
Order By c.CustomerID, outJoin.OrderID
Select New With
{
c.CustomerID,
c.CompanyName,
.OrderID = CType(outJoin.OrderID, Nullable(Of Integer)),
.OrderDate = CType(outJoin.OrderDate, Nullable(Of DateTime))
}
dg.ItemsSource = customers
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var customers = from c in ctx.Customers
join o in ctx.Orders
on c.CustomerID equals o.CustomerID into inJoin
from outJoin in inJoin.DefaultIfEmpty()
orderby c.CustomerID, outJoin.OrderID
select new
{
c.CustomerID,
c.CompanyName,
OrderID = (int?)outJoin.OrderID,
OrderDate = (DateTime?)outJoin.OrderDate
};w
dg.ItemsSource = customers;
MessageBox.Show(sw.GetStringBuilder().ToString());

El SQL obtenido:

SELECT [t0].[CustomerID], [t0].[CompanyName],
[t1].[OrderID] AS [OrderID2], [t1].[OrderDate] AS [OrderDate]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

Agrupación y agregación

LINQ to SQL también permite realizar operaciones de agrupación para recuperar los resultados agregados, por ej:

Dim ctx = New NorthwindDataContext()
Dim sw = New StringWriter()
ctx.Log = sw
Dim orders = From o In ctx.Order_Details
Group o By OrderID = o.OrderID Into grouped = Group
Select New With
{
.OrderID = OrderID,
.Total = grouped.Sum(Function(line) _
line.Quantity * line.UnitPrice * _
(1 - CType(line.Discount, Decimal)))
}
dg.ItemsSource = orders
MessageBox.Show(sw.GetStringBuilder().ToString())

-----------------------------------CS------------------------------------

var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var orders = from o in ctx.Order_Details
group o by o.OrderID
into grouped
select new
{
OrderID = grouped.Key,
Total = grouped.Sum(
line=>line.Quantity * line.UnitPrice *
(1 - (decimal)line.Discount))
};

Este ejemplo de código agrupa las filas Order_Details por OrderID y despues se calcula el total de cada pedido mediante el cálculo de la suma de las partidas de la orden. Para calcular la suma, cada línea tenía que ser calculado multiplicando la cantidad por el precio unitario por uno menos el descuento.


Y el SQL resultante seria:

SELECT SUM([t1].[value]) AS [Total], [t1].[OrderID]
FROM (
SELECT (CONVERT(Decimal(29,4),[t0].[Quantity])) * [t0].[UnitPrice] *
(@p0 - (CONVERT(Decimal(33,4),[t0].[Discount]))) AS [value], [t0].[OrderID]
FROM [dbo].[Order Details] AS [t0]
) AS [t1]
GROUP BY [t1].[OrderID]
-- @p0: Input Decimal (Size = -1; Prec = 33; Scale = 4) [1]

Resumen de la lección

■ Se pueden utilizar las propiedades de tabla en el DataContext para ejecutar consultas LINQ.
■ Si se proporciona una cláusula where , LINQ to SQL creará una consulta SQL que incluye la cláusula WHERE para limitar las filas devueltas a la aplicación.
■ Si se proporciona una proyección en la cláusula SELECT para limitar las propiedades que se seleccionan, LINQ to SQL creará una consulta SQL que incluye un filtro de columna coincidente para limitar los datos devueltos a la aplicación.