Procédures stockées d’insertion, de mise à jour et de suppression Code First

Par défaut, Code First configure toutes les entités pour effectuer des commandes d’insertion, de mise à jour et de suppression avec un accès direct aux tables. À compter d’EF6, vous pouvez configurer votre modèle Code First afin d’utiliser des procédures stockées pour une partie ou l’ensemble des entités de votre modèle.

Mappage d’entités de base

Vous pouvez choisir d’utiliser des procédures stockées pour effectuer des opérations d’insertion, de mise à jour et de suppression à l’aide de l’API Fluent.

modelBuilder
  .Entity<Blog>()
  .MapToStoredProcedures();

Dans ce cas, Code First doit utiliser certaines conventions pour créer la forme attendue des procédures stockées dans la base de données.

• Nous avons trois procédures stockées nommées <nom_type>_Insert, <nom_type>_Update et <nom_type>_Delete (par exemple, Blog_Insert, Blog_Update et Blog_Delete).
• Les noms des paramètres correspondent aux noms des propriétés.

  Remarque

  Si vous utilisez HasColumnName() ou l’attribut Column pour renommer la colonne d’une propriété donnée, ce nom est utilisé pour les paramètres au lieu du nom de la propriété.

• La procédure stockée d’insertion comprend un paramètre pour chaque propriété, à l’exception de celles marquées comme étant générées par le magasin (identité ou calculé). La procédure stockée doit retourner un jeu de résultats avec une colonne pour chaque propriété générée par le magasin.
• La procédure stockée de mise à jour comprend un paramètre pour chaque propriété, à l’exception de celles marquées avec un modèle « Calculé » généré par le magasin. Certains jetons d’accès concurrentiel nécessitent un paramètre pour la valeur d’origine. Pour plus d’informations, consultez la section Jetons d’accès concurrentiel. La procédure stockée doit retourner un jeu de résultats avec une colonne pour chaque propriété calculée.
• La procédure stockée de suppression doit avoir un paramètre pour la valeur de clé de l’entité (ou plusieurs paramètres si l’entité a une clé composite). La procédure de suppression doit également avoir des paramètres pour toutes les clés étrangères d’association indépendante sur la table cible (relations n’ayant pas de propriétés de clé étrangère correspondantes déclarées dans l’entité). Certains jetons d’accès concurrentiel nécessitent un paramètre pour la valeur d’origine. Pour plus d’informations, consultez la section Jetons d’accès concurrentiel.

Prenons la classe suivante comme exemple :

public class Blog  
{  
  public int BlogId { get; set; }  
  public string Name { get; set; }  
  public string Url { get; set; }  
}

Les procédures stockées par défaut sont les suivantes :

CREATE PROCEDURE [dbo].[Blog_Insert]  
  @Name nvarchar(max),  
  @Url nvarchar(max)  
AS  
BEGIN
  INSERT INTO [dbo].[Blogs] ([Name], [Url])
  VALUES (@Name, @Url)

  SELECT SCOPE_IDENTITY() AS BlogId
END
CREATE PROCEDURE [dbo].[Blog_Update]  
  @BlogId int,  
  @Name nvarchar(max),  
  @Url nvarchar(max)  
AS  
  UPDATE [dbo].[Blogs]
  SET [Name] = @Name, [Url] = @Url     
  WHERE BlogId = @BlogId;
CREATE PROCEDURE [dbo].[Blog_Delete]  
  @BlogId int  
AS  
  DELETE FROM [dbo].[Blogs]
  WHERE BlogId = @BlogId

Substitution des valeurs par défaut

Vous pouvez substituer une partie ou l’ensemble de ce qui a été configuré par défaut.

Vous pouvez modifier le nom d’une ou plusieurs procédures stockées. Cet exemple renomme uniquement la procédure stockée de mise à jour.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.HasName("modify_blog")));

Cet exemple renomme les trois procédures stockées.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.HasName("modify_blog"))  
     .Delete(d => d.HasName("delete_blog"))  
     .Insert(i => i.HasName("insert_blog")));

Dans ces exemples, les appels sont chaînés ensemble, mais vous pouvez également utiliser la syntaxe de bloc lambda.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    {  
      s.Update(u => u.HasName("modify_blog"));  
      s.Delete(d => d.HasName("delete_blog"));  
      s.Insert(i => i.HasName("insert_blog"));  
    });

Cet exemple renomme le paramètre de la propriété BlogId sur la procédure stockée de mise à jour.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.Parameter(b => b.BlogId, "blog_id")));

