Procedimentos Armazenados de Inserção, Atualização e Exclusão com Code First

Por padrão, o Code First configurará todas as entidades para executar comandos de inserção, atualização e exclusão usando o acesso direto à tabela. A partir do EF6, você pode configurar seu modelo Code First para usar procedimentos armazenados para algumas ou todas as entidades em seu modelo.

Mapeamento básico de entidades

Você pode optar por usar procedimentos armazenados para inserir, atualizar e excluir usando a API fluente.

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

Isso fará com que o Code First use algumas convenções para criar a forma esperada dos procedimentos armazenados no banco de dados.

• Três procedimentos armazenados denominados <type_name>_Insert, <type_name>_Update e <type_name>_Delete (por exemplo, Blog_Insert, Blog_Update e Blog_Delete).
• Os nomes dos parâmetros correspondem aos nomes das propriedades.

  Observação

  Se você usar HasColumnName() ou o atributo Column para renomear a coluna para uma determinada propriedade, esse nome será usado para parâmetros em vez do nome da propriedade.

• O procedimento armazenado de inserção terá um parâmetro para cada propriedade, exceto para aquelas marcadas como geradas automaticamente (identidade ou computadas). O procedimento armazenado deve retornar um conjunto de resultados com uma coluna para cada propriedade gerada pelo repositório.
• O procedimento armazenado de atualização terá um parâmetro para cada propriedade, exceto aqueles marcados com um padrão gerado pelo repositório de "Computado". Alguns tokens de simultaneidade exigem um parâmetro para o valor original, consulte a seção Tokens de Simultaneidade abaixo para obter detalhes. O procedimento armazenado deve retornar um conjunto de resultados com uma coluna para cada propriedade computada.
• O procedimento armazenado de exclusão deve ter um parâmetro para o valor de chave da entidade (ou vários parâmetros se a entidade tiver uma chave composta). Além disso, o procedimento de exclusão também deve ter parâmetros para qualquer chave estrangeira de associação independente na tabela de destino (relações que não têm propriedades de chave estrangeira correspondentes declaradas na entidade). Alguns tokens de simultaneidade exigem um parâmetro para o valor original, consulte a seção Tokens de Simultaneidade abaixo para obter detalhes.

Usando a seguinte classe como exemplo:

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

Os procedimentos armazenados padrão seriam:

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

Sobrescrevendo os padrões padrão

Você pode substituir parte ou tudo o que foi configurado por padrão.

Você pode alterar o nome de um ou mais procedimentos armazenados. Este exemplo renomeia apenas o procedimento armazenado de atualização.

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

Este exemplo renomeia todos os três procedimentos armazenados.

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

Nestes exemplos, as chamadas são encadeadas juntas, mas você também pode usar a sintaxe de bloco 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"));  
    });

Este exemplo renomeia o parâmetro da propriedade BlogId no procedimento armazenado de atualização.

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

Essas chamadas são encadeáveis e combináveis. Aqui está um exemplo que renomeia todos os três procedimentos armazenados e seus parâmetros.

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")));

Você também pode alterar o nome das colunas no conjunto de resultados que contém valores gerados pelo banco de dados.

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

Relações sem uma chave estrangeira na classe (associações independentes)

Quando uma propriedade de chave estrangeira é incluída na definição de classe, o parâmetro correspondente pode ser renomeado da mesma forma que qualquer outra propriedade. Quando existe uma relação sem uma propriedade de chave estrangeira na classe, o nome do parâmetro padrão é <navigation_property_name>_<primary_key_name>.

Por exemplo, as definições de classe a seguir resultariam em um parâmetro Blog_BlogId sendo esperado nos procedimentos armazenados para inserir e atualizar Postagens.

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; }  
}

Sobrescrevendo os padrões padrão

Você pode alterar parâmetros para chaves estrangeiras que não estão incluídas na classe fornecendo o caminho para a propriedade de chave primária para o método Parameter.

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

Se você não tiver uma propriedade de navegação na entidade dependente (ou seja, nenhuma propriedade Post.Blog), poderá usar o método Association para identificar a outra extremidade da relação e, em seguida, configurar os parâmetros que correspondem a cada uma das propriedades de chave.

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

Tokens de concorrência

Atualizar e excluir procedimentos armazenados também pode precisar lidar com simultaneidade:

• Se a entidade contiver tokens de simultaneidade, o procedimento armazenado poderá ter opcionalmente um parâmetro de saída que retorna o número de linhas atualizadas/excluídas (linhas afetadas). Esse parâmetro deve ser configurado usando o método RowsAffectedParameter.
  Por padrão, o EF usa o valor retornado de ExecuteNonQuery para determinar quantas linhas foram afetadas. Especificar um parâmetro de saída afetado de linhas será útil se você executar qualquer lógica em seu sproc que resulte no valor retornado de ExecuteNonQuery incorreto (da perspectiva do EF) no final da execução.
• Para cada token de simultaneidade, haverá um parâmetro chamado <property_name>_Original (por exemplo, Timestamp_Original). Isso passará o valor original dessa propriedade – o valor quando consultado no banco de dados.
  • Tokens de simultaneidade computados pelo banco de dados – como carimbos de data/hora – terão apenas um parâmetro de valor original.
  • As propriedades não computadas definidas como tokens de simultaneidade também terão um parâmetro para o novo valor no procedimento de atualização. Isso usa as convenções de nomenclatura já discutidas para novos valores. Um exemplo desse token seria usar a URL de um blog como token de concorrência. O novo valor é necessário porque ele pode ser atualizado pelo seu código para um novo valor (ao contrário de um token de carimbo de data/hora, que só é atualizado pelo banco de dados).

Este é um exemplo de classe e de procedimento armazenado de atualização com um token de concorrência de marcação temporal.

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

Aqui está uma classe de exemplo e um procedimento armazenado de atualização com token de simultaneidade não calculado.

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

Sobrescrevendo os padrões

Opcionalmente, você pode introduzir um parâmetro afetado por linhas.

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

Para tokens de concorrência gerados pelo banco de dados – nos quais apenas o valor original é passado – você pode usar o mecanismo padrão de renomeação de parâmetro para renomear o parâmetro para o valor original.

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

Para tokens de simultaneidade não computados – onde tanto o valor original quanto o novo são passados – você pode usar uma sobrecarga do parâmetro que permite fornecer um nome para cada parâmetro.

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

Muitos para muitos relacionamentos

Usaremos as seguintes classes como exemplo nesta seção.

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; }  
}

Muitos ou muitos relacionamentos podem ser mapeados para procedimentos armazenados com a sintaxe a seguir.

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

Se nenhuma outra configuração for fornecida, a seguinte forma de procedimento armazenado será usada por padrão.

• Dois procedimentos armazenados chamados <type_one><type_two>_Insert e <type_one><type_two>_Delete (por exemplo, PostTag_Insert e PostTag_Delete).
• Os parâmetros serão os valores de chave para cada tipo. O nome de cada parâmetro como <type_name>_<property_name> (por exemplo, Post_PostId e Tag_TagId).

Aqui estão exemplos de procedimentos armazenados de inserção e atualização.

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

Sobrescrevendo os padrões padrão

Os nomes de procedimento e parâmetro podem ser configurados de maneira semelhante aos procedimentos de armazenamento de entidade.

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")));