Código Primeiro Inserir, Atualizar e Eliminar Procedimentos Armazenados

Por padrão, o Code First configura todas as entidades para executar comandos de inserção, actualização e eliminação usando acesso direto à tabela. A partir do EF6, podes configurar o teu modelo Code First para usar stored procedures para algumas ou todas as entidades do teu modelo.

Mapeamento Básico de Entidades

Pode optar por usar procedimentos armazenados para inserir, atualizar e eliminar usando a API do Fluent.

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

Fazer isto fará com que o Code First use algumas convenções para construir a forma esperada dos procedimentos armazenados na base de dados.

• Três procedimentos armazenados chamados< 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 usar HasColumnName() ou o atributo Column para renomear a coluna de uma determinada propriedade, então esse nome é usado para parâmetros em vez do nome da propriedade.

• O procedimento armazenado de 'insert' terá um parâmetro para cada propriedade, exceto para aquelas marcadas como geradas pelo banco de dados (identidade ou calculadas). O procedimento armazenado deve devolver um conjunto de resultados com uma coluna para cada propriedade gerada pela loja.
• O procedimento armazenado de atualização terá um parâmetro para cada propriedade, exceto aquelas marcadas com um padrão gerado pela base de dados 'Computed'. Alguns tokens de concorrência requerem um parâmetro para o valor original, consulte a secção de Tokens de Concorrência abaixo para mais detalhes. O procedimento armazenado deve devolver um conjunto de resultados com uma coluna para cada propriedade calculada.
• O procedimento armazenado de eliminação deve ter um parâmetro para o valor da chave da entidade (ou múltiplos parâmetros se a entidade tiver uma chave composta). Além disso, o procedimento de eliminação deve também ter parâmetros para quaisquer chaves estrangeiras de associação independentes na tabela de destino (relações que não tenham propriedades correspondentes de chave estrangeira declaradas na entidade). Alguns tokens de concorrência requerem um parâmetro para o valor original, consulte a secção de Tokens de Concorrência abaixo para mais 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 as Predefinições

Podes sobrescrever parte ou tudo o que foi configurado por defeito.

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 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 estão encadeadas, mas também pode usar a sintaxe do 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 para a propriedade BlogId no procedimento armazenado de atualização.

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

Estas chamadas são todas encadeáveis e combináveis. Aqui está um exemplo que renomeia os três procedimentos armazenados e os 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")));

Também pode alterar o nome das colunas no conjunto de resultados que contém valores gerados pela base 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 da 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 padrão do parâmetro é <navigation_property_name>_<primary_key_name>.

Por exemplo, as seguintes definições de classe resultariam num 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; }  
}

Sobrescrevendo os Padrões

Pode alterar parâmetros para chaves estrangeiras que não estão incluídas na classe fornecendo o caminho para a propriedade da chave primária ao método Parâmetro.

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

Se não tiver uma propriedade de navegação na entidade dependente (ou seja, nenhuma propriedade Post.Blog), pode usar o método Association para identificar a outra extremidade da relação e depois configurar os parâmetros que correspondem a cada uma das propriedades 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

Os procedimentos armazenados de atualização e eliminação também podem ser necessários para lidar com concorrência:

• Se a entidade contiver tokens de concorrência, o procedimento armazenado pode opcionalmente ter um parâmetro de saída que devolve o número de linhas atualizadas/eliminadas (linhas afetadas). Tal parâmetro deve ser configurado usando o método RowsAffectedParmeter.
  Por defeito, o EF utiliza o valor de retorno do ExecuteNonQuery para determinar quantas linhas foram afetadas. Especificar um parâmetro de saída afetado por linhas é útil se fizeres alguma lógica no teu spurg que resulte no valor de retorno do ExecuteNonQuery incorreto (do ponto de vista do EF) no final da execução.
• Para cada token de concorrência haverá um parâmetro chamado <property_name>_Original (por exemplo, Timestamp_Original ). Isto será fornecido com o valor original desta propriedade – o valor quando consultado da base de dados.
  • Os tokens de concorrência que são calculados pela base de dados – como carimbos temporais – terão apenas um parâmetro de valor original.
  • Propriedades não calculadas que são definidas como tokens de concorrência também terão um parâmetro para o novo valor no procedimento de atualização. Isto utiliza as convenções de nomenclatura já discutidas para novos valores. Um exemplo de tal token seria usar o URL de um Blog como token de concorrência; o novo valor é necessário porque pode ser atualizado para um novo valor pelo seu código (ao contrário de um token Timestamp, que só é atualizado pela base de dados).

Este é um exemplo de procedimento armazenado de classe e atualização com um token de concorrência com carimbo 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á um exemplo de classe e de procedimento armazenado de atualização com token de concorrência 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

Podes opcionalmente introduzir um parâmetro afetado pelas linhas.

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

Para tokens de concorrência calculados em base de dados – onde apenas o valor original é passado – pode simplesmente usar o mecanismo padrão de renomeação dos parâmetros 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 concorrência não calculados – onde tanto o valor original como o novo são passados – é possível utilizar 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")));

Relações de Muitas a Muitas

Vamos usar as seguintes classes como exemplo nesta secçã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; }  
}

Demasiadas relações podem ser mapeadas para procedimentos armazenados com a seguinte sintaxe.

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

Se não for fornecida outra configuração, então a seguinte forma de procedimento armazenado é usada por defeito.

• 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 para cada tipo. O nome de cada parâmetro é <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

Os nomes dos procedimentos e parâmetros podem ser configurados de forma semelhante aos procedimentos armazenados das entidades.

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