首页 > 其他 > 详细

Entity Framework 6 Code First新特性:支持存储过程

时间:2014-03-18 12:02:58      阅读:563      评论:0      收藏:0      [点我收藏+]

  Entity Framework 6提供支持存储过程的新特性,本文具体演示Entity Framework 6 Code First的存储过程操作。

  Code First的插入/修改/删除存储过程

  默认情况下下,Code First配置对全部实体的插入/修改/删除操作均直接针对表进行。从EF6开始可以配置对全部或部分实体来选择使用存储过程。

  1、基本实体映射

  通过Fluent API,配置使用插入/修改/删除存储过程。

bubuko.com,布布扣
modelBuilder 
    .Entity<Blog>() 
    .MapToStoredProcedures();
bubuko.com,布布扣

  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:

bubuko.com,布布扣
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; }
    }
}
bubuko.com,布布扣

  实体映射文件BlogMap.cs:

bubuko.com,布布扣
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();
        }
    }
}
bubuko.com,布布扣

  在程序包管理器控制台中依次执行:

bubuko.com,布布扣
PM> Enable-Migrations -EnableAutomaticMigrations
bubuko.com,布布扣
bubuko.com,布布扣
PM> Add-Migration InitialCreate
bubuko.com,布布扣
bubuko.com,布布扣
PM> Update-Database -Verbose
bubuko.com,布布扣

  执行完成之后生成数据库:

bubuko.com,布布扣

  监控生成数据的SQL语句:

bubuko.com,布布扣
CREATE TABLE [dbo].[Blog] (
    [BlogID] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](50),
    [Url] [nvarchar](100),
    CONSTRAINT [PK_dbo.Blog] PRIMARY KEY ([BlogID])
bubuko.com,布布扣
bubuko.com,布布扣
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
bubuko.com,布布扣
bubuko.com,布布扣
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
bubuko.com,布布扣
bubuko.com,布布扣
CREATE PROCEDURE [dbo].[Blog_Delete]
    @BlogID [int]
AS
BEGIN
    DELETE [dbo].[Blog]
    WHERE ([BlogID] = @BlogID)
END
bubuko.com,布布扣

  2、重新默认约定

  2.1、重命名存储过程名称

bubuko.com,布布扣
modelBuilder  
    .Entity<Blog>()  
    .MapToStoredProcedures(s =>  
        s.Update(u => u.HasName("Modify_Blog")));
bubuko.com,布布扣
bubuko.com,布布扣
modelBuilder  
    .Entity<Blog>()  
    .MapToStoredProcedures(s =>  
        s.Update(u => u.HasName("Update_Blog"))  
         .Delete(d => d.HasName("Delete_Blog"))  
         .Insert(i => i.HasName("Insert_Bblog")));
bubuko.com,布布扣
bubuko.com,布布扣
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"));  
     });
bubuko.com,布布扣
bubuko.com,布布扣
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"));
            });
        }
    }
}
View Code

  运行之后执行的SQL语句:

bubuko.com,布布扣
EXECUTE sp_rename @objname = Ndbo.Blog_Insert, @newname = NInsert_Blog, @objtype = NOBJECT
EXECUTE sp_rename @objname = Ndbo.Blog_Update, @newname = NUpdate_Blog, @objtype = NOBJECT
EXECUTE sp_rename @objname = Ndbo.Blog_Delete, @newname = NDelete_Blog, @objtype = NOBJECT
bubuko.com,布布扣

  2.2、重命名存储过程参数名称

bubuko.com,布布扣
modelBuilder  
  .Entity<Blog>()  
  .MapToStoredProcedures(s =>  
    s.Update(u => u.Parameter(b => b.BlogID, "Blog_ID")));
bubuko.com,布布扣
bubuko.com,布布扣
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"));
            });
        }
    }
}
View Code

  运行之后执行的SQL语句:

bubuko.com,布布扣
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
bubuko.com,布布扣
bubuko.com,布布扣
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")));
bubuko.com,布布扣

  2.3、重命名数据库自动生成列的返回值的列名

bubuko.com,布布扣
modelBuilder 
  .Entity<Blog>() 
  .MapToStoredProcedures(s => 
    s.Insert(i => i.Result(b => b.BlogID, "generated_blog_identity")));
bubuko.com,布布扣
bubuko.com,布布扣
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"));
            });
        }
    }
}
View Code

  运行之后执行的SQL语句:

bubuko.com,布布扣
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
bubuko.com,布布扣

Entity Framework 6 Code First新特性:支持存储过程,布布扣,bubuko.com

Entity Framework 6 Code First新特性:支持存储过程

原文:http://www.cnblogs.com/libingql/p/3606268.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!