你的位置:首页 > ASP.net教程

[ASP.net教程]Entity Framework 6 Code First 系列: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; }  }

View Code

 

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);    }  }

View Code

 

3.定义初始化数据:目前SQLite.CodeFist只支持DropCreateDatabaseAlways和CreateDatabaseIfNotExists方式,从issues上看也就是几天的事。

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);    }  }

View Code

 

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    }  }

View Code

 

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

<??><!-- 有关如何配置 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 ="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>

View Code

 

6.配置Global.asax。

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

View Code

 

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

调用一下:

Demo下载