LINQ: Consultando tablas cruzadas en Visual Studio LightSwitch
LightSwitch es un RAD para hacer aplicaciones basadas en datos bien sea usando Silverlight, o HTML5. En cuestión de minutos podemos tener una aplicación de ese estilo desplegada y funcionando.
Cuando ya estamos desarrollando una app de cierta complejidad sin embargo, tarde o temprano llegamos a la necesidad de consultar data que se encuentra distribuida en varias tablas en un ambiente relacional.
Aunque LightSwitch provee un wizard de consultas básicas, éstas últimas requieren la escritura de código.
El código usado para escribir consultas complejas el LightSwitch no es más que LINQ.
En mi primer intento de hacer una consulta cruzada con LINQ dentro de LightSwitch sin embargo, a pesar de escribir instrucciones completamente válidas de acuerdo a la definición de LINQ, siempre obtenía un error emitido por LightSwitch, que además dado el mensaje presentado, no da muchas luces de qué es lo que está sucediendo:
Unable to create a constant value of type ‘LightSwitchApplication.MiTabla’. Only primitive types or enumeration types are supported in this context.
Pasé mucho tiempo experimentando qué podría ser hasta que descubrí que el error sucede cuando en el where de la instrucción LINQ ponemos dos tablas de la DB directamente. Entonces una solución que se me ocurrió y que mi buen amigo @jramirezdev me ayudó a implementar fue descargar primero unos datos, enumerarlos en una lista y luego con joins lograr el resultado. Sin embargo, dado que esto requiere dos llamados distintos a la DB y además resulta en una consulta LINQ difícil de leer, seguí “escarbando” y encontré una gran ayuda en el operador any de LINQ, que me permite seleccionar los datos que quiero sin tener que citar varias tablas dentro de LINQ, sino más bien explorando las propiedades de navegación del modelo de objetos.
En este artículo, he invitado precisamente a @jramirezdev, a quien agradezco el haber escrito el proceso de problema y solución en detalle; algo muy valioso para desarrolladores que estén teniendo este mismo problema:
...
Es muy común que en nuestras aplicaciones tengamos un origen de datos relacional y sobre este debamos realizar consultas en varias tablas para obtener la información que necesitamos. En LightSwitch las consultas simples se puede realizar con el diseñador integrado, pero si este no es el caso debemos recurrir a crear una consulta desde ceros, esto es, usar EntityFramework + LINQ.
Cuando utilizamos Entity Framework en LightSwitch nos encontraremos con algunas limitaciones, es por esto que debemos ser cuidadosos pues aunque la consulta LINQ compile no significa que al ejecutarla vayamos a obtener los resultados esperados.
Para entender esto veremos un caso específico donde realizaremos una consulta de tablas cruzadas con LINQ.
Aplicación: Crearemos una aplicación que permitirá registrar Usuarios, Tiendas y Facturas. Para esto debemos tener en cuenta que una factura será específica de un Usuario y una Tienda, esto significa que debe existir una relación entre Usuario y Tienda para finalmente registrar las facturas de ese usuario en esa tienda.
Modelo Relacional:
Problema: Se necesita generar un listado de todas las facturas que existan en las tiendas donde está registrado el usuario "Jorge Ramirez".
Para resolver este problema comenzaremos por crear una solución de LS, que se vera de la siguiente manera:
Aquí podemos observar que debajo de la tabla "Facturas" encontramos una consulta llamada "GetFacturasPorTiendasAsociadasAlCliente", es en ese archivo que escribiremos nuestra consulta LINQ.
Escribiré mi primer consulta de la siguiente manera.
partial void
GetFacturasPorTiendasAsociadasAlCliente_PreprocessQuery
(ref IQueryable<Factura> query)
{
//Consulta 1
var tiendas = from UsuarioTienda ut in UsuarioTiendas
where ut.Usuario.Nombre == "Jorge Ramirez"
select ut;
var facturas = from Factura factura in query
from UsuarioTienda ut in tiendas
where factura.UsuarioTiendas.Tienda.Id ==
ut.Tienda.Id
select factura;
query = facturas;
}
Semánticamente esta consulta es correcta, pero cuando la ejecutamos obtenemos el siguiente error:
Resultado Consulta 1: Error.
Este error aunque pareciera claro, no lo es, pues fácilmente nos confunde el pensar ¿Dónde creamos un Obj del tipo "UsuarioTienda"? Pues bueno, aquí es importante conocer el concepto de que son Propiedades de Navegación de EntityFramework.
Leyendo este error vemos que existe una limitación para acceder a las tablas relacionadas/cruzadas a través de las propiedades de navegación, lo cual nos indica que debemos considera una solución más del lado semántico de TSQL donde hacemos los JOINS de forma explícita.
partial void
GetFacturasPorTiendasAsociadasAlCliente_PreprocessQuery
(ref IQueryable<Factura> query)
{
//Consulta 2
var facturas = (from UsuarioTienda utienda in UsuarioTiendas
join Usuario usuario in Usuarios on
utienda.Usuario.Id equals usuario.Id
join Tienda tienda in Tiendas on
utienda.Tienda.Id equals tienda.Id
join Factura factura in query on tienda.Id
equals factura.UsuarioTiendas.Tienda.Id
where usuario.Nombre == "Jorge Ramirez"
select factura).AsQueryable();
query = facturas;
}
Resultado Consulta 2: Exito.
De esta forma EntityFramework ha logrado resolver las relaciones y entregarnos la data que necesitamos.
También podemos considerar otra posibilidad jugando con las propiedades de navegación y una expresión Lambda para que nuestra consulta sea más corta.
partial void
GetFacturasPorTiendasAsociadasAlCliente_PreprocessQuery
(ref IQueryable<Factura> query)
{
//Consulta 3
var facturas = from Factura factura in query
where factura.UsuarioTiendas
.Tienda.UsuarioTiendas.
Any(c => c.Usuario.Nombre == "Jorge Ramirez")
select factura;
query = facturas;
}
Resultado Consulta 3: Exito.
PROFILING
Finalmente como un proceso educativo, realizaremos uso de un perfilador (profiler) para ver como se comportan nuestras dos consultas exitosas.
Consulta 2: Reporte
Consula 2: Entity SQL Generado
SELECT TOP (45) [Project1].[Id]
AS [Id],
[Project1].[RowVersion]
AS [RowVersion],
[Project1].[Producto]
AS [Producto],
[Project1].[Valor]
AS [Valor],
[Project1].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Project1].[C1]
AS [C1],
[Project1].[C2]
AS [C2],
[Project1].[Id1]
AS [Id1],
[Project1].[RowVersion1]
AS [RowVersion1],
[Project1].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Project1].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario]
FROM (SELECT [Project1].[Id]
AS [Id],
[Project1].[RowVersion]
AS [RowVersion],
[Project1].[Producto]
AS [Producto],
[Project1].[Valor]
AS [Valor],
[Project1].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Project1].[Id1]
AS [Id1],
[Project1].[RowVersion1]
AS [RowVersion1],
[Project1].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Project1].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario],
[Project1].[C1]
AS [C1],
[Project1].[C2]
AS [C2],
row_number() OVER (ORDER BY [Project1].[Id] ASC)
AS [row_number]
FROM (SELECT [Extent1].[Id]
AS [Id],
[Extent1].[RowVersion]
AS [RowVersion],
[Extent1].[Producto]
AS [Producto],
[Extent1].[Valor]
AS [Valor],
[Extent1].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Extent2].[Id]
AS [Id1],
[Extent2].[RowVersion]
AS [RowVersion1],
[Extent2].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Extent2].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario],
N'UsuarioTiendas'
AS [C1],
N'UsuarioTiendas'
AS [C2]
FROM [dbo].[Facturas] AS [Extent1]
INNER JOIN [dbo].[UsuarioTiendas]
AS [Extent2]
ON [Extent1].[UsuarioTienda_Factura] =
[Extent2].[Id]) AS [Project1]) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Id] ASC
Consulta 3: Reporte
Consulta 3: Entity SQL Generado
SELECT TOP (45) [Project2].[Id]
AS [Id],
[Project2].[RowVersion]
AS [RowVersion],
[Project2].[Producto]
AS [Producto],
[Project2].[Valor]
AS [Valor],
[Project2].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Project2].[C1]
AS [C1],
[Project2].[C2]
AS [C2],
[Project2].[Id1]
AS [Id1],
[Project2].[RowVersion1]
AS [RowVersion1],
[Project2].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Project2].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario]
FROM (SELECT [Project2].[Id]
AS [Id],
[Project2].[RowVersion]
AS [RowVersion],
[Project2].[Producto]
AS [Producto],
[Project2].[Valor]
AS [Valor],
[Project2].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Project2].[Id1]
AS [Id1],
[Project2].[RowVersion1]
AS [RowVersion1],
[Project2].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Project2].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario],
[Project2].[C1]
AS [C1],
[Project2].[C2]
AS [C2],
row_number() OVER
(ORDER BY [Project2].[Id] ASC) AS [row_number]
FROM (SELECT [Extent1].[Id]
AS [Id],
[Extent1].[RowVersion]
AS [RowVersion],
[Extent1].[Producto]
AS [Producto],
[Extent1].[Valor]
AS [Valor],
[Extent1].[UsuarioTienda_Factura]
AS [UsuarioTienda_Factura],
[Extent2].[Id]
AS [Id1],
[Extent2].[RowVersion]
AS [RowVersion1],
[Extent2].[Tienda_UsuarioTienda]
AS [Tienda_UsuarioTienda],
[Extent2].[UsuarioTienda_Usuario]
AS [UsuarioTienda_Usuario],
N'UsuarioTiendas'
| AS [C1],
N'UsuarioTiendas'
AS [C2]
FROM [dbo].[Facturas] AS [Extent1]
INNER JOIN [dbo].[UsuarioTiendas]
AS [Extent2]
ON [Extent1].[UsuarioTienda_Factura] = [Extent2].[Id]
WHERE EXISTS (SELECT 1 AS [C1]
FROM [dbo].[UsuarioTiendas] AS [Extent3]
INNER JOIN [dbo].[UsuarioTiendas] AS [Extent4]
ON [Extent3].[Tienda_UsuarioTienda] =
[Extent4].[Tienda_UsuarioTienda]
INNER JOIN [dbo].[Usuarios] AS [Extent5]
ON [Extent4].[UsuarioTienda_Usuario] =
[Extent5].[Id]
WHERE ([Extent1].[UsuarioTienda_Factura] =
[Extent3].[Id])
AND (N'Jorge Ramirez' = [Extent5].[Nombre])))
AS [Project2]) AS [Project2]
WHERE [Project2].[row_number] > 0
ORDER BY [Project2].[Id] ASC
Nota: El perfilador usado fue Entity Framework Profiler, podemos aprender como usarlo Aquí.
Con esto hemos finalizado el proceso de creación y análisis de consultas LINQ to Entities en LightSwitch.
Muchas Gracias.
Jorge Ramírez ( @JramirezDev ): Es Tecnólogo de Sistemas y estudiante de Ingeniería de Sistemas, con 4 años de experiencia en desarrollo de BackEnd y FrontEnd usando .NET Framework; especialista en LINQ y WCF. Encuentren la versión original de este post y mucho contenido adicional muy interesante en su blog .