Les User Defined Functions, ou plus simplement UDFs, sont un moyen très pratique de mutualiser du code auquel on souhaite faire appel dans une requête SQL. On peut l'employer pour appliquer du traitement sur les données requêtées, effectuer des vérifications, simplifier une requête, etc. On peut aussi s'en servir pour camoufler l'usage d'une syntaxe SQL qui ne serait pas supportée par Entity Framework (Entity Framework 6 en l’occurrence). 

Nous allons donc tacher de voir ensemble comment faire appel à une UDF via Entity Framework.

Contexte

Dernièrement, j'ai été amené à utiliser le mot clef OPENJSON de SQL Server, afin de parser un champ JSON d'une table et d'en traiter le résultat dans une clause WHERE. Plus concrètement, l'une des colonnes de type nvarchar contient un tableau JSON et la requête SQL tente de vérifier si certaines valeurs sont présentes dans ce tableau JSON.

Prenons la table suivante : 

CREATE TABLE [dbo].[Persons](
	[Id] [uniqueidentifier] NOT NULL,
	[Kinds] [nvarchar](max) NULL)

Si l'on admet donc que le champ Kinds peut contenir un tableau JSON de string, une façon d'obtenir toutes les lignes avec la valeur 'test' serait :

select * from dbo.Persons
where 'test' in (select [value] from openjson (Kinds))

Toutefois, il n'est pas possible de traduire cette requête avec Linq to Entity Framework, en cause le mot clef OPENJSON.

Création d'une Scalar Valued Function

Dans ces conditions, il peut être pertinent de masquer l'usage de la syntaxe invalide pour Entity Framework, en créant une UDF :

CREATE FUNCTION [dbo].[json_array_contains]
(
	@json nvarchar(max),
	@value nvarchar(max)
)
RETURNS BIT
AS
BEGIN
	declare @res bit
	declare @count int
	select @count = count([key]) from openjson(@json) where [value] = @value
	select @res = case when @count > 0 then 1 else 0
END
	return @res
END

On peut alors simplifier la requête de filtrage précédente comme suit :

select * from dbo.Persons
where [dbo].[json_array_contains](Kinds, 'kind1') = 1

Appel d'une Scalar Valued Function via Entity Framework

Il peut maintenant être judicieux d'intégrer cette UDF dans un modèle Entity Framework et composer les appels à celle-ci intuitivement, en C# ! Selon le projet, l'usage d'un EDMX ou non, on trouvera plein de solutions via une simple recherche Google. Si, comme moi, vous préférez Entity Framework Code-First, un package nuget très utile permet l'usage de nos UDFs très simplement, dont vous trouverez le GitHub ici. Il suffit ensuite de définir une méthode statique, dans le contexte par exemple :

[Function(FunctionType.ComposableScalarValuedFunction, "json_array_contains", Schema = "dbo")]
public static bool JsonArrayContains(string json, string value)
{
    return Function.CallNotSupported<bool>();
}

Pour ajouter cette méthode au model, le package nuget évoqué précédemment offre une méthode de scan très pratique qui ajoutera toutes les fonctions qu'elle découvrira automatiquement :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.AddFunctions<MyDbContext>();
}

Pour utiliser l'UDF, il n'y a maintenant rien de plus simple :

using (var db = new MyDbContext())
{
    db.Persons.Where(p => MyDbContext.JsonArrayContains(p.Kinds, "test"));
}

Jusqu'ici, tout est assez simple, mais nous verrons dans un prochain article que les choses sérieuses commencent alors ! Nous aborderons notamment les problèmes de performances rencontrées avec l'usage de cette fonction et comment nous avons pu y remédier :)