先有資料庫，然後想用 EF Code First

快速記一下，已經先有資料庫，又想使用 Code First 模型的解決方案...

並不是每個案子都是從無到有的 green field 專案；已經先有資料庫，卻想要使用 Entity Framework 的 Code First 模型，這種情況肯定會有的。這裡簡略記一下試過的解決方案。

工具：Visual Studio 2012 Update 1 + Entity Framework 5 或 Entity Framework 6 Alpha 2

EF Power Tools

MSDN 網站上有一篇文章：Code First to an Existing Database，裡面用的工具是 EF Power Tools。我嘗試用它對資料庫做逆向工程來以產生 POCO 類別，結果並不順利...

首先碰到的錯誤是：

System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.DbContextPackage.Extensions.ProjectExtensions.InstallPackage(Project project, String packageId)
   at Microsoft.DbContextPackage.Handlers.ReverseEngineerCodeFirstHandler.ReverseEngineerCodeFirst(Project project)

One or more errors occurred while processing template 'Entity.tt'.
C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\Templates\Includes\EF.Utility.CS.ttinclude(1,4) : error : A processor named 'T4VSHost' could not be found for the directive named 'CleanupBehavior'. The transformation will not be run.  The following Exception was thrown:
System.InvalidOperationException: Cannot find processor for directive 'T4VSHost'.
   at Microsoft.DbContextPackage.Utilities.EfTextTemplateHost.Microsoft.VisualStudio.TextTemplating.ITextTemplatingEngineHost.ResolveDirectiveProcessor(String processorName)
   at Microsoft.VisualStudio.TextTemplating.Engine.ProcessCustomDirectives(ITextTemplatingEngineHost host, TemplateProcessingSession session, IEnumerable`1 directivesToBeProcessed)

臨時解法在這裡可以找到：

http://connect.microsoft.com/VisualStudio/feedback/details/769934/entityframework-bug-in-vs2012-update-1-ctp

此問題解決後，Reverse Engineer Code First 功能已經可以順利執行。接著編譯專案，又會發現另一個問題：好多編譯錯誤。主要是這個：

Compiling transformation: The type or namespace name 'EfTextTemplateHost' could not be found (are you missing a using directive or an assembly reference?)

官方網站有篇文章有提供臨時解法：http://msdn.microsoft.com/en-us/data/jj593170.aspx。然而，也許是忙中有錯，我試了之後仍有同樣的編譯錯誤，問題沒有解決。後來在一個練習用的 Entity Framework 6 Alpha 2 專案中測試，結果也是出現編譯錯誤。

以上問題似乎是在 Visual Studio 2012 Update 1 才會碰到，先前的版本也許會順一點。但無論如何，上述兩個臨時解的步驟都太多了，對於有時間練功研究的人來說不妨一試，但如果是實際的專案開發，還是趁早放棄另尋它法為妙。

EF Reverse POCO Code First Generator

另外試了另一個逆向工程工具：EntityFramework Reverse POCO Code First Generator。初步測試還蠻順的，使用方法也相當簡單。

我也在 EF 6 Alpha 2 的練習專案上簡單測試了一下，可以順利產生 Northwind 範例資料庫的 POCO，而且是將全部的 POCO 類別產生在一個 .cs 檔案中。我將此工具產生出來的 POCO 檔案部分內容貼在這裡：

// This file was automatically generated.
// Do not make changes directly to this file - edit the template instead.
// 
// The following connection settings were used to generate this file
// 
//     Connection String Name: "Northwind"
//     Connection String:      "server=localhost;database=Northwind;Integrated Security=true; Application Name=Test"

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;

namespace EF6CodeFirstExistDatabase
{
    // ************************************************************************
    // Database context
    public class NorthwindEntities : DbContext
    {
        public IDbSet<OrderDetails> OrderDetails { get; set; } // Order Details
        public IDbSet<Orders> Orders { get; set; } // Orders

        static NorthwindEntities()
        {
            Database.SetInitializer<NorthwindEntities>(null);
        }

        public NorthwindEntities()
            : base("Name=Northwind")
        {
        }

        public NorthwindEntities(string connectionString)
            : base(connectionString)
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Configurations.Add(new CustomersConfiguration());
            modelBuilder.Configurations.Add(new EmployeesConfiguration());
));
            modelBuilder.Configurations.Add(new OrderDetailsConfiguration());
            modelBuilder.Configurations.Add(new OrdersConfiguration());
        }
    }

    // ************************************************************************
    // POCO classes


    // Order Details
    public class OrderDetails
    {
        public int OrderId { get; set; } // OrderID (Primary key)
        public int ProductId { get; set; } // ProductID (Primary key)
        public decimal UnitPrice { get; set; } // UnitPrice
        public short Quantity { get; set; } // Quantity
        public float Discount { get; set; } // Discount

        // Foreign keys
        public virtual Orders Orders { get; set; } //  OrderId - FK_Order_Details_Orders
        public virtual Products Products { get; set; } //  ProductId - FK_Order_Details_Products

        public OrderDetails()
        {
            UnitPrice = 0m;
            Quantity = 1;
            Discount = 0;
        }
    }

    // Orders
    public class Orders
    {
        public int OrderId { get; set; } // OrderID (Primary key)
        public string CustomerId { get; set; } // CustomerID
        public int? EmployeeId { get; set; } // EmployeeID
        public DateTime? OrderDate { get; set; } // OrderDate
        public DateTime? RequiredDate { get; set; } // RequiredDate
        public DateTime? ShippedDate { get; set; } // ShippedDate
        // ...省略部分屬性...

        // Reverse navigation
        public virtual ICollection<OrderDetails> OrderDetails { get; set; } // Order Details.FK_Order_Details_Orders;

        // Foreign keys
        public virtual Customers Customers { get; set; } //  CustomerId - FK_Orders_Customers
        public virtual Employees Employees { get; set; } //  EmployeeId - FK_Orders_Employees
        public virtual Shippers Shippers { get; set; } //  ShipVia - FK_Orders_Shippers

        public Orders()
        {
            Freight = 0m;
            OrderDetails = new List<OrderDetails>();
        }
    }


    // ************************************************************************
    // POCO Configuration

    // Order Details
    public class OrderDetailsConfiguration : EntityTypeConfiguration<OrderDetails>
    {
        public OrderDetailsConfiguration()
        {
            ToTable("dbo.Order Details");
            HasKey(x => new { x.OrderId, x.ProductId });

            Property(x => x.OrderId).HasColumnName("OrderID").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            Property(x => x.ProductId).HasColumnName("ProductID").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
            Property(x => x.UnitPrice).HasColumnName("UnitPrice").IsRequired();
            Property(x => x.Quantity).HasColumnName("Quantity").IsRequired();
            Property(x => x.Discount).HasColumnName("Discount").IsRequired();

            // Foreign keys
            HasRequired(a => a.Orders).WithMany(b => b.OrderDetails).HasForeignKey(c => c.OrderId); // FK_Order_Details_Orders
            HasRequired(a => a.Products).WithMany(b => b.OrderDetails).HasForeignKey(c => c.ProductId); // FK_Order_Details_Products
        }
    }

    // Orders
    public class OrdersConfiguration : EntityTypeConfiguration<Orders>
    {
        public OrdersConfiguration()
        {
            ToTable("dbo.Orders");
            HasKey(x => x.OrderId);

            Property(x => x.OrderId).HasColumnName("OrderID").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            Property(x => x.CustomerId).HasColumnName("CustomerID").IsOptional().HasMaxLength(5);
            Property(x => x.EmployeeId).HasColumnName("EmployeeID").IsOptional();
            Property(x => x.OrderDate).HasColumnName("OrderDate").IsOptional();

            // Foreign keys
            HasOptional(a => a.Customers).WithMany(b => b.Orders).HasForeignKey(c => c.CustomerId); // FK_Orders_Customers
            HasOptional(a => a.Employees).WithMany(b => b.Orders).HasForeignKey(c => c.EmployeeId); // FK_Orders_Employees
            HasOptional(a => a.Shippers).WithMany(b => b.Orders).HasForeignKey(c => c.ShipVia); // FK_Orders_Shippers
        }
    }
}

然後寫點小程式測試一下，也沒問題：

static void Main(string[] args)
{
    using (var db = new NorthwindEntities())
    {
        var customers = from t in db.Customers
                        select t;
        foreach (var c in customers.Take(5))
        {
            Console.WriteLine(c.CompanyName);
        }
    }
}

不過，實務上恐怕多少會碰到一些狀況。目前沒有太多時間細究，改天若有進一步測試再補上來。

（有好心人要分享一下嗎其他解決方案嗎？）