Code First inserir, atualizar e excluir procedimentos armazenados

Artigo
10/05/2023

Observação

EF6 em diante apenas: os recursos, as APIs etc. discutidos nessa página foram introduzidos no Entity Framework 6. Se você estiver usando uma versão anterior, algumas ou todas as informações não se aplicarão.

Por padrão, o Code First configurará todas as entidades para executar comandos de inserção, atualização e exclusão usando 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 de parâmetros correspondem aos nomes de propriedade.

Observação

Se você usar HasColumnName() ou o atributo Column para renomear a coluna de 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 armazenadas (identidade ou computadas). O procedimento armazenado deve retornar um conjunto de resultados com uma coluna para cada propriedade gerada pelo armazenamento.
O procedimento armazenado de atualização terá um parâmetro para cada propriedade, exceto para aquelas marcadas com um padrão gerado pelo armazenamento 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 da 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 quaisquer chaves estrangeiras 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

Substituindo os padrões

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

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

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

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

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

Esse 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 todas encadeáveis e componí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 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 maneira 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 esperado nos procedimentos armazenados para inserir e atualizar 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; }  
}

Substituindo os padrões

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 simultaneidade

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

Se a entidade contiver tokens de simultaneidade, o procedimento armazenado pode, opcionalmente, ter 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 de retorno de ExecuteNonQuery para determinar quantas linhas foram afetadas. A especificação de um parâmetro de saída afetado por linhas será útil se você executar qualquer lógica em seu sproc que resulte no valor de retorno 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 ). Será passado o valor original dessa propriedade – o valor quando consultado do banco de dados.
- Os tokens de simultaneidade que são 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 de tal token seria usar a URL de um blog como um token de simultaneidade, o novo valor é necessário porque isso pode ser atualizado para um novo valor pelo seu código (ao contrário de um token Timestamp que só é atualizado pelo banco de dados).

Este é um exemplo de classe e atualizar procedimento armazenado com um token de simultaneidade de carimbo de data/hora.

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 computado.

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

Substituindo os padrões

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

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

Para tokens de simultaneidade computados de banco de dados – onde apenas o valor original é passado – você pode usar o mecanismo de renomeação de parâmetro padrão 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 em que o valor original e novo são passados, você pode usar uma sobrecarga de 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")));

Relações Muitos para Muitos

Usaremos as classes a seguir 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 para 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 o(s) valor(es) chave(s) para cada tipo. O nome de cada parâmetro sendo <type_name>_<property_name> (por exemplo, Post_PostId e Tag_TagId).

Aqui estão exemplos de inserir e atualizar procedimentos armazenados.

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

Substituindo os padrões

Os nomes de procedimento e parâmetro podem ser configurados de maneira semelhante aos procedimentos armazenados 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")));

Compartilhar via

Code First inserir, atualizar e excluir procedimentos armazenados

Mapeamento básico de entidades

Substituindo os padrões

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

Substituindo os padrões

Tokens de simultaneidade

Substituindo os padrões

Relações Muitos para Muitos

Substituindo os padrões

Recursos adicionais