EF6不支持sqlite Code First解决方案

最近需要项目中需要用到sqlite，项目中其他的功能都是EF+sqlserver实现的数据访问。于是，想用EF来访问sqlite，两个比较麻烦的地方。

第一：EF连接sqlite配置文件需要手动改一下

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v13.0" />
      </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" />//这里通过nuget添加sqlite的EF的dll后，生成的是：System.Data.SQLite.EF6，改成现在这样就行了。
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
      <remove invariant="System.Data.SQLite" />
      <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>

 

第二：EF6默认不支持sqlite Code First生成数据库，需要自己扩展。

public partial class SqliteDataModels : DbContext
    {
        private string _dbPath;
        public SqliteDataModels(string path)
            : base(new SQLiteConnection
            {
                ConnectionString = new SQLiteConnectionStringBuilder
                {
                    DataSource = path,
                    ForeignKeys = false,
                    BinaryGUID = false,
                }.ConnectionString
            }, true)
        {
            _dbPath = path;            
        }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();//
            Database.SetInitializer(new SqliteContextInitializer<SqliteDataModels>(_dbPath, modelBuilder));//这句话是关键。自定义数据库上下文初始化设置
            base.OnModelCreating(modelBuilder);            
        }

        public virtual DbSet<t_analysisBooks> t_analysisBooks { get; set; }
        public virtual DbSet<t_GeneraLedgerStatistics> t_GeneraLedgerStatistics { get; set; }
        public virtual DbSet<t_LedgerClassificationChart> t_LedgerClassificationChart { get; set; }
    }

    class SqliteContextInitializer<T> : IDatabaseInitializer<T>
       where T : DbContext
    {
        bool _dbExists;
        DbModelBuilder _modelBuilder;

        public SqliteContextInitializer(string dbPath, DbModelBuilder modelBuilder)
        {
            _dbExists = File.Exists(dbPath);
            _modelBuilder = modelBuilder;
        }

        public void InitializeDatabase(T context)
        {
            if (_dbExists)
                return;

            var model = _modelBuilder.Build(context.Database.Connection);

            using (var xact = context.Database.BeginTransaction())
            {
                try
                {
                    CreateDatabase(context.Database, model);
                    xact.Commit();
                }
                catch (Exception ex)
                {
                    xact.Rollback();
                    throw;
                }
            }
        }
       
        private void CreateDatabase(Database db, DbModel model)
        {
            const string tableTmpl = "CREATE TABLE [{0}] (\n{1}\n);";
            const string columnTmpl = "    [{0}] {1} {2}"; // name, type, decl    

            foreach (var type in model.StoreModel.EntityTypes)
            {
                var defs = new List<string>();
                //主键
                var keys = type.KeyProperties.Select(x => x.Name).ToList();
                
                foreach (var p in type.Properties)
                {
                    var decls = new HashSet<string>();
                    //创建列autoincrement
                    if (keys.Contains(p.Name.ToString()))
                    {
                        decls.Add("INTEGER PRIMARY KEY Autoincrement");
                        defs.Add(string.Format(columnTmpl, p.Name, "", string.Join(" ", decls)));
                    }
                    else
                    {
                        if (!p.Nullable)
                            decls.Add("NOT NULL");
                        defs.Add(string.Format(columnTmpl, p.Name, p.TypeName, string.Join(" ", decls)));
                    }
                }
                //创建表
                var sql = string.Format(tableTmpl, type.Name, string.Join(",\n", defs));
                db.ExecuteSqlCommand(sql);
            }
        }
    }