文章目录
目前 EF 是 .NET 平台下相当成熟的 ORM 框架,但是其最新发布的 6.x 版本依然不支持 SQLite 的 CodeFirst 模式
解决方案
安装依赖包
首先我们创建一个控制台程序,安装如下 Package:
System.Data.SQLite
SQLite.CodeFirst
创建模型对象 Person.cs
public class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
项目中添加ADO.NET实体数据
选空CODE FIRST模型,创建数据上下文 MYDB.cs。
OnModelCreating方法必须被重写,以建立数据表格,这一步在用LOCALDB时是不用的
建立Person的DBSET
public class MYDB : DbContext
{
public MYDB() : base("name = mydbcfg")
{
ConfigurationFunc();
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var initializer = new SqliteDropCreateDatabaseWhenModelChanges<MYDB>(modelBuilder); //模型变更时重建数据库
Database.SetInitializer(initializer);
}
public DbSet<Person> People { get; set; }
}
修改程序配置 App.config
如providers, connectionStrings
<entityFramework>
...
<providers>
<!--原invariantName="System.Data.SQLite.EF6"改为invariantName="System.Data.SQLite"-->
<provider invariantName="System.Data.SQLite" (type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
....
<!--connectionString要改动一下,只有data source和 providerName-->
<connectionStrings>
<add name="MYDB" connectionString="data source=.\sampledb.db" providerName="System.Data.SQLite" />
</connectionStrings>
</configuration>
主程序调用 Program.cs#
using(var db = new MYDB()){
#region 预热:针对数据表较多的情况下建议执行下述操作
var objectContext = ((IObjectContextAdapter)context).ObjectContext;
var mappingColection = (StorageMappingItemCollection)objectContext.MetadataWorkspace.GetItemCollection(DataSpace.CSSpace);
mappingColection.GenerateViews(new List<EdmSchemaError>());
#endregion
db.People.Add(new Person { FirstName = "a", LastName = "b" });
db.SaveChanges();
}
重要的坑
contains或indexof查询
在EF查询中,采用Where(p => p.stringColum.Contains(searchKey)的结果总是很奇怪,这是因为EF解释contains或indexOf成的CHARINDEX函数在sqlite里面并不支持。解决办法是自己写一个拦截器
public class SqliteInterceptor : IDbCommandInterceptor
{
private static Regex replaceRegex = new Regex(@"\(CHARINDEX\((.*?),\s?(.*?)\)\)\s*?>\s*?0");
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
{
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
ReplaceCharIndexFunc(command);
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
{
ReplaceCharIndexFunc(command);
}
private void ReplaceCharIndexFunc(DbCommand command)
{
bool isMatch = false;
var text = replaceRegex.Replace(command.CommandText, (match) =>
{
if (match.Success)
{
string paramsKey = match.Groups[1].Value;
string paramsColumnName = match.Groups[2].Value;
//replaceParams
foreach (DbParameter param in command.Parameters)
{
if (param.ParameterName == paramsKey.Substring(1))
{
param.Value = string.Format("%{0}%", param.Value);
break;
}
}
isMatch = true;
return string.Format("{0} LIKE {1}", paramsColumnName, paramsKey);
}
else
return match.Value;
});
if (isMatch)
command.CommandText = text;
}
}
然后在context类中调用:
public MYDB(): base("name=MYDB")
{
DbInterception.Add(new SqliteInterceptor());
}
数据库相对路径问题(在XP下)
见https://blog.csdn.net/kindmb/article/details/102328189
综述
与使用LOCALDB相比,用SQLITE需要做以下不同的修改:
1、安装SQLITE的NUGET包System.Data.SQLite和SQLite.CodeFirst
2、修改程序配置 App.config的provider与connectionStrings
3、覆写DbContext的OnModelCreating方法
Database.SetInitializer(new SqliteDropCreateDatabaseWhenModelChanges<MYDB>(modelBuilder));