让EFCore更疯狂些的扩展类库(一)：通过json文件配置sql语句

谁践踏了优雅 2022-07-11 11:23 554阅读 0赞

# **前言** #

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

**表的实体模型：**

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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****)：**

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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语句****配置内容：** ###

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

{
      //"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配置的代码（包括事物处理）：**

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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除了通过配置文件之外 还可以通过代码进行配置的：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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语句，那么：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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中的源码）：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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         }

未完待续...

[ContractedBlock.gif]: http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20220711/dca0bd2a1b184a00abec8e6008832b60.png