Using Views with EF CodeFirst

-Using Views with EF CodeFirst

Otomatik Procedure oluşumu sağlanması
Custom parametreli ve parametresiz procedure oluşumu sağlanması
Custom procedure kullanımı
View oluşumuve kullanımı sağlanması

00. HomeController.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace WebApplication1
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            DatabaseContext db = new DatabaseContext();
            db.Books.ToList();

            db.ExecuteInsertFakeData();
            var result = db.ExecuteGetBooksGroupByPublishedDateSP(1999, 2010);
            var result2 = db.GetBookInfos();
            var result3 = db.GetBookInfos(4);

            return View();
        }
    }

    public class DatabaseContext : DbContext
    {
        public DbSet<Book> Books { get; set; }

        public DatabaseContext()
        {
            Database.SetInitializer(new DbInitializer());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Book>()
                .MapToStoredProcedures(config =>
                {
                    config.Insert(i => i.HasName("BookInsertSP"));
                    config.Update(u =>
                    {
                        u.HasName("BookUpdateSP");
                        u.Parameter(p => p.Id, "bookId");
                    });
                    config.Delete(d => d.HasName("BookDeleteSP"));
                });
        }


        public void ExecuteInsertFakeData()
        {
            Database.ExecuteSqlCommand("EXEC InsertFakeDataSP");
        }

        public List<BookGroupByPublishedDate> ExecuteGetBooksGroupByPublishedDateSP(int startyear, int endyear)
        {
            return
                Database.SqlQuery<BookGroupByPublishedDate>(
                    "EXEC GetBooksGroupByPublishedDateSP @p0, @p1",
                        startyear, endyear)
                    .ToList();
        }

        public List<BookInfo> GetBookInfos()
        {
            return Database.SqlQuery<BookInfo>("SELECT * FROM GetBooksInfoVW").ToList();
        }

        public List<BookInfo> GetBookInfos(int minId)
        {
            return Database.SqlQuery<BookInfo>("SELECT * FROM GetBooksInfoVW WHERE Id > @p0", minId).ToList();
        }
    }

    public class DbInitializer : CreateDatabaseIfNotExists<DatabaseContext>
    {
        protected override void Seed(DatabaseContext context)
        {
            //context.Database.ExecuteSqlCommand("SELECT * FROM Books WHERE Id = @p0 AND Id = @p1", 5, 6);

            //context.Database.ExecuteSqlCommand("SELECT * FROM Books WHERE Id = @ilk AND Id = @son",
            //    new SqlParameter("@ilk",5),
            //    new SqlParameter("@son",6));

            // Create SP : InsertFakeDataSP
            context.Database.ExecuteSqlCommand(
                @"CREATE PROCEDURE InsertFakeDataSP
                    AS
                    BEGIN
	                    INSERT INTO [dbo].[Books] ([Name] ,[Description] ,[PublishedDate]) VALUES ('Da Vinci Code' ,'Da Vinci Şifresi' ,'2003-02-01')
	                    INSERT INTO [dbo].[Books] ([Name] ,[Description] ,[PublishedDate]) VALUES ('Angels & Demons' ,'Melekler ve Şeytanlar' ,'2000-03-30')
	                    INSERT INTO [dbo].[Books] ([Name] ,[Description] ,[PublishedDate]) VALUES ('Lost Symbol' ,'Kayıp Sembol' ,'2009-01-29')
                    END");

            // Create SP : GetBooksGroupByPublishedDateSP
            context.Database.ExecuteSqlCommand(
                @"CREATE PROCEDURE GetBooksGroupByPublishedDateSP 
	                @p0 int, 	--startdate
	                @p1 int		--enddate
                AS
                BEGIN
	                SELECT 
		                TBL.PublishedDate,
		                COUNT(TBL.PublishedDate) AS [Count]
	                FROM (
			                SELECT 
				                YEAR(PublishedDate) AS PublishedDate
			                FROM Books
			                WHERE YEAR(PublishedDate) BETWEEN @p0 AND @p1
		                ) AS TBL
	                GROUP BY TBL.PublishedDate
                END");

            // Create View : GetBooksInfoVW
            context.Database.ExecuteSqlCommand(
                @"CREATE VIEW [dbo].[GetBooksInfoVW]
                    AS
                    SELECT        
	                    Id, 
	                    Name + ' : ' + Description + ' (' + CONVERT(nvarchar(20), PublishedDate) + ')' AS Info
                    FROM dbo.Books");
        }
    }

    public class BookGroupByPublishedDate
    {
        public int PublishedDate { get; set; }
        public int Count { get; set; }
    }

    public class BookInfo
    {
        public int Id { get; set; }
        public string Info { get; set; }
    }


    [Table("Books")]
    public class Book
    {
        [Key]
        public int Id { get; set; }
        [Required, StringLength(50)]
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime PublishedDate { get; set; }
    }
}