Funciones SQL con Entity Framework

Hace poco, para realizar una funcionalidad del proyecto en el que estoy trabajando, tuve la necesidad de utilizar una función de SQL para obtener datos de una BBDD que no podía obtener con sentencias sobre los repositorios, o DbSet.

En las siguientes líneas intentaré explicar cómo crear una función en SQL (user defined function) y cómo poder utilizarla a través de Entity Framework en nuestro proyecto.

En mi caso concreto tenía un campo en BBDD que guardaba un listado de códigos separados por ‘;’ y necesitaba encontrar los registros de la tabla que tuvieran en esa lista un código en concreto.

El problema era que utilizando Linq no podía realizar una sentencia parecida al ‘like’ de SQL que buscara una coincidencia utilizando una expresión regular ya que a Linq no soporta el operador LIKE con caracteres especiales (wildcards). Por otro lado, tampoco podía utilizar la sentencia ‘contains’ porque podía devolverme resultados incorrectos.

El caso es el siguiente:

Tenemos una tabla en BBDD que contiene un campo como este:

Desde la aplicación si quisiéramos obtener los registros de la tabla, que en el campo códigos tuviera un ‘1’, podríamos hacer algo parecido a esto:

GetRowsByCode(x => x.codigos.Contains(‘1’)).ToList();
testEntitiesFunctions.GetRowsByCode(x=> x.codigos.Equals(‘1’)). ToList ();

El problema de la primera sentencia es que la función ‘contains’ mira si el campo contiene el número ‘1’ en todo el valor de la columna, por lo que también nos devolverá filas donde la columna códigos tenga el valor ’11’, ‘111’, ‘101’, ’21’, ’31’… por lo que no nos sirve.

Por otro lado, la segunda sentencia buscará que el campo códigos sea igual a ‘1’, por lo que tampoco nos sirve.

Es posible que la solución que plantearé a continuación no sea la única y posiblemente tampoco sea la más eficiente pero en mi caso fue suficiente.

El caso es que no me parecía muy óptimo obtener todos los datos de la tabla en memoria y recorrerlos para obtener las líneas que necesitaba, pensar que ese procedimiento lo debería hacer varias veces y la tabla tiene más de 8000 registros. La solución fue crear una función en SQL que recibiera el código que busco por parámetro y que mediante la sentencia ‘like’, combinado con una expresión regular sobre la columna códigos, me devolviera las filas que cumplieran la condición.

Para crear una función en SQL es tan sencillo como lanzar una sentencia como esta en nuestra BBDD:

Con esta función lo que conseguimos es buscar dentro de la columna códigos la combinación de caracteres que se parezca [like] a: ‘carácter no numérico [%[^0-9]]’ + ‘código’ + ‘carácter no numérico [[^0-9]%]’. La ‘select’ que hay dentro de la función nos devolverá los campos 1, 2 y 3 de todas las filas donde exista esa coincidencia.

Una vez creada la función tenemos que poder llamarla desde nuestra aplicación. En este caso haremos uso de Entity Framework que es el ORM encargado de comunicarse con la BBDD.

Par ello, sobre nuestro EDMX, haremos «update model from database». Este proceso se dará cuenta de que hemos generado una función nueva y nos la añadirá al proyecto para que podamos usarla. Aparentemente no notaremos nada en el EDMX pero si accedemos al context.cs que hay debajo de nuestro EDMX podremos ver como se ha creado la nueva función.