Procedimientos almacenados de inserción, actualización y eliminación de Code First

  • Artículo

Nota:

Solo EF6 y versiones posteriores: las características, las API, etc. que se tratan en esta página se han incluido a partir de Entity Framework 6. Si usa una versión anterior, no se aplica parte o la totalidad de la información.

De forma predeterminada, Code First configurará todas las entidades para ejecutar comandos de inserción, actualización y eliminación mediante el acceso directo a la tabla. A partir de EF6, puede configurar el modelo Code First para usar procedimientos almacenados con algunas o todas las entidades del modelo.

Asignación básica de entidades

Puede optar por usar procedimientos almacenados de inserción, actualización y eliminación mediante la API de Fluent.

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

Esto hará que Code First use algunas convenciones para compilar la forma esperada de los procedimientos almacenados en la base de datos.

  • Tres procedimientos almacenados llamados <type_name>_Insert, <type_name>_Update y <type_name>_Delete (por ejemplo, Blog_Insert, Blog_Update y Blog_Delete).
  • Los nombres de parámetro corresponden a los nombres de propiedad.

    Nota:

    Si usa HasColumnName() o el atributo Column para cambiar el nombre de la columna de una propiedad determinada, este es el nombre que se usa para los parámetros en lugar del nombre de la propiedad.

  • El procedimiento almacenado de inserción tendrá un parámetro para cada propiedad, excepto para las marcadas como generadas por el almacén (identidad o calculada). El procedimiento almacenado debe devolver un conjunto de resultados con una columna para cada propiedad generada por el almacén.
  • El procedimiento almacenado de actualización tendrá un parámetro para cada propiedad, excepto para las marcadas con un patrón generado por el almacén de "calculadas". Algunos tokens de simultaneidad requieren un parámetro para el valor original. Consulte la sección Tokens de simultaneidad a continuación para más información. El procedimiento almacenado debe devolver un conjunto de resultados con una columna para cada propiedad calculada.
  • El procedimiento almacenado de eliminación debe tener un parámetro para el valor de clave de la entidad (o varios parámetros si la entidad tiene una clave compuesta). Además, el procedimiento de eliminación también debe tener parámetros para cualquier clave externa de asociación independiente en la tabla de destino (relaciones que no tienen las propiedades de clave externa correspondientes declaradas en la entidad). Algunos tokens de simultaneidad requieren un parámetro para el valor original. Consulte la sección Tokens de simultaneidad a continuación para más información.

Usando la clase siguiente como ejemplo:

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

Los procedimientos almacenados predeterminados serían:

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

Invalidación de los valores predeterminados

Puede invalidar parte o todo lo que se configuró de forma predeterminada.

Puede cambiar el nombre de uno o varios procedimientos almacenados. En este ejemplo solo se cambia el nombre del procedimiento almacenado de actualización.

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

En este ejemplo se cambia el nombre de los tres procedimientos almacenados.

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

En estos ejemplos, las llamadas están encadenadas, pero también puede usar la sintaxis de bloque 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"));  
    });

En este ejemplo se cambia el nombre del parámetro de la propiedad BlogId en el procedimiento almacenado de actualización.

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

Todas estas llamadas se pueden encadenar y componer. Aquí hay un ejemplo donde se cambia el nombre de los tres procedimientos almacenados y sus 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")));

También puede cambiar el nombre de las columnas del conjunto de resultados que contiene valores generados por la base de datos.

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

Relaciones sin una clave externa en la clase (asociaciones independientes)

Cuando se incluye una propiedad de clave externa en la definición de clase, se puede cambiar el nombre del parámetro correspondiente de la misma manera que cualquier otra propiedad. Cuando existe una relación sin una propiedad de clave externa en la clase, el nombre del parámetro predeterminado es <navigation_property_name>_<primary_key_name>.

