Introducción
Se continua con la explicación que comenzó en:
n-Layer - SchoolManager - Herencia y navegación de entidades relacionadas (1/2)
En esta ocasión nos centraremos en como recuperar entidades relacionadas o asociadas, usando ado.net
Vamos a enfocarnos en el vinculo que tiene un un instructores y sus cursos.
En el articulo anterior se pudo comprender como se persisten las relaciones en conjunto con los concepto de herencia, pero en este caso vamos a recuperar la información de una entidad y sus relaciones todo en la misma operación.
Seguramente seria una buena idea crear dos funcionalidades, una que permita recuperar la entidad sin sus relaciones GetByKey(), o sea la entidad pura, y otro método en donde una única query recupere y arme la estructura jerárquica GetByKeyWithRelations().
Recuperar entidad y relaciones
Para poder llevar a cabo esta tarea se va a necesitar de la ayuda de linq, este permitirá trabajar una entidad simple para darle estructura.
Comenzaremos definiendo una entidad plana que contenga las propiedades tanto del Instructor como del curso.
public class InstructorComposed { public int PersonID { get; set; } public string LastName { get; set; } public string FirstName { get; set; } //Fecha de contratación public DateTime? HireDate { get; set; } public string Location { get; set; } public int CourseID { get; set; } public string Title { get; set; } }
Pero además se deberá tener la entidad que nos interesa devolver como respuesta, esta si tiene estructura, o sea una lista de cursos
public abstract class PersonEntity { public int PersonID {get; set;} public string LastName {get; set;} public string FirstName {get; set;} } public class InstructorEntity : PersonEntity { public InstructorEntity() { this.Courses = new List<CourseEntity>(); } //Fecha de contratación public DateTime? HireDate { get; set; } public string Location { get; set; } public List<CourseEntity> Courses { get; set; } } public class CourseEntity { public int CourseID { get; set; } public string Title { get; set; } }
El reto será lograr convertir una entidad de propiedades simples a una de propiedades complejas. Seguramente se preguntaran porque no se recupera esto directo con una query, el tema es que una consulta no devuelve como resultado registros con estructuras anidadas, sino que solo nos proporciona dos dimensiones, filas y columnas.
Se ha creado el método GetByKeyWithRelations() para obtener la entidad Instructor con sus cursos relacionados:
/// <summary> /// Devuelve el instructor incluyendo las relaciones con las demas entidades /// </summary> /// <param name="id"></param> /// <returns></returns> public static InstructorEntity GetByKeyWithRelations(int id) { InstructorEntity item = null; using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString())) { conn.Open(); string query = @"SELECT P.PersonID, P.LastName, P.FirstName, P.HireDate, P.EnrollmentDate, OA.Location, C.CourseID, C.Title FROM Person P INNER JOIN OfficeAssignment OA ON P.PersonID = OA.InstructorID INNER JOIN CourseInstructor CI ON CI.PersonID = P.PersonID LEFT JOIN Course C ON C.CourseID = CI.CourseID WHERE P.PersonID = @id ORDER BY P.PersonID"; SqlCommand cmd = new SqlCommand(query, conn); cmd.Parameters.Add("@id", SqlDbType.Int).Value = id; SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); item = ConvertInstructorWithRelations(reader); } return item; }
Es importante destacar como en la consulta sql hace uso de un LEFT JOIN para recuperar todos los datos del instructor tengan o no cursos asociados, el SELECT incluirá los campos de la entidad Instructor y también los del Curso, serán coincidentes con la definición de la clase InstructorComposed.
El siguiente paso será procesar los datos y darles formato:
private static InstructorEntity ConvertInstructorWithRelations(IDataReader reader) { List<InstructorComposed> list = new List<InstructorComposed>(); while(reader.Read()) { InstructorComposed item = new InstructorComposed(){ PersonID = Convert.ToInt32(reader["PersonID"]), LastName = Convert.ToString(reader["LastName"]), FirstName = Convert.ToString(reader["FirstName"]), HireDate = reader["HireDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(reader["HireDate"]), Location = Convert.ToString(reader["Location"]), CourseID = Convert.ToInt32(reader["CourseID"]), Title = Convert.ToString(reader["Title"]) }; list.Add(item); } if (list.Count == 0) return null; InstructorEntity instructor = (from item in list group item by item.PersonID into g select new InstructorEntity() { PersonID = g.Key, LastName = g.First().LastName, FirstName = g.First().FirstName, HireDate = g.First().HireDate, Location = g.First().Location, Courses = g.Select(x=> new CourseEntity() { CourseID = x.CourseID, Title = x.Title }).ToList() }).First(); return instructor; }
La primer parte es bien conocida, se convierte el reader volcando los datos de los campos a la instancia de la entidad. Pero la segunda parte es la mas interesante, porque es allí donde mediante la utilización de linq que damos estructura al objeto plano que se recupera de la query.
En este caso se hace uso de la capacidad de agrupar que brinda linq para poder juntar todos los cursos que pertenecen al instructor. En el ejemplo solo se necesito recupera un único instructor pero podría haberse utilizado la misma técnica para trabajar una colección de estos.
Código
El código se encuentra en el articulo anterior.