Ces appels sont tous chaînables et composables. Voici un exemple qui renomme les trois procédures stockées et leurs paramètres.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.HasName("modify_blog")  
                   .Parameter(b => b.BlogId, "blog_id")  
                   .Parameter(b => b.Name, "blog_name")  
                   .Parameter(b => b.Url, "blog_url"))  
     .Delete(d => d.HasName("delete_blog")  
                   .Parameter(b => b.BlogId, "blog_id"))  
     .Insert(i => i.HasName("insert_blog")  
                   .Parameter(b => b.Name, "blog_name")  
                   .Parameter(b => b.Url, "blog_url")));

Vous pouvez également modifier le nom des colonnes du jeu de résultats contenant les valeurs générées par la base de données.

modelBuilder
  .Entity<Blog>()
  .MapToStoredProcedures(s =>
    s.Insert(i => i.Result(b => b.BlogId, "generated_blog_identity")));

CREATE PROCEDURE [dbo].[Blog_Insert]  
  @Name nvarchar(max),  
  @Url nvarchar(max)  
AS  
BEGIN
  INSERT INTO [dbo].[Blogs] ([Name], [Url])
  VALUES (@Name, @Url)

  SELECT SCOPE_IDENTITY() AS generated_blog_id
END

Relations sans clé étrangère dans la classe (associations indépendantes)

Quand une propriété de clé étrangère est incluse dans la définition de classe, le paramètre correspondant peut être renommé de la même façon que toute autre propriété. Dans le cas d’une relation sans propriété de clé étrangère dans la classe, le nom du paramètre par défaut est <nom_propriété_navigation>_<nom_clé_primaire>.

Par exemple, pour les définitions de classe suivantes, un paramètre Blog_BlogId serait attendu dans les procédures stockées pour insérer et mettre à jour Posts.

public class Blog  
{  
  public int BlogId { get; set; }  
  public string Name { get; set; }  
  public string Url { get; set; }

  public List<Post> Posts { get; set; }  
}  

public class Post  
{  
  public int PostId { get; set; }  
  public string Title { get; set; }  
  public string Content { get; set; }  

  public Blog Blog { get; set; }  
}

Substitution des valeurs par défaut

Vous pouvez modifier les paramètres des clés étrangères qui ne sont pas incluses dans la classe en fournissant le chemin à la propriété de clé primaire à la méthode Parameter.

modelBuilder
  .Entity<Post>()  
  .MapToStoredProcedures(s =>  
    s.Insert(i => i.Parameter(p => p.Blog.BlogId, "blog_id")));

Si vous n’avez pas de propriété de navigation sur l’entité dépendante (c’est-à-dire aucune propriété Post.Blog), vous pouvez utiliser la méthode Association pour identifier l’autre extrémité de la relation, puis configurer les paramètres qui correspondent à chacune des propriétés de clé.

modelBuilder
  .Entity<Post>()  
  .MapToStoredProcedures(s =>  
    s.Insert(i => i.Navigation<Blog>(  
      b => b.Posts,  
      c => c.Parameter(b => b.BlogId, "blog_id"))));

Jetons d'accès concurrentiel

Les procédures stockées de mise à jour et de suppression peuvent également avoir besoin de gérer l’accès concurrentiel :

• Si l’entité contient des jetons d’accès concurrentiel, la procédure stockée peut éventuellement avoir un paramètre de sortie qui retourne le nombre de lignes mises à jour/supprimées (lignes affectées). Un tel paramètre doit être configuré à l’aide de la méthode RowsAffectedParameter.
  Par défaut, EF utilise la valeur de retour d’ExecuteNonQuery pour déterminer le nombre de lignes affectées. La spécification d’un paramètre de sortie de lignes affectées est utile si vous effectuez une logique dans votre sproc qui donnerait lieu à une valeur de retour d’ExecuteNonQuery incorrecte (du point de vue d’EF) à la fin de l’exécution.
• À chaque jeton d’accès concurrentiel correspond un paramètre nommé <nom_propriété>_Original (par exemple, Timestamp_Original). La valeur d’origine de cette propriété (valeur lorsqu’elle est interrogée à partir de la base de données) lui est passée.
  • Les jetons d’accès concurrentiel calculés par la base de données, tels que les horodatages, ont uniquement un paramètre de valeur d’origine.
  • Les propriétés non calculées définies en tant que jetons d’accès concurrentiel ont également un paramètre pour la nouvelle valeur dans la procédure de mise à jour. Les conventions d’affectation de noms déjà abordées pour les nouvelles valeurs sont respectées. L’utilisation de l’URL d’un blog comme jeton d’accès concurrentiel est un exemple d’un tel jeton. La nouvelle valeur est requise car elle peut être mise à jour vers une nouvelle valeur par votre code (contrairement à un jeton Timestamp qui n’est mis à jour que par la base de données).

Voici un exemple de classe et de procédure stockée de mise à jour avec un jeton d’accès concurrentiel Timestamp.

