在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