LINQ To SQL: Definiendo un Modelo de Entidades y operando con él!

Después de unos días ajetreados preparando el seminario sobre LINQ que próximamente impartiremos en el CIIN (de hecho justo hoy hemos acabado de hacer las últimas pruebas con LINQ To XML), en este post os voy a comentar algunos aspectos interesantes que hemos aprendido sobre LINQ To SQL. Pero, ¿Qué es LINQ To SQL? Según la documentación disponible en el sitio oficial del proyecto de LINQ, LINQ To SQL es el componente específico de LINQ que proporciona la infraestructura de runtime necesaria para utilizar datos relacionales como objetos y poder definir consultas sobre dichos objetos, es decir, habilita la consulta de contenedores de datos relacionales sin tener que abandonar la sintaxis o el entorno de tiempo de compilación.  Para hacer posible esto, LINQ To SQL se apoya en las siguientes características clave:


·         Las innovaciones del lenguaje (C# 3.0 y VB 9.0) que vimos en un post previo, así como en las características propias de LINQ (consultas integradas en el lenguaje).


·         Mapping del esquema de la BD en clases, propiedades, métodos, etc. De hecho, la correspondencia que LINQ To SQL hace entre los elementos de un esquema de una BD y los elementos correspondientes a nivel del CLR es el siguiente:



·         Persistencia, que habilita el control automático de cambios en la BD, y la actualización de datos a través de sentencias T-SQL.


·         Integración de la información del esquema de la BD en metadatos del CLR:las tablas se tratan como colecciones, los datos (columnas) son descritos en clases, la conexión a la BD y los resultados están tipados, etc. Cómo se comenta en el artículo El Proyecto LINQ (traducción de Octavio Hernández), esta integración es la que permite compilar las definiciones de tablas y vistas SQL en tipos del CLR para que puedan ser accedidas desde cualquier lenguaje.


En la práctica, LINQ To SQL  provee de los mecanismos necesarios para encapsular y utilizar en nuestras aplicaciones la definición (completa o no) de un cierto esquema relacional, y poder definir consultas LINQ sobre las clases mapeadas. El esquema de la arquitectura de LINQ To SQL nos permitirá comprender mejor las ideas expuestas:



Como se puede deducir, los puntos clave de la arquitectura de LINQ To SQL son los siguientes:



  • Consultas a nivel de aplicación integradas en el lenguaje.


    • Base de datos tipada en objetos: mapeo a través de atributos, devuelve objetos al realizar consultas SQL.

  • LINQ To SQL garantiza la persistencia, control de cambios y control de concurrencia.


    • LINQ To SQL traduce las consultas en SQL que se envía a la BD: las consultas no se ejecutan en memoria y  no se transmiten como IL al servidor BD.

Bueno, después de esta introducción “tan teórica”, es el momento de pasar a la acción. En los siguientes apartados trataré de cubrir aspectos relativos al mapeado de objetos de acuerdo a un esquema relacional existente, y como definir operaciones sobre dichos objetos  que luego se traducirán en la BD a las correspondientes sentencias T-SQL. Empecemos.


Nota: Los requisitos necesarios para probar LINQ To SQL son los que ya comentamos en el primer post de LINQ.


Creación del modelo de objetos


El modelo de objetos creado se basa en una sencilla base de datos formada por dos tablas relacionadas: Md_Clientes y Md_Vehiculos.


Como hemos comentado, el mapeo de elementos de la BD se basa en definir las clases, propiedades y métodos necesarios para poder interactuar con ellos del mismo modo que con otros tipos del CLR. LINQ To SQL nos da tres posibilidades para realizar este mapeo:


(i)                  Utilizando la herramienta SQL Metal la cuál a partir del archivo .mdf de nuestra BD genera un archivo .cs con todos los elementos de la BD mapeados.


Utilizando LINQ To SQL OR Designer que es integrado en VS 2005 después de instalar el LINQ Preview. En este caso, el mapeo es todavía más sencillo puesto que sólo tenemos que arrastrar las tablas que nos interesen de nuestro esquema  de datos a la superficie de diseño (seguro que esto le suena a más de uno). Si nos vamos a la vista de código (después de grabar :P), veremos que se nos han generado todos los elementos de la BD que hemos situado en la superficie de diseño.



(i)                  Hacer el mapeo “a mano”, es decir, definir adecuadamente en código las clases y propiedades que representan las tablas y campos a mapear.


¿Qué método es más adecuado para definir el modelo de objetos? Pues básicamente, y desde mi punto de vista, está claro que las opciones (i) e (ii) son más rápidas, pues hacen todo el trabajo por nosotros (aparte de que no se dejan cosas en el tintero) y nos mapean todas las tablas con sus columnas en un archivo .cs. Sin embargo, perdemos un poco de flexibilidad puesto que el modelo de objetos se define de acuerdo a la descripción fiel de la BD y no con respecto a necesidades de negocio concretas. En cambio, el método (iii) es más flexible ya que nos permite definir “entidades” de acuerdo al proceso de negocio concreto, de manera que nuestras clases estarán formadas por aquellas propiedades que realmente son interesantes para nosotros (y no por todas las columnas de la correspondiente tabla en la BD). Evidentemente el método (iii) implica más esfuerzo de desarrollo y conocer en mayor detalle como mapear tablas en objetos en LINQ. Nosotros vamos a ir por la opción (iii) para tener un mayor control de lo que estamos haciendo.


Dentro del proyecto de LINQ que hayamos creado, vamos a definir una clase que represente a la entidad Clientes. La definición de esta clase la vamos a basar en la tabla Md_Clientes de nuestro esquema relaciona.  Esta clase es una representación lógica y tipada de la tabla en la BD física, y actúa a todos los efectos como un proxy para realizar consultas fuertemente tipadas.







     [Table(Name=”Md_Clientes”)]


        public class Clientes


        {


            [Column (Id=true, DBType=”varchar”)]


            public string ID_Cliente;


 


            private string _NombreCliente;


            [Column(Storage=”_NombreCliente”, DBType=”varchar”)]


            public string NombreCliente


            {


 


                get {return this._NombreCliente;}


                set {this._NombreCliente=value;}


 


            }


 


            private string _CiudadCliente;


            [Column(Storage=”_CiudadCliente”, DBType=”varchar”)]


            public string CiudadCliente


            {


 


                get {return this._CiudadCliente;}


                set {this._CiudadCliente=value;}


 


            }


 


        }


Como vemos en el código anterior, el mapeo de la entidad Clientes a la tabla Md_Clientes  se basa en utilizar los atributos adecuados:


·         El atributo Table que define cuál es la tabla de la BD con la que se encuentra vinculada la entidad Clientes. Este atributo prese


·         El atributo Column que permite especificar la correspondencia de los miembros de la clase con las correspondientes columnas de la tabla física. Además, este atributo lleva asociados una serie de parámetros que nos permiten especificar si el miembro de la clase se corresponde con la primary key de la tabla de la BD (parámetro Id), el tipo de dato parámetro (DBType, si el campo es autogenerado (parámetro autogen), etc.


Una vez que hemos creado la primera entidad del modelo de objetos, nos falta definir el canal que nos permita traer objetos desde la BD y enviar cambios a la BD. Este canal es lo que en LINQ se conoce como DataContext, que se encarga de traducir las peticiones de objetos en consultas SQL (ver el esquema de la arquitectura de LINQ) y devolver los resultados como objetos. El objeto DataContext se puede especificar de dos formas:


·         Definiendo una instancia del mismo y pasando como parámetro la cadena de conexión de la BD. Este método implica que para definir consultas contra la BD tengamos que utilizar el método GetTable.







DataContext BD=new DataContext(@”C:Program FilesLINQ PreviewDataCLIENTES.mdf”);


Table<Clientes> Customers=BD.GetTable<Clientes>();


Nota: Como vemos, como cadena de conexión estamos especificando un path físico en el que tenemos almacenada la BD. DataContext admite otras tres sobrecargas (una de ellas es la típica cadena de conexión de ADO.NET)  en la definición de la cadena de conexión.


·         Definiendo el objeto DataContext de modo fuertemente tipado, lo que facilita la definición de las consultas en la BD puesto que no necesitamos utilizar GetTable tras crear una instancia de dicho objeto. Esta es la opción que vamos a utilizar. Para ello, tenemos que crear una clase que herede de DataContext.







    class Program


    {


        //BD CLIENTES


        public class CLIENTES: DataContext


        {


            public Table<Clientes> Customers;


            public CLIENTES(string connection): base(connection){}


        }


Una vez que hemos definido la primera entidad del modelo y tipado el correspondiente DataContext, ya podemos empezar a realizar consultas contra el modelo de objetos.


Realizando consultas contra el modelo de objetos definido


La filosofía de la realización de consultas es la que ya vimos cuando se definen consultas sobre objetos de memoria.







//Conexión Fuertemente Tipada


CLIENTES BD2=new CLIENTES(@”C:Program FilesLINQ PreviewDataCLIENTES.mdf”);


//Clientes de Madrid


BD2.Log=Console.Out;


            var MisClientes =


                    from c in BD2.Customers


                    where c.CiudadCliente==”Madrid”


                    select c;


            Console.WriteLine(“*********************Consulta estándar*********************”);


            foreach (var cliente in MisClientes)


            {


                Console.WriteLine(“ID: {0},Nombre: {1},Ciudad: {2}”,


                        cliente.ID_Cliente,cliente.NombreCliente,cliente.CiudadCliente);


            }


            Console.ReadLine();


La salida por pantalla que se obtiene nos muestra la sentencia T-SQL que LINQ To SQL envía a la BD (esta sentencia se captura mediante BD2.Log=Console.Out) y el resultado de la consulta, que se ejecuta en el momento en que se define la correspondiente iteración.




Vamos a definir ahora una consulta más compleja que permita incluir el uso de Joins a partir de que conocemos que Md_Clientes y Md_Vehicles están relacionadas mediante a través del campo ID_Cliente. Antes de realizar la consulta, tendríamos que definir una clase Vehiculos que realice el mapeo correspondiente con la tabla Md_Vehiculos. La filosofía es la misma explicada para la clase Clientes, por lo que no os reproduzco el código. Si nos centramos en la definición de la consulta, supongamos que queremos obtener para cada cliente de la BD la marca de su coche, tendríamos que definir una consulta como la siguiente:







            var ClientesMarcaCoche=


                from c in BD2.Customers


                join ve in BD2.Vehicles on


                c.ID_Cliente equals ve.ID_Cliente


                orderby c.CiudadCliente ascending


                select new{NIF=c.ID_Cliente,


                    Nombre=c.NombreCliente,Coche=ve.MarcaVehiculo};


            Console.WriteLine(“*********************Consulta con joins*********************”);


            Console.WriteLine(“n”);


            foreach(var cliente in ClientesMarcaCoche)


            {


                Console.WriteLine(“NIF: {0}, Nombre: {1},Coche: {2} “,


                    cliente.NIF,cliente.Nombre,cliente.Coche);


            }


            Console.ReadLine();


Aparte del uso del join, en la consulta anterior es destacable el uso del operador orderby para obtener los resultados ordenados según un cierto criterio y el hecho de que en lugar de devolver un objeto completo como hicimos en la consulta anterior estemos devolviendo una colección arbitraria utilizando para ello un tipo anónimo. La salida que se obtiene por pantalla es la siguiente:



Hasta ahora hemos visto como definir un modelo de objetos a partir del esquema de una BD y como realizar consultas contra ese modelo de objetos, consultas que LINQ To SQL traduce a sentencias T-SQL que se envían a la BD. En los siguientes puntos comentaré como realizar operaciones habituales contra una BD: inserción de registros, actualización de datos, borrado de registros y ejecución de sentencias T-SQL (incluidos procedimientos almacenados).


Realizando operaciones contra la BD


Lo primero que vamos a hacer es añadir un registro en la tabla Md_Clientes de la BD. La clave de creación de registros está en crear una instancia de la entidad Clientes (con el operador new()) que mapea la correspondiente tabla de la BD y en el método Add() que nos permite añadir la instancia creada al objeto equivalente del DataContext que a su vez se encargará de generar la correspondiente sentencia SQL de inserción para poder reflejar el registro en la BD. Así, para crear un nuevo cliente en la BD el código necesario es el siguiente:







            Console.WriteLine(“******************Operando con Entidades******************”);


 


            //Nuevo registro


            Console.WriteLine(“Insercción”);


            Clientes nuevoCliente= new Clientes();


            nuevoCliente.ID_Cliente=”72678934C”;


            nuevoCliente.NombreCliente=”Ángel Álvarez”;


            nuevoCliente.CiudadCliente=”Ponferrada”;


            //Añadimos el registro


            BD2.Customers.Add(nuevoCliente);


            //Forzamos que los datos se vuelquen en la BD


            BD2.SubmitChanges();


            //Comprobamos que el registro se ha añadido


            var ClienteAñadido=


                (from c in BD2.Customers


                 where c.ID_Cliente==”72678934C”


                 select c).First();


             Console.WriteLine(“El cliente con NIF {0}, Nombre {1} ha sido añadido”,


                            ClienteAñadido.ID_Cliente,ClienteAñadido.NombreCliente);


             Console.ReadLine();


 Como vemos, añadir un nuevo registro es realmente sencillo. Basta con crear una nueva instancia de la entidad Clientes, completar sus propiedades y añadir la instancia en el DataContext para que los datos se vuelquen en memoria. Además, para que los cambios sean efectivos tenemos que llamar al método SubmitChanges() que se encarga de iniciar una transacción (por defecto las transacciones en LINQ To SQL son de tipo implicitico para las operaciones de inserción y borrado de datos, la actualización de datos en cambio es automática y no se hace en el contexto de una transacción). Después de llevar los cambios a la BD, comprobamos que estos son efectivos definiendo una consulta en la que filtremos por ID_Cliente y a la que le aplicamos el método First() que retorna un objeto en lugar de una colección de objetos. La salida por pantalla que se obtiene es la siguiente:



Actualizar o borrar un registro es igualmente sencillo en la BD con LINQ es también bastante sencillo. Para actualizar un registro basta con recuperar el registro o registros a actualizar, cambiar sus propiedades y registrar los cambios en la BD con SubmitChanges(). Y para borrar el registro, utilizamos el método Remove() para borrar el registro o registros recuperados desde la BD y que nos interesa eliminar. El código que permite actualizar y borrar el cliente que hemos añadido antes es el siguiente:







            //Actualización del registro           


            Console.WriteLine(“Actualización”);


            ClienteAñadido.NombreCliente=”Ángel Fernández”;


            ClienteAñadido.CiudadCliente=”La Bañeza”;


 


            Console.WriteLine(“El cliente con NIF {0}, ha sido actualizado con los siguientes datos:n  


            Nombre {1} y Ciuad {2}”,  ClienteAñadido.ID_Cliente,


           ClienteAñadido.NombreCliente, ClienteAñadido.CiudadCliente);


            Console.ReadLine();


 


            //Borrado de registro


            Console.WriteLine(“Borrado”);


            BD2.Customers.Remove(ClienteAñadido);


            BD2.SubmitChanges();


 


            foreach(var cliente in BD2.Customers)


            {


                Console.WriteLine(“NIF: {0}, Nombre: {1},Coche: {2} “,


                    cliente.ID_Cliente,cliente.NombreCliente,cliente.CiudadCliente);


            }


            Console.ReadLine();


 La salida por pantalla correspondiente es la siguiente:



Utilizando comandos T-SQL en LINQ To SQL


Para acabar con este post, os voy a mostrar como LINQ To SQL permite definir y utilizar comandos SQL o procedimientos almacenados (SP’s) en el modelo de entidades definido. Como os podréis imaginar, el uso de estos comandos y/o procedimientos almacenados pasa por definir los correspondientes métodos en la clase que definir nuestro DataContext y decorarlos de manera adecuada.  Lo primero que vamos a hacer es definir estos métodos en la clase comentada. Como veréis en el código, he añadido dos:


·         El método Actualizar que nos permitirá actualizar un registro de la BD. Como veis, la clave de este método está en decorarlo con el atributo Update y luego definir en su cuerpo el uso del método ExecuteCommand (propio de la clase DataContext). Como se ve en el código, este método recibe como argumentos la sentencia T-SQL y los parámetros necesarios para ejecutarla de manera adecuada.


·         El método ExtraerClientesVehiculosByCiudad, que devuelve el resultado de ejecutar un cierto SP. La clave de este método está en decorarlo con el atributo StoreProcedure, en definir adecuadamente el tipo de dato a devolver, y en pasarle adecuadamente los parámetros que se necesiten para ejecutar el SP. Como veis, utilizar SP’s en nuestro modelo de entidades es más complicado que utilizar simples sentencias T-SQL, veamos el código necesario para definir los dos métodos.







            //Método que ejecuta un comando T-SQL


            [UpdateMethod]


            public void Actualizar(Clientes Cliente)


            {


                Console.WriteLine(“Actualización de datos”);


               


                ExecuteCommand(“UPDATE [Md_Clientes] set [CiudadCliente]={0} WHERE ID_Cliente={1}”,


                                Cliente.CiudadCliente,Cliente.ID_Cliente);


            }


             //Método que ejecuta un SP complejo


             [StoredProcedure (Name=”ExtraerClientesVehiculosByCiudad”)]


            public StoredProcedureResult<ClientesVehiculos>


                 ExtraerClientesVehiculosByCiudad(


                  [Parameter(Name=”CiudadCliente”,DBType=”NVarChar(50)”)] string CiudadCliente)


             {


                 return this.ExecuteStoredProcedure<ClientesVehiculos>(


                     ((MethodInfo)(MethodInfo.GetCurrentMethod())),CiudadCliente);


             }


Como vemos en el código anterior, utilizar un SP es más complejo, por lo que merece la pena explicar un poco más en detalle cómo se define el método:


·         Decorar el método con el atributo StoredProcedure, además especificaremos el parámetro Name en el que indicamos el nombre del SP a ejecutar.







CREATE PROCEDURE ExtraerClientesVehiculosByCiudad


 @CiudadCliente NVARCHAR(50)


AS


                        select C.ID_Cliente, C.CiudadCliente, V.MarcaVehiculo


                                   from Md_Clientes C


                                   left join Md_Vehiculos V on


                                   C.ID_Cliente=V.ID_Cliente


                                     where CiudadCliente=@CiudadCliente


                                     order by C.CiudadCliente  


GO



 ·         En la declaración del método, vemos que es de un tipo especial: StoredProcedureResult<T>, que hereda de las clases IEnumerable<T> y StoredProcedureResult.  Por lo tanto, implementa una interfaz de tipo IEnumerable y necesitaremos definir la correspondiente clase que nos de la descripción de la colección. Lógicamente, la clase que implementemos se tendrá que corresponder con lo que esperamos que nos devuelva el SP.







public class ClientesVehiculos


        {


            private string _ID_Cliente;


           


            public string ID_Cliente


            {


                get {return this._ID_Cliente;}


                set {this._ID_Cliente=value;}


            }


 


            private string _CiudadCliente;


           


            public string CiudadCliente


            {


                get {return this._CiudadCliente;}


                set {this._CiudadCliente=value;}


            }


 


            private string _MarcaVehiculo;


           


            public string MarcaVehiculo


            {


                get {return this._MarcaVehiculo;}


                set {this._MarcaVehiculo=value;}


            }


        }


Cómo parámetros del método, definiremos justamente los parámetros que espera el SP y decorándolos adecuadamente para que sean del tipo que esperado. En nuestro caso, el SP necesita un único parámetro, @CiudadCliente, por lo que en código lo especificamos mediante el parámetro CiudadCliente convenientemente decorado con el atriburo parameter 


( [Parameter(Name=”CiudadCliente”,DBType=”NVarChar(50)”)] string CiudadCliente)


 


·         Lo siguiente que hacemos es ejecutar el procedimiento y devolver el resultado de la ejecución. Para ello llamamos al método ExecuteStoredProcedure de la clase DataContext, el cuál recibe como parámetros un tipo MethodInfo y el parámetro o parámetros que necesita el SP para ser ejecutado correctamente.


Nota: Por supuesto, se pueden definir métodos que ejecuten un procedimiento almacenado y devuelvan tipos simples (como un entero) o tipos más complejos en los que a priori no sepamos el formato (en cuanto a número de columnas) que nos devuelva el SP (en este caso se utiliza un tipo shape).


Una vez que hemos definido los métodos necesarios para habilitar el uso de sentencias T-SQL y SP’s en nuestro código, utilizarlos es sencillo. El código necesario para utilizar los métodos anteriores es el siguiente:







            Console.WriteLine(“****************Uso de Comandos T-SQL****************”);


            Console.WriteLine(“Sentencia T-SQL”);


              var ClienteExistente=


                (from c in BD2.Customers


                 where c.ID_Cliente==”71505286B”


                 select c).First();


 


            Console.WriteLine(“Datos antes de actualizar ID={0},Ciudad={1}”,


                ClienteExistente.ID_Cliente,ClienteExistente.CiudadCliente);          


            ClienteExistente.CiudadCliente=”Oviedo”;


            BD2.Actualizar(ClienteExistente);


            Console.WriteLine(“Datos después de actualizar ID={0},Ciudad={1}”,


                ClienteExistente.ID_Cliente,ClienteExistente.CiudadCliente);


            Console.WriteLine(“n”);


           


            Console.WriteLine(“****************Procedimiento almacenado****************”);


            StoredProcedureResult<ClientesVehiculos> resultados =


                BD2.ExtraerClientesVehiculosByCiudad(“Madrid”);


            foreach(ClientesVehiculos resultado in resultados)


            {


                Console.WriteLine(“ID Cliente: {0},Ciudad Cliente: {1},Marca Vehiculo:  


                                           {2}”,resultado.ID_Cliente,resultado.CiudadCliente,resultado.MarcaVehiculo);


            }


            Console.ReadLine();


 La salida por pantalla para este caso es la siguiente:



Y hasta aquí (que no es poco), lo que os quería contar sobre LINQ To SQL. Espero vuestros comentarios y que os hayan resultado interesantes los aspectos cubiertos en el post. Os dejo para que os podáis descargar y probar el código del proyecto con todas las funcionalidades de LINQ To SQL y el script de creación de la BD (tablas, datos y procedimientos almacenados incluidos) que he usado para las pruebas. Os animo a que probéis LINQ , a mi me ha impresionado bastante y resulta muy natural utilizarlo si estás familiarizado con T-SQL. En otro post os contaré de que va LINQ To XML.

Publicado por

Juan Carlos González

Juan Carlos es Ingeniero de Telecomunicaciones por la Universidad de Valladolid y Diplomado en Ciencias Empresariales por la Universidad Oberta de Catalunya (UOC). Cuenta con más de 12 años de experiencia en tecnologías y plataformas de Microsoft diversas (SQL Server, Visual Studio, .NET Framework, etc.), aunque su trabajo diario gira en torno a SharePoint & Office 365. Juan Carlos es MVP de Office Servers & Services desde 2015 (anteriormente fue reconocido por Microsoft como MVP de Office 365 y MVP de SharePoint Server desde 2008 hasta 2015), coordinador del grupo de usuarios .NET de Cantabria (Nuberos.Net, www.nuberos.es), co-fundador y coordinador del Grupo de Usuarios de SharePoint de España (SUGES, www.suges.es), así como co-director de la revista gratuita en castellano sobre SharePoint CompartiMOSS (www.compartimoss.com). Hasta la fecha, ha publicado 8 libros sobre SharePoint & Office 365 y varios artículos en castellano y en inglés sobre ambas plataformas.

11 comentarios en “LINQ To SQL: Definiendo un Modelo de Entidades y operando con él!”

  1. Hola Octavio!
    Pues sí, tenemos que contar las prestaciones de LINQ, porque va a suponer un cambio aunque no tan radical como muchos puedan pensar. A mi me está gustando mucho, y las posibilades que va a dar son muy grandes.
    Un saludo
    JC

  2. HOla como estas no tendras un ejemplo de Consulta con codigo fuente , con la tabla Customers de la BD Northwind, me gustaria que fuera sobre winsforms todos los ejemplos que he visto son sobre la linea de comandos y eso me enoja, yo vengo de programar de Delphi y no se hace tanto rollo para poder hacer una consulta es muy muy sencillo, en C# se le da muchas vueltas para presentar un dato. saludos cordiales.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *