首页 > 代码库 > 让EFCore更疯狂些的扩展类库(一)：通过json文件配置sql语句
让EFCore更疯狂些的扩展类库(一)：通过json文件配置sql语句
前言
EF通过linq和各种扩展方法，再加上实体模型，编写数据库的访问代码确实是优美、舒服，但是生成的sql不尽如意、性能低下，尤其是复杂些的逻辑关系，最终大家还是会回归自然，选择能够友好执行sql语句的ORM，认认真真的编写sql；问题是：EF是否也能够很友好的执行sql语句？EF提供直接执行sql语句的方法并不多，而且也是极其简单的；那是否容易进行扩展？答案是肯定的，在DbContext下提供了Database属性就是为了执行sql用的，然后自己就通过Database下的方法属性进行了扩展（不过最后为了各种数据库的兼容性，使用了DbContext的扩展方法GetService获取相应的服务进行sql语句的执行），以完成这个扩展类库的编写。
扩展类库大体功能简介：
1) sql语句执行器：用于直接执行sql语句
2) EF的查询缓存器：IQueryable(linq) 或 sql语句 的查询缓存，分为本地存储 或 非本地存储(Redis)
a) 缓存存储：永久缓存（不过期） 或者 过期缓存
b) 缓存清理
3) sql配置管理器（让EFCore像MyBatis配置sql，但是通过json配置）：加载与管理配置文件中的sql语句
a) sql配置执行器：用于执行配置的sql语句
b) 策略管理器：用于管理策略 与 策略执行器(目前分为三种策略执行器)
i. 策略管理：管理各种策略类型，用于初始化配置文件中的策略配置转换成对象
ii. 策略执行器(一般通过策略对象进行相应的处理)
1. 初始化型的策略执行器
a) 配置策略对象的初始化、替换表名、合并分部sql等的策略执行器
2. sql执行前的策略执行器
a) foreach策略执行器：对SqlParameter或者某些数据类型（list/dictionary/model）进行遍历生成字串替换到sql中
3. sql执行时的策略执行器
a) sql与参数的日志记录策略执行器
b) 查询缓存与清理策略执行器
4) 类库的扩展与优化（因为类库中的各种类是通过DI进行管理的，因此易于扩展与优化）
a) 将查询缓存存储到Redis中
b) 策略与策略执行器的扩展
c) 其他：例如反射帮助类的优化（如果有更好的实现，因为类库内部有不少实现需要通过反射）
源码：
github：https://github.com/skigs/EFCoreExtend
引用类库：
nuget：https://www.nuget.org/packages/EFCoreExtend/
PM> Install-Package EFCoreExtend
查询缓存引用Redis：
PM> Install-Package EFCoreExtend.Redis
类库的使用说明会分好几篇文章进行详细描述，也可参考源码（源码中也有使用测试），类库目前仅支持EFCore 1.1.0，兼容性：MSSqlServer、sqlite、mysql、PostgreSql基本都兼容(EFCore兼容的应该都可以兼容)，因为刚完成不久，可能还存在一些bug或不合理的地方，望大家谅解，也请告知。
通过json文件配置sql
Person.json配置文件内容：
{ //"name" : "Person", //设置表名，如果不指定name，那么默认文件名为表名 //配置sql：key为Sql的名称(SqlName，获取配置sql执行器的时候需要根据key获取) "sqls": { "GetList": { //"sql": "select name,birthday,addrid from [Person] where name=@name or id=@id", "sql": "select name,birthday,addrid from ##tname where name=@name or id=@id", //##tname => 表名 "type": "query" } } }
表的实体模型：
1 [Table(nameof(Person))] 2 public class Person 3 { 4 public int id { get; set; } 5 public string name { get; set; } 6 [Column(TypeName = "datetime")] 7 public DateTime? birthday { get; set; } 8 public int? addrid { get; set; } 9 }
DbContext(MSSqlServer、sqlite、mysql、PostgreSql)：
1 public class MSSqlDBContext : DbContext 2 { 3 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 4 { 5 if (optionsBuilder.IsConfigured == false) 6 { 7 optionsBuilder.UseSqlServer(@"data source=localhost;initial catalog=TestDB;uid=sa;pwd=123;"); 8 } 9 base.OnConfiguring(optionsBuilder); 10 } 11 12 public DbSet<Person> Person { get; set; } 13 } 14 15 public class SqlieteDBContext : DbContext 16 { 17 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 18 { 19 if (optionsBuilder.IsConfigured == false) 20 { 21 optionsBuilder.UseSqlite(@"data source=./Datas/db.sqlite"); //把/Datas/db.sqlite放到bin下 22 } 23 base.OnConfiguring(optionsBuilder); 24 } 25 26 public DbSet<Person> Person { get; set; } 27 } 28 29 public class MysqlDBContext : DbContext 30 { 31 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 32 { 33 if (optionsBuilder.IsConfigured == false) 34 { 35 //SapientGuardian.EntityFrameworkCore.MySql 36 optionsBuilder.UseMySQL(@"Data Source=localhost;port=3306;Initial Catalog=testdb;user id=root;password=123456;"); 37 } 38 base.OnConfiguring(optionsBuilder); 39 } 40 41 public DbSet<Person> Person { get; set; } 42 } 43 44 public class PostgreSqlDBContext : DbContext 45 { 46 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 47 { 48 if (optionsBuilder.IsConfigured == false) 49 { 50 optionsBuilder.UseNpgsql(@"User ID=admin;Password=123456;Host=localhost;Port=5432;Database=TestDB;Pooling=true;"); 51 } 52 base.OnConfiguring(optionsBuilder); 53 } 54 55 public DbSet<Person> Person { get; set; } 56 }
加载配置文件（在程序初始化的时候调用）：
1 ////加载指定的配置文件 2 //EFHelper.Services.SqlConfigMgr.Config.LoadFile(Directory.GetCurrentDirectory() + "/Person.json"); 3 //加载指定目录下的所有json配置文件 4 EFHelper.Services.SqlConfigMgr.Config.LoadDirectory(Directory.GetCurrentDirectory() + "/Datas");
获取与调用配置sql的代码：
1 DbContext db = new MSSqlDBContext(); 2 //获取指定表(配置文件名)的配置信息 3 var tinfo = db.GetConfigTable<Person>(); 4 //获取指定sql的执行器 5 var exc = tinfo.GetExecutor(); //使用了CallerMemberNameAttribute，因此会自动获取 方法/属性名 作为参数 6 var exc1 = tinfo.GetExecutor("GetList"); //这行和上面的一样，"GetList"为在配置文件配置的key 7 8 //执行sql： 9 //方式一：使用SqlParameter传递sql参数 10 var rtn1 = exc.Query<Person>( //泛型为返回值数据类型 11 //SqlParams 12 new [] { new SqlParameter("name", "tom"), new SqlParameter("id", 1) }, 13 //返回值类型中需要忽略的属性 14 new[] { "id" }); //select name,birthday,addrid，并没有加载获取id，因此需要忽略，否则抛异常 15 16 //方式二：使用Dictionary传递sql参数 17 var rtn2 = exc.QueryUseDict<Person>( //泛型为返回值数据类型 18 //Dictionary => SqlParams 19 new Dictionary<string, object> 20 { 21 { "name", "tom" }, 22 { "id", 1 }, 23 }, 24 //返回值类型中需要忽略的属性 25 new[] { "id" }); //select name,birthday,addrid，并没有加载获取id，因此需要忽略，否则抛异常 26 27 //方式三：使用Model传递sql参数 28 var rtn3 = exc.QueryUseModel<Person>( 29 //Model => SqlParams 30 new { name = "tom", id = 1, addrid = 123 }, 31 //参数Model需要忽略的属性 32 new[] { "addrid" }, //where name=@name or id=@id，并不需要设置addrid 33 //返回值类型中需要忽略的属性 34 new[] { "id" }); //select name,birthday,addrid，并没有加载获取id，因此需要忽略，否则抛异常
增删改查sql语句配置内容：
{ //"name" : "Person", //设置表名，如果不指定name，那么默认文件名为表名 "policies": { ////表名策略 //"tname": { // //"tag": "##tname" //默认值为 ##tname // "prefix": "[", //前缀 // "suffix": "]" //后缀 //} }, //配置sql：key为Sql的名称(SqlName，获取配置sql执行器的时候需要根据key获取) "sqls": { "GetList": { //"sql": "select * from [Person] where name=@name", "sql": "select * from ##tname where name=@name", //##tname => Table Name "type": "query" //可以不设置，如果设置了会在执行前进行类型检测， // notsure（默认，不确定），query（查询）, nonquery（非查询），scalar，nonexecute（不用于执行的sql，例如分部sql） }, "GetPerson": { "sql": "select * from ##tname where name=@name", "type": "query" }, "Count": { "sql": "select count(*) from ##tname", "type": "scalar" }, "UpdatePerson": { "sql": "update ##tname set birthday=@birthday, addrid=@addrid where name=@name", "type": "nonquery" }, "AddPerson": { "sql": "insert into ##tname(name, birthday, addrid) values(@name, @birthday, @addrid) ", "type": "nonquery" }, "DeletePerson": { "sql": "delete from ##tname where name=@name", "type": "nonquery" }, //执行存储过程 "ProcQuery": { "sql": "exec TestQuery @name", "type": "query" }, "ProcUpdate": { "sql": "exec TestUpdate @addrid,@name", "type": "nonquery" } } }
调用sql配置的代码（包括事物处理）：
1 public class PersonBLL 2 { 3 string _name = "tom"; 4 DBConfigTable tinfo; 5 public PersonBLL(DbContext db) 6 { 7 //获取指定表(配置文件名)的配置信息 8 tinfo = db.GetConfigTable<Person>(); 9 } 10 11 public IReadOnlyList<Person> GetList() 12 { 13 return tinfo.GetExecutor().QueryUseModel<Person>( 14 //Model => SqlParams 15 new { name = _name, id = 123 }, 16 //不需要的SqlParams 17 new[] { "id" }, 18 //返回值类型需要忽略的属性 19 new[] { "name" }); 20 21 } 22 23 public int AddPerson() 24 { 25 return tinfo.GetExecutor() //获取sql执行器 26 .NonQueryUseModel(new Person 27 { 28 addrid = 1, 29 birthday = DateTime.Now, 30 name = _name, 31 }, null); 32 } 33 34 public int UpdatePerson(int? addrid = null) 35 { 36 var exc = tinfo.GetExecutor(); 37 return exc.NonQueryUseModel(new { name = _name, birthday = DateTime.Now, addrid = addrid }, null); 38 } 39 40 public int DeletePerson() 41 { 42 return tinfo.GetExecutor().NonQueryUseModel(new 43 { 44 name = _name 45 }, null); 46 } 47 48 public int Count() 49 { 50 var exc = tinfo.GetExecutor(); 51 var rtn = exc.ScalarUseModel(new { name = _name }, null); 52 //MSSqlServer返回值会为int，而Sqlite会为long，转换就会出错，因此需要ChangeValueType 53 return (int)typeof(int).ChangeValueType(rtn); 54 } 55 56 public Person GetPerson() 57 { 58 return tinfo.GetExecutor().QueryUseModel<Person>(new 59 { 60 name = _name 61 }, null)?.FirstOrDefault(); 62 } 63 64 //执行存储过程 65 public IReadOnlyList<Person> ProcQuery() 66 { 67 ////Stored procedure sql： 68 //create proc TestQuery 69 //@name varchar(256) = null 70 //as 71 //begin 72 // select * from person where [name] = @name 73 //end 74 75 return tinfo.GetExecutor().QueryUseModel<Person>(new { name = "tom" }, null); 76 } 77 78 //执行存储过程 79 public int ProcUpdate() 80 { 81 ////Stored procedure sql： 82 //create proc TestUpdate 83 //@addrid int = 0, 84 //@name varchar(256) 85 //as 86 //begin 87 88 // update person set addrid = @addrid where[name] = @name 89 //end 90 91 return tinfo.GetExecutor().NonQueryUseModel(new { addrid = 3, name = "tom" }, null); 92 } 93 94 //事物 95 public void DoTran() 96 { 97 try 98 { 99 //开启事物 100 tinfo.DB.Database.BeginTransaction(); 101 bool bRtn = UpdatePerson() > 0; 102 bRtn &= AddPerson() > 0; 103 if (bRtn) 104 { 105 tinfo.DB.Database.CommitTransaction(); //提交 106 } 107 else 108 { 109 tinfo.DB.Database.RollbackTransaction(); //回滚 110 } 111 } 112 catch (Exception ex) 113 { 114 tinfo.DB.Database.RollbackTransaction(); //回滚 115 } 116 } 117 118 }
通过代码设置sql配置：
配置sql除了通过配置文件之外 还可以通过代码进行配置的：
1 public void AddSqls() 2 { 3 EFHelper.Services.SqlConfigMgr.Config.AddSqls<Person>(new Dictionary<string, IConfigSqlInfo> 4 { 5 { 6 "UpdatePerson", //SqlName 7 new ConfigSqlInfo 8 { 9 Sql = $"update {nameof(Person)} set name=@name where id=@id", 10 Type = ConfigSqlExecuteType.nonquery, 11 } 12 }, 13 { 14 "GetPersonList", //SqlName 15 new ConfigSqlInfo 16 { 17 Sql = $"select * from {nameof(Person)} id=@id", 18 Type = ConfigSqlExecuteType.query, 19 } 20 } 21 }); 22 } 23 24 public void AddTables() 25 { 26 EFHelper.Services.SqlConfigMgr.Config.AddOrCombine(new[] 27 { 28 new ConfigTableInfo 29 { 30 Name = nameof(Person), //表名 31 Sqls = new Dictionary<string, IConfigSqlInfo> 32 { 33 { 34 "UpdatePerson", 35 new ConfigSqlInfo 36 { 37 Sql = $"update {nameof(Person)} set name=@name where id=@id", 38 Type = ConfigSqlExecuteType.nonquery, 39 } 40 }, 41 { 42 "GetPersonList", 43 new ConfigSqlInfo 44 { 45 Sql = $"select * from {nameof(Person)} id=@id", 46 Type = ConfigSqlExecuteType.query, 47 } 48 } 49 } 50 }, 51 new ConfigTableInfo 52 { 53 Name = nameof(Address), //表名 54 Sqls = new Dictionary<string, IConfigSqlInfo> 55 { 56 { 57 "UpdateAddress", 58 new ConfigSqlInfo 59 { 60 Sql = $"update {nameof(Address)} set fullAddress=@fullAddress where id=@id", 61 Type = ConfigSqlExecuteType.nonquery, 62 } 63 }, 64 { 65 "GetAddressList", 66 new ConfigSqlInfo 67 { 68 Sql = $"select * from {nameof(Address)} id=@id", 69 Type = ConfigSqlExecuteType.query, 70 } 71 } 72 } 73 }, 74 }); 75 }
直接执行sql语句的方法：
如果不想通过配置文件配置sql，而是直接执行sql语句，那么：
1 DbContext db = new MSSqlDBContext(); 2 var nRtn = db 3 .NonQueryUseModel($"insert into {nameof(Person)}(name, birthday, addrid) values(@name, @birthday, @addrid)", 4 //可以使用SqlParameter / Dictionary作为sql的参数(使用Model对象时通过反射转换成SqlParameter的，因此性能会慢些) 5 new Person 6 { 7 name = "tom1", 8 birthday = DateTime.Now, 9 addrid = 123, 10 }, 11 //参数Model需要忽略的属性 12 new[] { "id" }); 13 Assert.True(nRtn > 0); 14 15 var qRtn = db 16 .QueryUseModel<Person>($"select name, birthday, addrid from {nameof(Person)} where name=@name", 17 new 18 { 19 name = "tom1" 20 }, 21 //参数Model需要忽略的属性(这里设置为null) 22 null, 23 //返回值类型中需要忽略的属性 24 new[] { "id" }); 25 Assert.True(qRtn?.Count > 0); 26 27 var sRtn = db.ScalarUseModel($"select count(id) from {nameof(Person)} where name=@name", new 28 { 29 name = "tom1" 30 }, null); 31 Assert.True((int)typeof(int).ChangeValueType(sRtn) > 0); 32 33 var nRtn1 = db.NonQueryUseDict($"delete from {nameof(Person)} where name=@name", 34 new Dictionary<string, object> 35 { 36 {"name", "tom1"} 37 }); 38 Assert.True(nRtn1 > 0);
执行sql语句的源码:
类库中是如何通过DbContext执行sql语句的，部分源码如下（更详细的可参考github中的源码）：
1 public IReadOnlyList<T> Query<T>(DbContext db, string sql, IDataParameter[] parameters = null, 2 IReadOnlyCollection<string> ignoreProptNames = null) 3 where T : new() 4 { 5 var concurrencyDetector = db.GetService<IConcurrencyDetector>(); 6 using (concurrencyDetector.EnterCriticalSection()) 7 { 8 var reader = GetReader(db, sql, parameters); 9 var rtnList = new List<T>(); 10 T model; 11 object val; 12 using (reader.DbDataReader) 13 { 14 var propts = _objReflec.GetPublicInstancePropts(typeof(T), ignoreProptNames); 15 while (reader.DbDataReader.Read()) 16 { 17 model = new T(); 18 foreach (var l in propts) 19 { 20 val = reader.DbDataReader[l.Name]; 21 val = ChangeType(l.PropertyType, val); 22 l.SetValue(model, val); 23 } 24 rtnList.Add(model); 25 } 26 } 27 return rtnList; 28 } 29 } 30 31 /// <summary> 32 /// 值的类型转换 33 /// </summary> 34 protected abstract object ChangeType(Type proptType, object val); 35 36 protected RelationalDataReader GetReader(DbContext db, string sql, IDataParameter[] parameters) 37 { 38 if (parameters?.Length > 0) 39 { 40 //带参数的 41 var cmd = db.GetService<IRawSqlCommandBuilder>() 42 .Build(sql, parameters); 43 return cmd 44 .RelationalCommand 45 .ExecuteReader( 46 db.GetService<IRelationalConnection>(), 47 parameterValues: cmd.ParameterValues); 48 } 49 else 50 { 51 //不带参数的 52 var cmd = db.GetService<IRawSqlCommandBuilder>() 53 .Build(sql); 54 return cmd 55 .ExecuteReader(db.GetService<IRelationalConnection>()); 56 } 57 }
未完待续...
让EFCore更疯狂些的扩展类库(一)：通过json文件配置sql语句
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。