EF6不支援sqlite Code First解決方案

標籤：context   work   void   添加   struct   地方   data   param   定義   

最近需要項目中需要用到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);            }        }    }

 

EF6不支援sqlite Code First解決方案