EntityFramework使用CodeFirst模式管理存储过程

CREATE PROCEDURE [dbo].[SelectBooks]
@BookTypeName AS NVARCHAR(10)
AS
BEGIN
select B.Name,B.Author,B.PublicationDate,T.BookTypeName from Books as B
join BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeName
END

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.Model
{
    public class Book
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public string Author { get; set; }

        public DateTime PublicationDate { get; set; }

        public virtual BookType BookType { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.Model
{
    public class BookType
    {
        public BookType()
        {
            Books = new HashSet<Book>();
        }

        public int BookTypeId { get; set; }

        public string BookTypeName { get; set; }

        public virtual ICollection<Book> Books { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.Model
{
    public class BookFromProcedure
    {
        public string Name { get; set; }

        public string Author { get; set; }

        public DateTime PublicationDate { get; set; }

        public string BookTypeName { get; set; }
    }
}

using CodeFirstProcedureApp.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.EF
{
    public class Initializer : DropCreateDatabaseIfModelChanges<EFDbContext>
    {
        protected override void Seed(EFDbContext context)
        {
            // 创建初始化数据
            BookType bookType = new BookType()
            {
                BookTypeName = "文学小说",
                Books = new List<Book>
                {
                  new Book(){Name="人间失格",Author="太宰治",PublicationDate=DateTime.Parse("2015-08-01")},
                  new Book(){Name="解忧杂货店",Author="东野圭吾",PublicationDate=DateTime.Parse("2014-05-01")},
                  new Book(){Name="追风筝的人",Author="卡勒德胡赛尼",PublicationDate=DateTime.Parse("2006-08-01")},
                  new Book(){Name="百年孤独",Author="加西亚马尔克斯",PublicationDate=DateTime.Parse("2011-06-01")},
                  new Book(){Name="霍乱时期的爱情",Author="加西亚马尔克斯",PublicationDate=DateTime.Parse("2015-06-01")}
                }
            };

            BookType bookType2 = new BookType()
            {
                BookTypeName = "科学",
                Books = new List<Book>
                {
                   new Book(){Name="人类简史",Author="尤瓦尔赫拉利",PublicationDate=DateTime.Parse("2017-01-01")}
                }
            };

            context.BookTypes.Add(bookType);
            context.BookTypes.Add(bookType2);
            base.Seed(context);
        }
    }
}

using CodeFirstProcedureApp.Model;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeFirstProcedureApp.EF
{
    public class EFDbContext :DbContext
    {
        public EFDbContext()
            : base("name=AppConnection")
        {
            Database.SetInitializer(new Initializer());
        }

        // 添加到数据上下文中
        public DbSet<Book> Books { get; set; }

        public DbSet<BookType> BookTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            // 配置表名和主键
            modelBuilder.Entity<Book>().ToTable("Books").HasKey(p => p.Id);
            modelBuilder.Entity<BookType>().ToTable("BookTypes").HasKey(p => p.BookTypeId);
            // 设置实体关系
            //  BookType和 Books 一对多关系 外键：BookTypeId
            modelBuilder.Entity<BookType>().HasMany(p => p.Books).WithRequired(t => t.BookType)
                .Map(m =>
                {
                    m.MapKey("BookTypeId");
                });
            base.OnModelCreating(modelBuilder);
        }
    }
}

var createSql = @"CREATE PROCEDURE [dbo].[SelectBooks]
                                    @BookTypeName AS NVARCHAR(10)
                                    AS
                                    BEGIN
                                    select B.Name,B.Author,B.PublicationDate,T.BookTypeName from Books as B
                                    join BookTypes as T on B.BookTypeId=T.BookTypeId where T.BookTypeName=@BookTypeName
                                    END";
context.Database.ExecuteSqlCommand(createSql);

var sql = "SelectBooks {0}";
var books = context.Database.SqlQuery<BookFromProcedure>(sql, "文学小说");
books.ToList().ForEach(p =>
{
      Console.WriteLine("BookName:" + p.Name + "     Author:" + p.Author + "    BookTypeName:" + p.BookTypeName + "     PublicationDate:" + p.PublicationDate);
});

CREATE PROCEDURE UpdateBooks
@name AS NVARCHAR(60),
@id as int
AS 
BEGIN
UPDATE Books SET Name=@name
WHERE Id=@id
END

var sql = "UpdateBooks @name,@id";
SqlParameter[] para = new SqlParameter[]
{
       new SqlParameter("@id",1d),
       new SqlParameter("@name","人间失败"),
};
var book = context.Books.Where(p => p.Id == 1);
Console.WriteLine("执行存储过程前的数据为：");
foreach (var item in book)
{
       Console.WriteLine(item.Name + "\t" + item.Author + "\t" + item.PublicationDate);
}
var rowsAffected = context.Database.ExecuteSqlCommand(sql, para);
Console.WriteLine("影响的行数为{0}条", rowsAffected);
Console.WriteLine("执行存储过程之后的数据为：");
var books = context.Books.Where(p => p.Id == 1);
foreach (var item in books)
{
        Console.WriteLine(item.Name + "\t" + item.Author + "\t" + item.PublicationDate);
}