Conviviendo con SqlQuery

Aunque trabajemos con un ORM, a veces y por exigencias del guión, nos toca ejecutar alguna sentencia SQL en bruto y materializar el resultado en objetos. Si estamos trabajando con Entity Framework, tenemos a nuestra disposición el método SqlQuery.

La verdad es que su utilización es muy sencilla, simplemente debemos ejecutar una SQL cuyos resultados sean “mapeables” a un objeto destino (la clase de este objeto puede ser una entidad del contexto o cualquier otra clase).

Pero como siempre, el diablo está en los detalles.

Por ejemplo, partamos de una sencilla base de datos con una relación 1:1 entre un Producto y sus Propiedades, donde las Propiedades pueden o no existir.

A continuación, tenemos una clase que es la unión de ambas tablas que será el destino de la materialización:

class ProductoCompleto

{

    public int IdProducto { get; set; }

    public string Descripcion { get; set; }

    public bool Novedad { get; set; }

    public decimal Precio { get; set; }

}

Y ahora el código, simplemente recuperamos un producto con el identificador 1… producto que no tiene Propiedades, por ahí vendrán los errores y las soluciones…

using (var context = new ProductosEntities())

{

    var sql = @"

        SELECT  Productos.IdProducto ,

                Descripcion ,

                PropiedadesProducto.IdProducto ,

                Novedad ,

                Precio

        FROM    Productos

                LEFT JOIN PropiedadesProducto

                ON Productos.IdProducto = PropiedadesProducto.IdProducto

        WHERE   Productos.IdProducto = {0}";

    var parametros = new object[] { "1" };

    var productos =

        context.Database.SqlQuery<ProductoCompleto>(sql, parametros).ToList();

}

Pues algo tan sencillo como esto, ya nos lanza un primer error donde dice que no puede castear el tipo Boolean porque es nulo. Es decir, el campo PropiedadesProducto.Novedad es del tipo bit, pero al haber hecho un LEFT JOIN y no haber registro no puede asignarle un null a ProductoCompleto.Novedad.

¿Cómo solucionarlo?

Podríamos hacer que la propiedad ProductoCompleto.Novedad fuera nullable, con bool? y el problema estaría resuelto. Sin embargo yo quiero (vete tú a saber por qué) que no sea nullable. Siendo así, nos toca meter mano a la SQL, para especificar un valor por defecto y además castearlo al tipo concreto:

COALESCE(Novedad, 0) AS BIT

Este problema pasa igual con el campo PropiedadesProducto.Precio, pero en este caso basta con darle un valor predeteminado y no es necesario el cast en la SQL porque .NET puede castear implícitamente desde int a decimal.

Otro punto a tener en cuenta es cómo espera SqlQuery recibir los parámetros. La verdad es que hay varias maneras de especificarlos, cada una con sus pros y sus contras. Cabe mencionar que, finalmente, todas las opciones que veremos a continuación terminan ejecutando la misma sentencia SQL en el servidor, en este punto no hay diferencia.

Podemos utilizar indicadores de posición al estilo de string.Format:

        WHERE   Productos.IdProducto = {0}

También podemos utilizar una “extraña” notación similar al anterior, pero que obliga a poner el sufijo @p<Posición>

        WHERE   Productos.IdProducto = @p0

Por último, un acercamiento más clásico (y quizás el más legible de todos) implica especificar los parámetros por nombre

        WHERE   Productos.IdProducto = @IdProducto

 

En los 2 primeros casos ({0} y @p0), los valores los suministraremos con un array de tipo Object.

    var parametros = new object[] { "1" };

Para el caso de parámetros con nombre, tenemos que pasar un array de objetos del tipo SqlParameter:

    var parametros = new[] { new SqlParameter("IdProducto", "1") };

Llegados a este punto, todo parece atado y bien atado, pero no… no sé si será la versión de Entity Framework (actualmente este post está hecho con la versión 6.0.2) o qué sé yo… pero utilizando parámetros del tipo SqlParameter, si ejecutas 2 veces la misma sentencia falla ¿?

Por ejemplo:

var parametros = new[] { new SqlParameter("IdProducto", "1") };

var productos =

    context.Database.SqlQuery<ProductoCompleto>(sql, parametros).ToList();

var productos2 =

    context.Database.SqlQuery<ProductoCompleto>(sql, parametros).ToList();

Lanza la siguiente excepción:

Después de perder mucho tiempo intentando buscar una solución… no la he encontrado. Entiendo que en futuras versiones de EF solucionarán este problema y no reutilizarán el mismo objeto SqlCommand… espero.

Como tengo que ejecutar (de nuevo por exigencias del guión) la misma SQL 2 veces, he optado por Dapper que conocí gracias al post de @gulnor ORMs vs MicroORMs vs ADO.NET: Cuándo usar cada uno.

Con Dapper, el anterior código queda como sigue:

dynamic parametros = new ExpandoObject();

parametros.IdProducto = "1";

var productos =

    context.Database.Connection.Query<ProductoCompleto>(sql, (ExpandoObject)parametros).ToList();

La verdad es que me gusta poder tener parámetros con nombre en la SQL (@IdProducto) y la flexibilidad que da un objeto ExpandoObject que permite agregar propiedades al vuelo y haciendo así posible la inclusión condicional de parámetros. Lógicamente, si parámetros tuviera más propiedades que no coincidieran con parámetros con nombre en la SQL, no pasaría nada, pero me gusta más agregarlos de forma dinámica y así tengo la certeza de que sólo viajan los parámetros necesarios:

dynamic parametros = new ExpandoObject();

parametros.IdProducto = "1";

if (novedad != null)

{

    parametros.Novedad = novedad;

}

Un saludo!