Por ejemplo, las siguientes definiciones de clase darían lugar a que se espere un parámetro Blog_BlogId en los procedimientos almacenados para insertar y actualizar publicaciones.

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

Invalidación de los valores predeterminados

Puede cambiar los parámetros de las claves externas que no se incluyen en la clase proporcionando la ruta de acceso a la propiedad de clave principal al método Parameter.

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

Si no tiene una propiedad de navegación en la entidad dependiente (es decir, ninguna propiedad Post.Blog), puede usar el método Association para identificar el otro extremo de la relación y, luego, configurar los parámetros que corresponden a cada una de las propiedades de clave.

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

Tokens de simultaneidad

Es posible que también necesite actualizar y eliminar procedimientos almacenados para hacer frente a la simultaneidad:

  • Si la entidad contiene tokens de simultaneidad, el procedimiento almacenado puede tener opcionalmente un parámetro de salida que devuelva el número de filas actualizadas o eliminadas (filas afectadas). Este parámetro debe configurarse mediante el método RowsAffectedParameter.
    De forma predeterminada, EF usa el valor devuelto por ExecuteNonQuery para determinar cuántas filas se han visto afectadas. Especificar un parámetro de salida de filas afectadas es útil si se realiza alguna lógica en el procedimiento almacenado que podría dar lugar a que el valor devuelto de ExecuteNonQuery fuera incorrecto (desde la perspectiva de EF) al final de la ejecución.
  • Para cada token de simultaneidad habrá un parámetro llamado <property_name>_Original (por ejemplo, Timestamp_Original ). A este se le pasará el valor original de esta propiedad, el valor cuando se consulta desde la base de datos.
    • Los tokens de simultaneidad calculados por la base de datos, como las marcas de tiempo, solo tendrán un parámetro de valor original.
    • Las propiedades no calculadas que se establecen como tokens de simultaneidad también tendrán un parámetro para el nuevo valor en el procedimiento de actualización. Para este se usarán las convenciones de nomenclatura ya descritas para los nuevos valores. Un ejemplo de este token sería usar la dirección URL de un blog como token de simultaneidad. El nuevo valor es necesario porque el código puede actualizarlo a un nuevo valor (a diferencia de un token de marca de tiempo que solo actualiza la base de datos).

Este es un ejemplo de una clase y un procedimiento almacenado de actualización con un token de simultaneidad de marca de tiempo.

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

Este es un ejemplo de una clase y un procedimiento almacenado de actualización con un token de simultaneidad no 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

Invalidación de los valores predeterminados

Opcionalmente, puede introducir un parámetro de filas afectadas.

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

En el caso de los tokens de simultaneidad calculados de base de datos, en los que solo se pasa el valor original, puede usar el mecanismo estándar de cambio de nombre de los parámetros para cambiar el nombre del parámetro por el valor original.

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

En el caso de los tokens de simultaneidad no calculados, en los que se pasa el valor original y el nuevo, puede usar una sobrecarga de Parameter que le permita proporcionar un nombre para cada parámetro.

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

Relaciones de varios a varios

En esta sección usaremos las siguientes clases como ejemplo.

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

Se pueden asignar relaciones de varios a varios a procedimientos almacenados con la sintaxis siguiente.

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

Si no se proporciona ninguna otra configuración, se usa de forma predeterminada la siguiente forma de procedimiento almacenado.

  • Dos procedimientos almacenados llamados <type_one><type_two>_Insert y <type_one><type_two>_Delete (por ejemplo, PostTag_Insert y PostTag_Delete).
  • Los parámetros serán los valores de clave para cada tipo. Donde el nombre de cada parámetro es <type_name>_<property_name> (por ejemplo, Post_PostId y Tag_TagId).

Estos son ejemplos de procedimientos almacenados de inserción y actualización.

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

Invalidación de los valores predeterminados

Los nombres de procedimiento y parámetro se pueden configurar de forma similar a los procedimientos almacenados de entidad.

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