public class Blog  
{  
  public int BlogId { get; set; }  
  public string Name { get; set; }  
  public string Url { get; set; }  
  [Timestamp]
  public byte[] Timestamp { get; set; }
}

CREATE PROCEDURE [dbo].[Blog_Update]  
  @BlogId int,  
  @Name nvarchar(max),  
  @Url nvarchar(max),
  @Timestamp_Original rowversion  
AS  
  UPDATE [dbo].[Blogs]
  SET [Name] = @Name, [Url] = @Url     
  WHERE BlogId = @BlogId AND [Timestamp] = @Timestamp_Original

Voici un exemple de classe et de procédure stockée de mise à jour avec un jeton d’accès concurrentiel non calculé.

public class Blog  
{  
  public int BlogId { get; set; }  
  public string Name { get; set; }  
  [ConcurrencyCheck]
  public string Url { get; set; }  
}

CREATE PROCEDURE [dbo].[Blog_Update]  
  @BlogId int,  
  @Name nvarchar(max),  
  @Url nvarchar(max),
  @Url_Original nvarchar(max),
AS  
  UPDATE [dbo].[Blogs]
  SET [Name] = @Name, [Url] = @Url     
  WHERE BlogId = @BlogId AND [Url] = @Url_Original

Substitution des valeurs par défaut

Vous pouvez éventuellement introduire un paramètre affecté par les lignes.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.RowsAffectedParameter("rows_affected")));

Pour les jetons d’accès concurrentiel calculés par base de données, où seule la valeur d’origine est passée, vous pouvez simplement utiliser le mécanisme de renommage de paramètre standard afin de renommer le paramètre pour la valeur d’origine.

modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.Parameter(b => b.Timestamp, "blog_timestamp")));

Pour les jetons d’accès concurrentiel non calculés, où la valeur d’origine et la nouvelle valeur sont passées, vous pouvez utiliser une surcharge de Parameter qui vous permet de fournir un nom pour chaque paramètre.

modelBuilder
 .Entity<Blog>()
 .MapToStoredProcedures(s => s.Update(u => u.Parameter(b => b.Url, "blog_url", "blog_original_url")));

Relations plusieurs-à-plusieurs

Nous utiliserons les classes suivantes comme exemples dans cette section.

public class Post  
{  
  public int PostId { get; set; }  
  public string Title { get; set; }  
  public string Content { get; set; }  

  public List<Tag> Tags { get; set; }  
}  

public class Tag  
{  
  public int TagId { get; set; }  
  public string TagName { get; set; }  

  public List<Post> Posts { get; set; }  
}

Des relations plusieurs à plusieurs peuvent être mappées à des procédures stockées avec la syntaxe suivante.

modelBuilder  
  .Entity<Post>()  
  .HasMany(p => p.Tags)  
  .WithMany(t => t.Posts)  
  .MapToStoredProcedures();

Si aucune autre configuration n’est fournie, la forme de procédure stockée suivante est utilisée par défaut.

• Nous avons deux procédures stockées nommées <type_un><type_deux>_Insert et <type_un><type_deux>_Delete (par exemple, PostTag_Insert et PostTag_Delete).
• Les paramètres sont les valeurs de clé pour chaque type. Le nom de chaque paramètre est <nom_type>_<nom_propriété> (par exemple, Post_PostId et Tag_TagId).

Voici des exemples de procédures stockées d’insertion et de mise à jour.

CREATE PROCEDURE [dbo].[PostTag_Insert]  
  @Post_PostId int,  
  @Tag_TagId int  
AS  
  INSERT INTO [dbo].[Post_Tags] (Post_PostId, Tag_TagId)   
  VALUES (@Post_PostId, @Tag_TagId)
CREATE PROCEDURE [dbo].[PostTag_Delete]  
  @Post_PostId int,  
  @Tag_TagId int  
AS  
  DELETE FROM [dbo].[Post_Tags]    
  WHERE Post_PostId = @Post_PostId AND Tag_TagId = @Tag_TagId

Substitution des valeurs par défaut

La configuration des noms de procédures et de paramètres est similaire à la configuration des noms de procédures stockées d’entité.

modelBuilder  
  .Entity<Post>()  
  .HasMany(p => p.Tags)  
  .WithMany(t => t.Posts)  
  .MapToStoredProcedures(s =>  
    s.Insert(i => i.HasName("add_post_tag")  
                   .LeftKeyParameter(p => p.PostId, "post_id")  
                   .RightKeyParameter(t => t.TagId, "tag_id"))  
     .Delete(d => d.HasName("remove_post_tag")  
                   .LeftKeyParameter(p => p.PostId, "post_id")  
                   .RightKeyParameter(t => t.TagId, "tag_id")));