EntityFramework系列：SQLite.CodeFirst自动生成数据库

在Code First模式下使用SQLite一直存在不能自动生成数据库的问题，使用SQL Server Compact再转换到SQLite的方式（SQL Server Compact/SQLite Toolbox插件）基本不在我的考虑范围内，直接使用SQL Server Compact性能又是问题。理论上我们可以自己去实现SQLite的Code Frist支持，但实际上我只是在等待它的出现。期待了一年多，SQLite.CodeFirst真的出现了。

1.首先定义实体：

Customer、Role、Category、Post。

public class BaseEntity
    {
        public int Id { get; set; }
    }

    public class Customer : BaseEntity
    {
        public Customer()
        {
            this.Roles = new List<Role>();
        }

        public string UserName { get; set; }

        public virtual ICollection<Role> Roles { get; set; }
    }

    public class Role : BaseEntity
    {
        public Role()
        {
            this.Customers = new List<Customer>();
        }

        public virtual ICollection<Customer> Customers { get; set; }

        public string RoleName { get; set; }
    }

    public class Category : BaseEntity
    {
        public Category()
        {
            this.Children = new List<Category>();
            this.Posts = new List<Post>();
        }

        public int? ParentId { get; set; }

        public virtual Category Parent { get; set; }

        public virtual ICollection<Category> Children { get; set; }

        public virtual ICollection<Post> Posts { get; set; }
    }

    public class Post : BaseEntity
    {
        public virtual Category Category { get; set; }
    }

2.定义实体映射

CustomerMap、RoleMap、CategoryMap和PostMap作为关系表、索引的配置。

public class CustomerMap : EntityTypeConfiguration<Customer>
    {
        public CustomerMap()
        {
            this.Property(o => o.UserName).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute() { IsUnique = true }));
        }
    }

    public class RolerMap : EntityTypeConfiguration<Role>
    {
        public RolerMap()
        {
            this.HasMany(o => o.Customers).WithMany(o => o.Roles);
        }
    }

    public class CategoryMap : EntityTypeConfiguration<Category>
    {
        public CategoryMap()
        {
            this.HasOptional(o => o.Parent).WithMany(o => o.Children).HasForeignKey(o => o.ParentId);
        }
    }

    public class PostMap : EntityTypeConfiguration<Post>
    {
        public PostMap()
        {
            this.HasOptional(o => o.Category).WithMany(o => o.Posts);
        }
    }

3.定义初始化数据：

目前SQLite.CodeFist只支持DropCreateDatabaseAlways和CreateDatabaseIfNotExists方式。

public class MyDbInitializer : SqliteDropCreateDatabaseAlways<SqliteDbContext>
    {
        public MyDbInitializer(string connectionString, DbModelBuilder modelBuilder)
            : base(connectionString, modelBuilder) { }

        protected override void Seed(SqliteDbContext context)
        {
            context.Set<Customer>().Add(new Customer { UserName = "user" + DateTime.Now.Ticks.ToString(), Roles = new List<Role> { new Role { RoleName = "user" } } });
            context.Set<Post>().Add(new Post { Category = new Category() });
            base.Seed(context);
        }
    }

4.定义DbContext：

此处必须配置PluralizingTableNameConvention，否则无法正常使用。

public class SqliteDbContext : DbContext
    {
        public SqliteDbContext()
            : base("DefaultConnection")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Configurations.AddFromAssembly(typeof(SqliteDbContext).Assembly);
#if DEBUG
            Database.SetInitializer(new MyDbInitializer(Database.Connection.ConnectionString, modelBuilder));
#endif
        }
    }

5.配置Web.config:

默认的配置文件各种问题。可以直接拷贝项目中的测试用的配置文件。

<?xml version="1.0" encoding="utf-8"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=301880
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="data source=|DataDirectory|db.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security.OAuth" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security.Cookies" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Owin.Security" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-6.0.0.0" newVersion="6.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.5.2.14234" newVersion="1.5.2.14234" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.2.2.0" newVersion="5.2.2.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
</configuration>

6.配置Global.asax：

public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            using (var db = new SqliteDbContext())
            {
            }
            AreaRegistration.RegisterAllAreas();
            RouteConfig.RegisterRoutes(RouteTable.Routes);
        }
    }

查看生成的数据库：表映射、关系映射和索引都正确创建了。

调用一下：

代码已经上传到gitosc：http://git.oschina.net/myshare/SQLiteCodeFirst