前言 EF通過linq和各種擴展方法，再加上實體模型，編寫資料庫的訪問代碼確實是優美、舒服，但是生成的sql不盡如意、性能低下，尤其是複雜些的邏輯關係，最終大家還是會回歸自然，選擇能夠友好執行sql語句的ORM，認認真真的編寫sql；問題是：EF是否也能夠很友好的執行sql語句？EF提供直接執行sq ...
前言
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 }View Code
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 }View Code
載入配置文件（在程式初始化的時候調用）：
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" } } }View Code
調用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 }View Code
通過代碼設置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"
