強大的拉姆運算式轉Sql 類別庫 - SqlSugar 隱藏功能之Lambda-有解無憂

使用場景

1、Lambda to sql 一直是ORM中最難的功能之一，如果有現成的決議庫那么自已寫一個ORM難度將大大降低

2、通過Lambda作為KEY進行快取操作，特別是倉儲模式想要拿到運算式進行通用查詢快取則需要用到運算式決議

3、學習愛好者，如果能夠較深入的理Lambda如何去解成Sql，那么你對Lambda的理解將會更上一個層次，而不僅僅在使用上

優點

SqlSugar除了有自帶的ORM功能，還是一款強大的拉姆達決議器，并且是市場上獨有的拉姆達轉Sql類別庫

SqlSugar不僅小巧并且性能非常的好，毫不遜色于Dapper 甚至更加的優秀，至于性能別看其他人的測評非常不靠譜，自個用了才知道，自個測了才知道

1、高性能運算式決議速度遠超EF

2、功能非常完整，并且是產品級的決議類別庫，支持各種模式的決議

3、小巧你只要參考SqlSugar一個DLL就能使用決議各種資料庫、支持Sql函式、支持常用原生函式

安裝

.NET CORE / NET5 Nuget SqlSugarCore

.NET 4.5 Nuget SqlSugar

.NET 4.0 Nuget SqlSuagr 4.x

入門

我們先來看一下例子：

static void Main(string[] args)
{
 var age = 11;
 Expression<Func<Test, bool>> exp = a => a.id == age; //運算式
 SqlServerExpressionContext expContext = new SqlServerExpressionContext();//創建決議物件
 expContext.Resolve(exp, ResolveExpressType.WhereSingle);//開始決議
 var value = https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/expContext.Result.GetString();//( [id] = @id0 )
 var pars = expContext.Parameters;// @id:11
}

下面是除錯結果：

通過上面的例子就能夠完美的將運算式轉換成Sql陳述句

教程案例

1、如何創建 ExpressionContext 決議物件

我們可以通過SqlSugar.DbType進行區分資料庫并且進行實體化

ExpressionContext expContext=null;
switch (dbtype)
{
case DbType.MySql:
expContext = new MySqlExpressionContext();
break;
case DbType.SqlServer:
expContext = new SqlServerExpressionContext();
break;
case DbType.Sqlite:
expContext = new SqliteExpressionContext();
break;
case DbType.Oracle:
expContext = new OracleExpressionContext();
break;
case DbType.PostgreSQL:
expContext = new PostgreSQLExpressionContext();
break;
case DbType.Dm:
expContext = new DmExpressionContext();
break;
case DbType.Kdbndp:
expContext = new KdbndpExpressionContext();
break;
default:
throw new Exception("不支持");

2、Where條件的決議

基本和寫EF一樣常用的都會支持

Expression<Func<DataTestInfo2, bool>> exp = it => it.Bool2== b.Value;
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//( [Bool2] = @Bool20 )

我們在寫一個Like的例子

Expression<Func<Student, bool>> exp = it => it.Name.Contains(schoolData.Name);
ExpressionContext expContext = new ExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereMultiple);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//([it].[Name] like '%'+@MethodConst0+'%')

bool型別的決議是ORM決議的難點中的難點，情況多種多樣

Expression<Func<DataTestInfo2, bool>> exp = it => it.Bool1&&it.Bool1;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//( ( [Bool1]=1 ) AND ( [Bool1]=1 ) )

多樣化的bool解釋的支持

Expression<Func<Student, bool>> exp = it =>true&& it.Name != null;
ExpressionContext expContext = new ExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//(( 1 = 1 ) AND( [Name] IS NOT NULL ))

多表查詢的支持，在多表查詢中我們是需要加前綴的比如我們想要 it.id 而不是id

  Expression<Func<Student, bool>> exp = it => (it.Id > 1 && it.Name != name || it.Id == 1) || it.Name == WhereConst.name;
  ExpressionContext expContext = new ExpressionContext();
  expContext.Resolve(exp, ResolveExpressType.WhereMultiple);
  var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
  var pars = expContext.Parameters;
//(((( [it].[Id] > @Id0 ) AND ( [it].[Name] <> @Name1 )) OR ( [it].[Id] = @Id2 )) OR ( [it].[Name] = @Name3 ))

3、如何決議Select

支持物體類決議、查詢單個欄位決議、匿名物件決議

