当前位置:网站首页>Lambda: powerful ram expression to SQL class library - lambda of sqlsugar hiding function

Lambda: powerful ram expression to SQL class library - lambda of sqlsugar hiding function

2020-12-07 10:58:27 osc_ sixty-three million nine hundred and thirty-four thousand

Use scenarios

1、Lambda to sql Has always been a ORM One of the most difficult features in , If there is a ready-made parsing library, then write one by yourself ORM The difficulty will be greatly reduced

2、 adopt Lambda As KEY Cache operation , In particular, the warehouse mode wants to get expressions for general query caching Expression parsing is needed

3、 Learning enthusiasts , If we can have a deeper understanding of Lambda How to solve it into Sql, So you are right Lambda It's going to take that understanding to a higher level , Not just in use

 

advantage

SqlSugar Except that you have your own ORM function , It's also a powerful ramda parser   , And it's unique in the market   The ramda turn Sql Class library  

SqlSugar Not only compact but also very good performance , As good as Dapper Even better   , As for performance, don't look at other people's evaluation Very unreliable , I only know when I use it , Only after I tested myself did I know

1、 High performance   The speed of expression parsing is much faster than EF 

2、 The function is very complete , And it's a production level parsing class library , Supports parsing of various patterns

3、 Small, you just need to quote SqlSugar One DLL Can use Parsing various databases 、 Support Sql function 、 Support common native functions

 

install

.NET CORE / NET5   Nuget SqlSugarCore

.NET 4.5   Nuget SqlSugar 

.NET 4.0 Nuget SqlSuagr 4.x

 

introduction

Let's start with an example

static void Main(string[] args)
{
 var age = 11;
 Expression<Func<Test, bool>> exp = a => a.id == age; // expression 
 SqlServerExpressionContext expContext = new SqlServerExpressionContext();// Create parsing objects 
 expContext.Resolve(exp, ResolveExpressType.WhereSingle);// Start parsing 
 var value = expContext.Result.GetString();//( [id] = @id0 )
 var pars = expContext.Parameters;// @id:11
}  

  Here are the debugging results :

 

  Through the above example, we can transform the expression into Sql sentence

 

Tutorial cases

1、 How to create  ExpressionContext Parse object

  We can go through SqlSugar.DbType To differentiate databases And instantiate

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(" I won't support it ");

 

 2、Where Analysis of conditions

Basic and writing EF The same commonly used will support

Expression<Func<DataTestInfo2, bool>> exp = it => it.Bool2== b.Value;
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
//( [Bool2] = @Bool20 )

We're writing one Like Example

Expression<Func<Student, bool>> exp = it => it.Name.Contains(schoolData.Name);
ExpressionContext expContext = new ExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereMultiple);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
//([it].[Name] like '%'+@MethodConst0+'%') 

bool The resolution of type is ORM The difficulty in the analysis , There are many different situations

Expression<Func<DataTestInfo2, bool>> exp = it => it.Bool1&&it.Bool1;
SqlServerExpressionContext expContext = new SqlServerExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
//( ( [Bool1]=1 ) AND ( [Bool1]=1 ) )

Diversified bool The support of explanation

Expression<Func<Student, bool>> exp = it =>true&& it.Name != null;
ExpressionContext expContext = new ExpressionContext();
expContext.Resolve(exp, ResolveExpressType.WhereSingle);
var value = expContext.Result.GetString();
var pars = expContext.Parameters;
//(( 1 = 1 ) AND( [Name] IS NOT NULL ))

Multi table query support , In multi table query, we need to add prefix, such as We want to it.id instead of 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 = expContext.Result.GetString();
  var pars = expContext.Parameters;
//(((( [it].[Id] > @Id0 ) AND ( [it].[Name] <> @Name1 )) OR ( [it].[Id] = @Id2 )) OR ( [it].[Name] = @Name3 ))

 

3、 How to parse Select

Support entity class parsing 、 Query single field resolution 、 Anonymous object resolution

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 = 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 = expContext.Result.GetString();
// @constant0 AS [Name] , ( [it].[Id] / @Id1 ) AS [Id] , [school].[Id] AS [SchoolId] 

 

3、 Field name resolution

for example orderby(it=>it.Name) Like this, we need to get Name

Expression<Func<Student, object>> exp = it => it.Name;
ExpressionContext expContext = GetContext();
expContext.Resolve(exp, ResolveExpressType.FieldSingle);
var selectorValue = expContext.Result.GetString();
//Name

Statistics A field

Expression<Func<Student, object>> exp = it =>SqlFunc.AggregateAvg(it.Id);
ExpressionContext expContext = GetContext();
expContext.Resolve(exp, ResolveExpressType.FieldMultiple);
var selectorValue = expContext.Result.GetString();
//AVG([it].[Id])

 

4、Sql Function support  

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

Supported by C# function :

.ToString  .Contains  .Length

.ToLower  .ToUpper  .ToSubstring 

.Equals    .HasValue  .Replace

.EndsWith  .StartsWith  .Trim

.HasValue  .Value  .AddDay  .Date

And commonly used Convert.ToInt32 wait  

 

SqlFunc. function

SqlFunc Function contains a large number of SQL function   

SqlFunc.IF(st.Id > 1)
     .Return(st.Id)
     .ElseIF(st.Id == 1)
     .Return(st.SchoolId).End(st.Id) // be equal to Case when

 

5、 Other features

5.1 Support features such as my table name and entity name are different, we can also implement

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

Listing is also very easy

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

 

5.2 Support subquery

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 = expContext.Result.GetString();
var pars = expContext.Parameters;

 

6、 Create it yourself ORM

Through the above study, it should be quite easy for you to write the following grammar  

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()

Implement the above functions All in all Where Parsing 、 Column resolution and Select Parsing

1、 below 3 One uses Where Parsing

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

 2、 below 1 Use column parsing

o=>o.Id

 3、 below 1 One uses Select Parsing

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

 

  

Source download :

Your compliments or your star Namely The power of open source , Open source is not easy to make money with kindness  

https://gitee.com/sunkaixuan/SqlSugar

 

Colored eggs :

Next article I'll show you how to use SqlSugar Realization WebFirst Pattern development code , leading CodeFirst and DbFirst Design and development concept of , It is also a summary of my more than ten years of development experience

版权声明
本文为[osc_ sixty-three million nine hundred and thirty-four thousand ]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/12/20201207105541962y.html