Continuando con el artículo anterior sobre Linq to SQL en la Dal, donde explica el rol que siguen cumpliendo los Stored Procedures. Razón por la cual esta entrada se llama "Linq to SQL y Stored Procedures" y no "Linq to SQL vs. Stored Procedures").
Las razones más importantes son:
- Las consultas Linq to SQL se traducen en queries SQL, no son procedimientos: con Linq to SQL no podemos aprovechar tablas temporales, estructuras de control (if-else, loops, cursores), devolver múltiples resultsets, operaciones por lotes (UPDATE, DELETE), etc.
- Performance, desde hace varias generaciones SQL Server sabe como optimizar código Transact-SQL en Stored Procedures. Más aún, la traducción de Linq a SQL, aunque evolucione con el .Net Framework, no siempre genera los mejores resultados, existirán los casos en que necesitemos escribir nuestro propio Transact-SQL.
- Seguridad, son limitados los escenarios en que nuestra aplicación tiene acceso total a las tablas de la base de datos. Es habitual que nuestro DBA, por razones de seguridad y desacoplamiento entre modelos de datos, limite el acceso al SQL Server a la ejecución de Stored Procedures.
Llegamos a la conclusión de que, en general, no podemos prescindir de los SP, la buena noticia es:
Linq to SQL incorpora un manejo simple de SP que permite realizar cualquier tipo de operaciones sobre los datos vía SP.
Pero la mala noticia es:
El O/R Designer con el cual editamos gráficamente nuestros Dbml, no soporta completamente el uso de SP.
Mientras esperamos que la mala noticia se solucione con actualizaciones del VS2008, en algunos puntos tendremos que tocar algo de código manualmente.
Generación de wrappers
Primero vamos a incorporar los SP que queremos invocar a nuestro dbml, para esto basta con arrastrarlos a la zona de métodos (a la derecha en el O/R Designer), esto generará en nuestro DataContext un método para cada SP, con el mismo nombre y lista de parámetros.
Ahora podemos invocar estos SP con intellisense, comprobación de tipos, y control sobre cada invocación (detección temprana de errores, mayor mantenibilidad y desacoplamiento).
Esto genera también para cada SP una clase que representa el tipo de resultado detectado, una clase con propiedades públicas análogas a las columnas del resultado devuelto, esto le permite a Linq to SQL devolver de la invocación de estos métodos wrappers una colección de estos objetos.
El nombre que tienen estas clases por defecto será "NombreDelStoredProcedureResult", y esto es invisible desde el O/R Designer, por lo tanto si queremos cambiar el nombre de la clase de resultados (por ej: ListarClientesPorZonaResult a Cliente), será necesario meterse en el Designer.cs asociado al dbml, cortar esta clase, pegarla en otro archivo (por ejemplo junto con el resto del modelo de datos), y aquí cambiarle el nombre (el código autogenerado se pisa cada vez que tocamos el dbml).
Ahora resta actualizar el wrapper del SP con el nombre del tipo devuelto, desafortunadamente el O/R Designer solo nos permite especificar como tipo devuelto una entidad que se encuentre visible en el mapa dbml, lo cual no siempre es posible si queremos compartir estas entidades entre otros dbml, o mover las entidades a un assembly separado. Inclusive tampoco es posible con el O/R Designer especificar múltiples resultados (típicamente utilizado para devolver entidades cabecera-detalle).
Si podemos hacer esto "tocando" los wrappers auto-generados, para lo cual tenemos 2 opciones:
- Una vez generados, mover los wrappers a una partial class del DataContext, y modificando manualmente los atributos que decoran los wrappers, especificar tipos devueltos, inclusive múltiples resultsets. Ejemplo:
[Function(Name = "dbo.LeerClienteConPedidos")]
[ResultType(typeof(Cliente))]
[ResultType(typeof(Pedidos))]
public IMultipleResults LeerClienteConPedidos([Parameter(Name = "IdCliente", DbType = "Int")] System.Nullable<int> idCliente)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), idCliente);
return (IMultipleResults)result.ReturnValue;
}
esto indica que el SP devuelve 2 resultsets, el primero con Clientes, y el segundo con Pedidos.
- Reemplazar el generador de código por defecto MSLinqToSQLGenerator, por uno que genere los wrappers "a gusto", lo cual afortunadamente resulta más simple de lo que parece. Nuestro generador de código ULinqGen podría extenderse para realizar esto.
Aplicar cambios
Una vez obtenidas colecciones de entidades existentes en la base de datos, por defecto, Linq to SQL activa para todas ellas un control de cambios (cuando estos objetos notifican sus modificaciones mediante eventos). Cuando se detectan modificaciones, se mantiene en el DataContext un "change set", que permite luego aplicar todos esos cambios en la base de datos.
Cuando se llama al método SubmitChanges() del DataContext, Linq to SQL genera y ejecuta una serie de comandos UPDATE, INSERT y DELETE necesarios para reflejar los cambios en la base de datos.
Sin embargo es común que necesitemos realizar algún tipo de verificación o auditoría al momento de realizar estas operaciones. Es por esto que desde el O/R Designer es posible configurar el "Behavior" de una entidad, defiendo el método a invocar para realizar un UPDATE, un DELETE o un INSERT de esta entidad, en lugar del SQL auto-generado.
Carga de entidades asociadas
Por último, existe un uso de estos wrappers de SP un poco más desconocido, revelado por Dinesh Kulkarni, en su blog.
Cuando existen foreign keys entre 2 tablas del SQL, en el dbml se generan asociaciones, que se ven como colecciones de detalle (EntitySet) dentro las entidades maestro. Ej:
Cliente c = ...
for each (Pedido p in c.Pedidos)
{
...
}
La forma en que se obtienen los Pedidos para un Cliente, es por defecto mediante un query Linq generado a partir de la foreign key, sin embargo es posible definir esto mediante un método propio, definido en una partial class del DataContext.
Ejemplo:
public partial class VentasDataContext {
public IEnumerable<Pedido> LoadPedidos(Cliente c) {
return this.LeerPedidosPorCliente(c.IdCliente);
}
public Cliente LoadCliente(Pedido p) {
return this.LeerCliente(p.IdCliente);
}
}El método LoadPedidos es llamado al cargar la propiedad Pedidos en un Cliente, y el segundo al cargar la propiedad Cliente en un pedido.
Esto funciona tanto para carga deferida (deferred loading) como para carga inmediata (utilizando el método LoadWith<T>()).
Conclusiones
Teniendo estos features podemos decir que el uso Linq to SQL para invocar Stored Procedures, nos atrae por estas ventajas:
- Integración con Linq to SQL (a través de compartir un mismo modelo de datos).
- Wrappers para invocación de SP, con las ventajas de tener comprobación de tipos en los parámetros, intellisense, y control sobre las invocaciones.
- Materialización de objetos (POCO) para los resultados (en lugar de sólo DataSets).
y preocupan estas desventajas:
- Una invocación a Stored Procedures un poco oscura, que no permite demasiada personalización. Por ejemplo no es posible interceptar errores, tocar la lista de parámetros, o forzar reintentos, etc. de forma global.
- Solo funciona (en la versión actual) con MS SQL Server.
En el artículo anterior, José explica con más detalle razones que justifican mantener un mecanismo separado para las invocaciones a procedimientos almacenados.
Por lo tanto, exploramos la posibilidad de tener las ventajas mencionadas, sin las desventajas mencionadas.
Para esto trabajamos en el desarrollo de estas herramientas:
- Un generador de wrappers, esto nos permitiría solucionar el problema de los tipos de retorno custom y/o múltiples, y la posibilidad elegir el mecanismo de invocación.
- Un materializador de objetos que proyecte colecciones de objetos POCO de cualquier DataReader, permitiéndonos invocar SP con ADO.Net puro, EnterpriseLibraries, etc. En nuestro blog en inglés escribí un articulo con algunos prototipos de materialización de objetos
Hasta pronto,