Expression<Func<DataTestInfo2, DataTestInfo2>> exp =it => new DataTestInfo2() {  Bool1=it.Bool1 , Bool2=it.Bool2 };
ExpressionContext expContext = new ExpressionContext();
expContext.IsSingle = false;
expContext.Resolve(exp, ResolveExpressType.SelectSingle);
var selectorValue =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//[Bool1] AS [Bool1] , [Bool2] AS [Bool2]

Expression<Func<Student, School, object>> exp = (it, school) => new { Name = "a", Id = it.Id / 2, SchoolId = school.Id };
ExpressionContext expContext = new ExpressionContext();
expContext.IsSingle = false;
expContext.Resolve(exp, ResolveExpressType.SelectMultiple);
var selectorValue =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
// @constant0 AS [Name] , ( [it].[Id] / @Id1 ) AS [Id] , [school].[Id] AS [SchoolId]

3、欄位名稱的決議

例如orderby(it=>it.Name) 像這種我們就需要拿到Name

Expression<Func<Student, object>> exp = it => it.Name;
ExpressionContext expContext = GetContext();
expContext.Resolve(exp, ResolveExpressType.FieldSingle);
var selectorValue =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
//Name

統計單個欄位

Expression<Func<Student, object>> exp = it =>SqlFunc.AggregateAvg(it.Id);
ExpressionContext expContext = GetContext();
expContext.Resolve(exp, ResolveExpressType.FieldMultiple);
var selectorValue =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
//AVG([it].[Id])

4、Sql函式的支持

Expression<Func<Student, bool>> exp = it => (it.Name.Contains("a")? 1:2)==1;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;
//(( CASE  WHEN  ([Name] like '%'+@MethodConst0+'%')  THEN @MethodConst1  ELSE @MethodConst2 END ) = @Const3 )

支持的C#函式：

.ToString .Contains .Length

.ToLower .ToUpper .ToSubstring

.Equals .HasValue .Replace

.EndsWith .StartsWith .Trim

.HasValue .Value .AddDay .Date

和常用的Convert.ToInt32等等

SqlFunc.函式

SqlFunc函式下面包含了大量SQL函式

 SqlFunc.IF(st.Id > 1)
     .Return(st.Id)
     .ElseIF(st.Id == 1)
     .Return(st.SchoolId).End(st.Id) //等于Case when

5、其它功能

5.1 支持特性比如我表名和物體名不一樣我們也可以實作

SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.MappingTables.Add(typeof(Student).Name,"OA_STUDENT")

列名也非常容易

SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.MappingColumns.Add("Id","ID",typeof(Student).Name)

5.2 支持子查詢

Expression<Func<Test, bool>> exp = a => SqlFunc.Subqueryable<Sutdent>().Where(it => it.testId == a.id).Select(it => it.id） == 1);
var expContext = new SqlServerExpressionContext();
 expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value =https://www.cnblogs.com/sunkaixuan/archive/2020/12/06/ expContext.Result.GetString();
var pars = expContext.Parameters;

6、自已動手創建ORM

通上面的學習你們想寫出下面的語法應該相當容易了

 var oneClass = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&& o.CustomId == c.Id))
.Where(o=>o.id==1) 
.Where((o,i)=>i.xx==1) 
.OrderBy(o=>o.Id)
.Select((o,i,c)=> new ViewOrder
 {
          Id=SqlFunc.GetSelfAndAutoFill(o.Id),// o.*
          CustomName=c.Name   //[c].[Name] AS [CustomName]
}).ToList()

實作上面的功能總共用到Where的決議、列的決議和Select的決議

1、下面3個用到Where的決議

(o, i, c) => o.Id == i.OrderId&& o.CustomId == c.Id)
 o=>o.id==1
(o,i)=>i.xx==1

2、下面1個用到列的決議

o=>o.Id

3、下面1個用到Select的決議

(o,i,c)=> new ViewOrder
 {
          Id=SqlFunc.GetSelfAndAutoFill(o.Id),// o.*
          CustomName=c.Name   //[c].[Name] AS [CustomName]
}

原始碼下載：

你們的贊或者你們的 star 就是開源者的動力，開源不容易和氣生財

https://github.com/sunkaixuan/SqlSugar

彩蛋：

下一篇文章我會介紹如何使用 SqlSugar 實作WebFirst模式開發代碼，領先CodeFirst和DbFirst的設計和開發理念，也我十多年開發經驗的匯總

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/230864.html

標籤：.NET技术

上一篇：C#9.0新特性詳解系列之四：頂級程式陳述句（Top-Level Programs)

下一篇：C#（五）基礎篇—字串