The first release of mini ORM DapperLambda, ormdapperlambda
Introduction: I encountered many ORM operations, But I encountered some unsatisfactory problems when I was using them. From the very beginning, I started to package my own, and then I used EF later, as well as FluentData used by Dapper and DapperExtensions, let's talk about my own user experience. In comparison, Dapper should be the most lightweight and has the best performance, but it is relatively simple. The latest version of EF has not been used, so I am not very familiar with it. EF has the most powerful functions, but it is slow to start loading and complicated functions, subsequent optimization troubles. FluentData is very useful, and the syntax is the easiest to understand, but Lambda is still not supported. Based on the above, I started to develop an ORM by myself. The performance is comparable with that of Dapper. The syntax is simple and easy to understand. Therefore, FluentData is used for reference in many places, however, the internal logic is completely different.
In general, DapperLambda is the secondary package of Dapper and DapperExtensions. The syntax is similar to FluentData, and Lambda is added.Principle: encapsulate and enrich these functions not to avoid writing SQL, but to achieve simple SQL, and do not want to re-write in development...
Next I will introduce the application in the development process.
1. Download the project and reference DapperLambda. dll. Execute Install-Package DapperLambda in the vs command window.
Or search for DapperLambda in reference> Manage NuGet packages.
Ii. dll references to our data service layer.
1) Create and initialize a DbContext.
It is the context of our database operations, and all related data operations call the methods below it;Currently, only unit tests are conducted for MSSQL and will be improved in other databases in the future. Therefore, do not use MSSQL with caution.
1 public static DbContext GetContext()2 {3 string connectionStr = "server=(local);User ID=sa;Password=password01!;Database=LocalDB;Persist Security Info=True;Pooling=true;Max Pool Size=700";4 return new DbContext().ConnectionString(connectionStr, DatabaseType.MSSQLServer);5 }
2) Next we will officially start to introduce the syntax of DapperLambda. Because some of the syntaxes are consistent, we will not discuss it in the first article. We will discuss it in detail in the future.
1 [TestMethod]2 public void TestOrderByMethod()3 {4 using (var context = DBHelper.GetContext())5 {6 var ls = context.Select<Application>().Where(p => p.CategoryID == 1).OrderBy(p => p.ApplicationID).QueryMany();7 Assert.IsTrue(ls.Count() > 0);8 }9 }
Directly, most directly
Of course, this condition and sorting can be combined multiple times as follows:
1 [TestMethod]2 public void TestConditionMethod()3 {4 using (var context = DBHelper.GetContext())5 {6 var ls = context.Select<Application>().Where(p => p.ApplicationID == 1000 || p.CategoryID == 1).And(c => c.Owner == "CNLIFAN").OrderBy(p => p.ApplicationID).QueryMany();7 Assert.IsTrue(ls.Count() > 0);8 }9 }
2. Lambda specifies the condition and dynamically specifies the updated field to prevent additional fields from being updated.
1 public void UpdateMethod()2 {3 using (var context = DBHelper.GetContext())4 {5 var item = context.Update<MobileForTest>().Set(new { MobileHolder = "TEST-10", MobilePhone = "18923456789" }).Where(p => p.ID == 1).Execute();6 Assert.IsTrue(item > 0);7 }8 }
3. nested query statements are combined into a large SQL statement for execution.
1 public void Delete3Method()2 {3 using (var context = DBHelper.GetContext())4 {5 var item = context.Delete<MobileForTest>().WhereIsIn(p => p.ID, context.Select<Application>(p => p.ApplicationID == 10001).Select(p => p.CategoryID)).Execute();6 Assert.IsTrue(item > 0);7 }8 }
1 [TestMethod]2 public void SQLMethod13()3 {4 using (var context = DBHelper.GetContext())5 {6 var item = context.Select<MobileForTest>().WhereNotIn(p => p.ID, context.Select<Application>(p => p.ApplicationID == 10001).Select(p => p.CategoryID)).QuerySingle();7 Assert.IsTrue(item.ID == 1);8 }9 }
1 [TestMethod]2 public void SQLMethod14()3 {4 using (var context = DBHelper.GetContext())5 {6 var curID = context.Select<MobileForTest>(p => p.ID == 1).Select(item => new { ID = item.ID, Mobile = item.MobilePhone }).QueryDynamicSingle();7 var pkID = curID;8 }9 }
Flexibly specify the desired field to be returned
4. convenient use of things
1 [TestMethod] 2 public void UseTransactionMethod() 3 { 4 var pkid = 1; 5 var model = new MobileForTest { MobileHolder = "TEST-linfengFang", MobilePhone = "18911112222", Status = 0 }; 6 using (var context = DBHelper.GetContext().UseTransaction(true)) 7 { 8 context.Insert<MobileForTest>(model).Execute(); 9 var item = context.Sql("UPDATE MobileForTest SET MobileHolder = 'SQLMethod-linfeng' WHERE ID=@ID", new { ID = pkid }).Execute();10 context.Commit();11 }12 }
5. More paging functions used in projects
1 [TestMethod] 2 public void SQLPage() 3 { 4 var record = 0; 5 using (var context = DBHelper.GetContext()) 6 { 7 var pageIndex = 0; 8 var pageSize = 3; 9 context.Select<MobileForTest>(p => p.MobilePhone == "18911112222" && p.ID != 1).QueryPage(pageIndex, pageSize, out record);10 Assert.IsTrue(record > 0);11 }12 }13 [TestMethod]14 public void SQLPage2()15 {16 var record = 0;17 using (var context = DBHelper.GetContext())18 {19 var pageIndex = 0;20 var pageSize = 3;21 var ls = context.Sql("select * from MobileForTest").QueryPage<MobileForTest>(pageIndex, pageSize, out record);22 Assert.IsTrue(record > 0);23 }24 }
Compared with other ORM, most of the basic functions are similar to other syntaxes. Therefore, it is not mentioned at the beginning and is currently supported. You can leave a message if any problem occurs,
The code will be considered for open source after the code is stable and regular in the future.