Entity Framework 6 Code First新特性:支持存储过程
Entity Framework 6提供支持存储过程的新特性,本文具体演示Entity Framework 6 Code First的存储过程操作。
Code First的插入/修改/删除存储过程
默认情况下下,Code First配置对全部实体的插入/修改/删除操作均直接针对表进行。从EF6开始可以配置对全部或部分实体来选择使用存储过程。
1. 基本实体映射
1.1 通过Fluent API,配置使用插入/修改/删除存储过程
modelBuilder .Entity<Blog>() .MapToStoredProcedures();
1.2 Code First在数据库中生成存储过程的约定
◊ 生成三个存储过程,名称分别为<type_name>_Insert, <type_name>_Update, <type_name>_Delete;
◊ 参数名对应于属性名 (注意:如果在 property上使用 HasColumnName() 或者 Column attribute 来重命名,那么参数也将使用这个重命名过的名称 );
◊ The insert stored procedure 为每一个属性都有一个参数,除了那些标记为数据库产生的(identity or computed),返回结果为那些标记为数据库产生的属性列;
◊ The update stored procedure 为每一个属性都有一个参数,除了那些标记为数据库产生且模式为 computed 的。一些并发标记的需要一个代表原始值的参数。返回值为那些 computed property 的列;
◊ The delete stored procedure 参数为实体主键(或者组合主键),此外也需要为每一个独立关联的外键准备一个参数(指那些没有在实体上定义相应外键属性的关系),一些并发标记的需要一个代表原始值的参数。
示例:
实体文件blog.cs:
using System; using System.Collections.Generic; namespace EF6.Models { public partial class Blog { public int BlogID { get; set; } public string Name { get; set; } public string Url { get; set; } } }
实体映射文件BlogMap.cs:
using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; namespace EF6.Models.Mapping { public class BlogMap : EntityTypeConfiguration<Blog> { public BlogMap() { // Primary Key this.HasKey(t => t.BlogID); // Properties this.Property(t => t.Name) .HasMaxLength(50); this.Property(t => t.Url) .HasMaxLength(100); // Table & Column Mappings this.ToTable("Blog"); this.Property(t => t.BlogID).HasColumnName("BlogID"); this.Property(t => t.Name).HasColumnName("Name"); this.Property(t => t.Url).HasColumnName("Url"); // Procedures this.MapToStoredProcedures(); } } }
在程序包管理器控制台中依次执行:
PM> Enable-Migrations -EnableAutomaticMigrations
PM> Add-Migration InitialCreate
PM> Update-Database -Verbose
执行完成之后生成数据库:
监控生成数据的SQL语句:
CREATE TABLE [dbo].[Blog] ( [BlogID] [int] NOT NULL IDENTITY, [Name] [nvarchar](50), [Url] [nvarchar](100), CONSTRAINT [PK_dbo.Blog] PRIMARY KEY ([BlogID])
CREATE PROCEDURE [dbo].[Blog_Insert] @Name [nvarchar](50), @Url [nvarchar](100) AS BEGIN INSERT [dbo].[Blog]([Name], [Url]) VALUES (@Name, @Url) DECLARE @BlogID int SELECT @BlogID = [BlogID] FROM [dbo].[Blog] WHERE @@ROWCOUNT > 0 AND [BlogID] = scope_identity() SELECT t0.[BlogID] FROM [dbo].[Blog] AS t0 WHERE @@ROWCOUNT > 0 AND t0.[BlogID] = @BlogID END
CREATE PROCEDURE [dbo].[Blog_Update] @BlogID [int], @Name [nvarchar](50), @Url [nvarchar](100) AS BEGIN UPDATE [dbo].[Blog] SET [Name] = @Name, [Url] = @Url WHERE ([BlogID] = @BlogID) END
CREATE PROCEDURE [dbo].[Blog_Delete] @BlogID [int] AS BEGIN DELETE [dbo].[Blog] WHERE ([BlogID] = @BlogID) END
2、重新默认约定
2.1、重命名存储过程名称
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => s.Update(u => u.HasName("Modify_Blog")));
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => s.Update(u => u.HasName("Update_Blog")) .Delete(d => d.HasName("Delete_Blog")) .Insert(i => i.HasName("Insert_Bblog")));
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => { s.Update(u => u.HasName("Update_Blog")); s.Delete(d => d.HasName("Delete_Blog")); s.Insert(i => i.HasName("Insert_Blog")); });
using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; namespace EF6.Models.Mapping { public class BlogMap : EntityTypeConfiguration<Blog> { public BlogMap() { // Primary Key this.HasKey(t => t.BlogID); // Properties this.Property(t => t.Name) .HasMaxLength(50); this.Property(t => t.Url) .HasMaxLength(100); // Table & Column Mappings this.ToTable("Blog"); this.Property(t => t.BlogID).HasColumnName("BlogID"); this.Property(t => t.Name).HasColumnName("Name"); this.Property(t => t.Url).HasColumnName("Url"); // Procedures this.MapToStoredProcedures(s =>{ s.Insert(u => u.HasName("Insert_Blog")); s.Update(u => u.HasName("Update_Blog")); s.Delete(u => u.HasName("Delete_Blog")); }); } } }
运行之后执行的SQL语句:
EXECUTE sp_rename @objname = N'dbo.Blog_Insert', @newname = N'Insert_Blog', @objtype = N'OBJECT' EXECUTE sp_rename @objname = N'dbo.Blog_Update', @newname = N'Update_Blog', @objtype = N'OBJECT' EXECUTE sp_rename @objname = N'dbo.Blog_Delete', @newname = N'Delete_Blog', @objtype = N'OBJECT'
2.2、重命名存储过程参数名称
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => s.Update(u => u.Parameter(b => b.BlogID, "Blog_ID")));
using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; namespace EF6.Models.Mapping { public class BlogMap : EntityTypeConfiguration<Blog> { public BlogMap() { // Primary Key this.HasKey(t => t.BlogID); // Properties this.Property(t => t.Name) .HasMaxLength(50); this.Property(t => t.Url) .HasMaxLength(100); // Table & Column Mappings this.ToTable("Blog"); this.Property(t => t.BlogID).HasColumnName("BlogID"); this.Property(t => t.Name).HasColumnName("Name"); this.Property(t => t.Url).HasColumnName("Url"); // Procedures this.MapToStoredProcedures(s => { s.Update(u => u.Parameter(b => b.BlogID, "Blog_ID")); }); } } }
运行之后执行的SQL语句:
ALTER PROCEDURE [dbo].[Blog_Update] @Blog_ID [int], @Name [nvarchar](50), @Url [nvarchar](100) AS BEGIN UPDATE [dbo].[Blog] SET [Name] = @Name, [Url] = @Url WHERE ([BlogID] = @Blog_ID) END
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => s.Update(u => u.HasName("Update_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")));
2.3、重命名数据库自动生成列的返回值的列名
modelBuilder .Entity<Blog>() .MapToStoredProcedures(s => s.Insert(i => i.Result(b => b.BlogID, "generated_blog_identity")));
using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.ModelConfiguration; namespace EF6.Models.Mapping { public class BlogMap : EntityTypeConfiguration<Blog> { public BlogMap() { // Primary Key this.HasKey(t => t.BlogID); // Properties this.Property(t => t.Name) .HasMaxLength(50); this.Property(t => t.Url) .HasMaxLength(100); // Table & Column Mappings this.ToTable("Blog"); this.Property(t => t.BlogID).HasColumnName("BlogID"); this.Property(t => t.Name).HasColumnName("Name"); this.Property(t => t.Url).HasColumnName("Url"); // Procedures this.MapToStoredProcedures(s => { s.Insert(i => i.Result(b => b.BlogID, "generated_blog_identity")); }); } } }
运行之后执行的SQL语句:
ALTER PROCEDURE [dbo].[Blog_Insert] @Name [nvarchar](50), @Url [nvarchar](100) AS BEGIN INSERT [dbo].[Blog]([Name], [Url]) VALUES (@Name, @Url) DECLARE @BlogID int SELECT @BlogID = [BlogID] FROM [dbo].[Blog] WHERE @@ROWCOUNT > 0 AND [BlogID] = scope_identity() SELECT t0.[BlogID] AS generated_blog_identity FROM [dbo].[Blog] AS t0 WHERE @@ROWCOUNT > 0 AND t0.[BlogID] = @BlogID END
3、多对多关系
两个多对多的实体类:
using System; using System.Collections.Generic; namespace EF6.Models { public partial class Post { public int PostID { get; set; } public string Title { get; set; } public string Content { get; set; } public virtual ICollection<Tag> Tags { get; set; } } }
using System; using System.Collections.Generic; namespace EF6.Models { public partial class Tag { public int TagID { get; set; } public string TagName { get; set; } public virtual ICollection<Post> Posts { get; set; } } }
映射存储过程:
protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Post>() .HasMany(p => p.Tags) .WithMany(t => t.Posts) .MapToStoredProcedures(); }
默认生成的存储过程:
◊ 生成两个存储过程,命名为 <type_one><type_two>_Insert 和 <type_one><type_two>_Delete
◊ 参数为每一类型的主键(或组合主键),命名为 <type_name>_<property_name>
代码运行后生成的存储过程:
CREATE PROCEDURE [dbo].[PostTag_Insert] @Post_PostID [int], @Tag_TagID [int] AS BEGIN INSERT [dbo].[PostTags]([Post_PostID], [Tag_TagID]) VALUES (@Post_PostID, @Tag_TagID) END
CREATE PROCEDURE [dbo].[PostTag_Delete] @Post_PostID [int], @Tag_TagID [int] AS BEGIN DELETE [dbo].[PostTags] WHERE (([Post_PostID] = @Post_PostID) AND ([Tag_TagID] = @Tag_TagID)) END
参考资料: