[EF] Code First 產生 Table

練習 ASP.NET Web API 2 中的屬性路由建立 REST API 時，第一個步驟是利用 EF Code First Migration 來建立 DB 和 Table，重點畫面截圖紀錄一下

建立一個空白 WebAPI 專案

預設是會啟動 HTTPS，練習用就把它取消

建立 Model

Author class

using System.ComponentModel.DataAnnotations;

namespace BooksAPI.Models
{
    public class Author
    {
        public int AuthorId { get; set; }
        [Required]
        public string Name { get; set; }
    }
}

Book Class

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace BooksAPI.Models
{
    public class Book
    {
        public int BookId { get; set; }
        [Required]
        public string Title { get; set; }
        public decimal Price { get; set; }
        public string Genre { get; set; }
        public DateTime PublishDate { get; set; }
        public string Description { get; set; }
        public int AuthorId { get; set; }
        [ForeignKey("AuthorId")]
        public Author Author { get; set; }
    }
}

建立完 Class，請先建置專案，以利後續操作

新增 Web API 控制器

新增 [具有動作、使用 Entity Framework 的 Web API 2 控制器]

假如建立 Model 後，沒有建置專案，新增控制器時會出現下圖錯誤

新增控制器後的專案，可以發現 BooksAPIContext 是放在 Data 資料夾內

透過 Scaffolding 產生的 BooksControl 內容

namespace BooksAPI.Controllers
{
    public class BooksController : ApiController
    {
        private BooksAPIContext db = new BooksAPIContext();

        // GET: api/Books
        public IQueryable GetBooks()
        {
            return db.Books;
        }

        // GET: api/Books/5
        [ResponseType(typeof(Book))]
        public IHttpActionResult GetBook(int id)
        {
            Book book = db.Books.Find(id);
            if (book == null)
            {
                return NotFound();
            }

            return Ok(book);
        }

        // PUT: api/Books/5
        [ResponseType(typeof(void))]
        public IHttpActionResult PutBook(int id, Book book)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (id != book.BookId)
            {
                return BadRequest();
            }

            db.Entry(book).State = EntityState.Modified;

            try
            {
                db.SaveChanges();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!BookExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return StatusCode(HttpStatusCode.NoContent);
        }

        // POST: api/Books
        [ResponseType(typeof(Book))]
        public IHttpActionResult PostBook(Book book)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            db.Books.Add(book);
            db.SaveChanges();

            return CreatedAtRoute("DefaultApi", new { id = book.BookId }, book);
        }

        // DELETE: api/Books/5
        [ResponseType(typeof(Book))]
        public IHttpActionResult DeleteBook(int id)
        {
            Book book = db.Books.Find(id);
            if (book == null)
            {
                return NotFound();
            }

            db.Books.Remove(book);
            db.SaveChanges();

            return Ok(book);
        }

        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }

        private bool BookExists(int id)
        {
            return db.Books.Count(e => e.BookId == id) > 0;
        }
    }
}

Migration 功能

套件管理員內輸入指令：Enable-Migrations 來啟用

專案內會出現 Migrations 資料夾

Configuation  內設定 Seed 功能，建立 DB 和 Table 時也一併把資料塞進去

namespace BooksAPI.Migrations
{
    internal sealed class Configuration : DbMigrationsConfiguration
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(BooksAPI.Data.BooksAPIContext context)
        {
            context.Authors.AddOrUpdate(new Author[] {
                new Author() { AuthorId = 1, Name = "Ralls, Kim" },
                new Author() { AuthorId = 2, Name = "Corets, Eva" },
                new Author() { AuthorId = 3, Name = "Randall, Cynthia" },
                new Author() { AuthorId = 4, Name = "Thurman, Paula" }
                });

            context.Books.AddOrUpdate(new Book[] {
                new Book() { BookId = 1,  Title= "Midnight Rain", Genre = "Fantasy",
                PublishDate = new DateTime(2000, 12, 16), AuthorId = 1, Description =
                "A former architect battles an evil sorceress.", Price = 14.95M },

                new Book() { BookId = 2, Title = "Maeve Ascendant", Genre = "Fantasy",
                    PublishDate = new DateTime(2000, 11, 17), AuthorId = 2, Description =
                    "After the collapse of a nanotechnology society, the young" +
                    "survivors lay the foundation for a new society.", Price = 12.95M },

                new Book() { BookId = 3, Title = "The Sundered Grail", Genre = "Fantasy",
                    PublishDate = new DateTime(2001, 09, 10), AuthorId = 2, Description =
                    "The two daughters of Maeve battle for control of England.", Price = 12.95M },

                new Book() { BookId = 4, Title = "Lover Birds", Genre = "Romance",
                    PublishDate = new DateTime(2000, 09, 02), AuthorId = 3, Description =
                    "When Carla meets Paul at an ornithology conference, tempers fly.", Price = 7.99M },

                new Book() { BookId = 5, Title = "Splish Splash", Genre = "Romance",
                    PublishDate = new DateTime(2000, 11, 02), AuthorId = 4, Description =
                    "A deep sea diver finds true love 20,000 leagues beneath the sea.", Price = 6.99M},
            });
        }
    }
}

套件管理員內輸入指令：Add-Migration Initial，Inital 為識別名稱，可自行取名

在 Migration 紀錄內就會出現 Initial 紀錄

套件管理員內輸入：Update-Database

從 Web.config 檔案內可以發現是連線至 LocalDB

<connectionStrings>
    <add name="BooksAPIContext" connectionString="Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=BooksAPIContext-20200529235631; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|BooksAPIContext-20200529235631.mdf"
      providerName="System.Data.SqlClient" />
</connectionStrings>

利用 SSMS 來連線 LocalDB 來查詢 DB 和 Table