Usage scenario

1. Lambda to sql has always been one of the most difficult functions in orm. If there is a ready-made parsing library, the difficulty of writing an ORM by yourself will be greatly reduced

2. Lambda is used as a KEY for caching operations, especially in the warehousing mode. Expression parsing is needed to get expressions for general query caching

3. Learning enthusiasts, if you can have a deeper understanding of how Lambda is interpreted into Sql, your understanding of Lambda will go to a higher level, not just in use

advantage

In addition to its own ORM function, SqlSugar is also a powerful lambda parser, and is a unique lambda to Sql class library in the market

SqlSugar is not only small, but also has very good performance. It is not inferior to Dapper or even better. As for performance, it is very unreliable to look at other people's evaluation. You can only know it after you use it and test it yourself

1. High performance} expression parsing speed is much faster than EF

2. The class library supports all kinds of parsing functions at the product level

3. As long as you reference a DLL of SqlSugar, you can use it to parse various databases, support Sql functions, and 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 resolution object
 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 perfectly convert the expression into Sql statement

Tutorial case

1. How to create an ExpressionContext parsing object

We can use sqlsugar Dbtype distinguishes the database and instantiates it

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. Analysis of Where condition

It is basically the same as writing EF, and the commonly used ones will be supported

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 an example of Like

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 type parsing is one of the difficulties in ORM parsing, and there are many 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 ) )

Support for diversified bool interpretation

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. For example, we want 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 resolution, query single field resolution and 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. Resolution of field names

For example, orderby (it = > it. Name) like this, we need to get the name

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

Statistics single 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 C# functions:

.ToString  .Contains  .Length

.ToLower  .ToUpper  .ToSubstring 

.Equals    .HasValue  .Replace

.EndsWith  .StartsWith  .Trim

.HasValue  .Value  .AddDay  .Date

And the commonly used convert Toint32 wait

SqlFunc. function

The SqlFunc function contains a large number of SQL functions

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

5. Other functions

5.1 supporting features, such as our table name and entity name are different, we can also implement them

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 sub query

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. Do it yourself

Through the above study, it should be quite easy for you to write the following syntax 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()

The above functions are always shared with Where parsing, column parsing and Select parsing

1. The following three parses use Where

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

2. The following one uses column parsing

o=>o.Id

3. The following one uses the analysis of Select

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

Source code download:

Your praise or your star is the driving force of open source developers. Open source is not easy to make money in harmony

https://github.com/sunkaixuan/SqlSugar     

Eggs:

In the next article, I will introduce how to use SqlSugar to develop code in WebFirst mode, leading the design and development concepts of CodeFirst and DbFirst, and also a summary of my more than ten years of development experience