Net / NETCORE /. Net5 ORM six query systems – sqlsugar advanced


Framework introduction

Sqlsugar ORM is an old domestic ORM framework with strong vitality. From the early immature stage of ORM, it has survived until now. Why should I insist on it all the time? That’s because there are still many users using it. I could have launched the new open source framework earlier However, users are constantly raising new requirements and high requirements, so I try my best to improve sqlsugar

1. Some people say they don’t support domestic databases, but I do

2. Some people say that PgSQL MySQL Oracle compatibility is not good, after efforts, I also let him mature

3. Some people say that sqlsugar is asynchronous Task.RUN I also upgraded sqlsugar to net 4.5, which supports native asynchrony

4, some people say that navigation support is awesome, and I let him suck up.

5. Some people say that multi-level insertion is not supported, so do I

I can’t guarantee that every user’s needs can be supported in the past six years, but I can guarantee that multiple users have the same needs, so I need to design well and meet them


Key points of this paper

Sqlsugar query is very rich and colorful, and these functions also have very high practicability. I have sorted out and classified the query of sqlsugar

1. Basic query

2. Joint table query

3. Subquery

4. Navigation query

5. Query results

6. Advanced query


1、 Basic query

Basic query literally means the most common and simplest query

var getAll =  db.Queryable (). Tolist(); // query all
var top10=  db.Queryable (). Take (10). Tolist(); // query the top 10
var getFirst =  db.Queryable ().First(it=> it.Id==1 ); // query single
var getAllNoLock =  db.Queryable ().With( SqlWith.NoLock ). tolist(); // witness in sqlserver
var getByPrimaryKey =  db.Queryable (). Insingle (2); // query by primary key
var sum =  db.Queryable ().Sum(it=> it.Id ); // total query
var isAny =  db.Queryable ().Where(it=> it.Id== -1) . any(); // does it exist
var isAny2 = db.Queryable().Any(it => it.Id == -1);
var getListByRename = db.Queryable().AS("Student").ToList();
var getByWhere = db.Queryable().Where(it => it.Id == 1 || it.Name == "a").ToList();
var list= db.Queryable().AS("student2019").ToList();//select * from student2019
var list2 =  db.Queryable ().Where(it => it.Name.Contains ("Jack"). Tolist(); // fuzzy query name like '%' [email protected]+ '%'

Group query

var list = db.Queryable()
             .GroupBy(it => new { it.Id, it.Name })
             .Having(it =>  SqlFunc.AggregateAvg ( it.Id )> 0) // not an aggregate function, just use where
             .Select(it => new { idAvg = SqlFunc.AggregateAvg(it.Id), name = it.Name })
//SELECT AVG([Id]) AS[idAvg], [Name] AS[name]  FROM[Student] GROUP BY[Name],[Id] HAVING(AVG([Id]) > 0 )

Paging query

//Synchronous paging 
 int pageIndex = 1; 
 int pageSize = 20;
 int totalCount=0;
 var page = db.Queryable().ToPageList(pageIndex, pageSize, ref totalCount);

And collect queries

var q1 = db.Queryable().Select(it=>new Model{ name=it.Name });
 var q2 = db.Queryable().Select(it => new Model { name = it.Name });
 var list = db.UnionAll(q1, q2).ToList();
SELECT * FROM  (SELECT  [Name] AS [name]  FROM [STudent]  UNION ALL
SELECT  [Name] AS [name]  FROM [School] ) unionTable


2、 Joint table query

1. Two table query returns results to anonymous objects

var list = db.Queryable((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
      .Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name})      .ToList();
The generated SQL is as follows:
SELECT  [st].[ID] AS [id] , 
          [st].[Name] AS [name] , 
          [sc].[Name] AS [schoolName]  FROM [STudent] st 
          Left JOIN School sc ON ( [st].[SchoolId] =[sc].[Id])


2. Join table query returns the result to the new class, and implements “select a. *, as bName”

var oneClass = db.Queryable((o, i, c) => new JoinQueryInfos(
    JoinType.Left, o.Id == i.OrderId,
    JoinType.Left, o.CustomId == c.Id
. select ((O, I, c) = > New vieworder // is a new class
   Id= SqlFunc.GetSelfAndAutoFill (, // equals o*
   Customname = // equals [C]. [name] as [customname]


3. Fill in automatically according to rules

It should be noted that select uses automatic filling, which is convenient to use, and the high concurrency is written in the way above

public class ViewModelStudent : Student
        Public string schoolname {get; set;} // class name + property name
 var list = db.Queryable((st, sc, di) => new JoinQueryInfos(
              JoinType.Left , st.SchoolId==sc . ID, // you can use & & to implement the on condition and
           //.Where((st,sc)=> >0) multi table conditional usage
    //Sc. [name] as [schoolname], -- automatically generate schoolname
      //st.[ID] AS [Id],st.[SchoolId] AS [SchoolId],
      //st.[Name] AS [Name],st.[CreateTime] AS [CreateTime]
      //FROM [STudent] st
      //Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] )  
      //Left JOIN [DataTestInfo] di ON ( [st].[Name] = [di].[String] )


4. Simple linked table

var list = db.Queryable((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)

Generated SQL:

SELECT  c.[Name] AS [CustomName],
        o.[Id] AS [Id],o.[Name] AS [Name],
        o.[Price] AS [Price],
        o.[CreateTime] AS [CreateTime],
        o.[CustomId] AS [CustomId]         FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  
         WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))


3、 Subquery

Sqlsugar sub query is also very powerful, many people ask

1. Look up a column by subquery

var getAll = db.Queryable((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id))
.Where(st => st.Id == SqlFunc.Subqueryable().Where(s => s.Id == st.Id).Select(s => s.Id))

The generated SQL is as follows

SELECT `st`.`ID`,`st`.`SchoolId`,`st`.`Name`,`st`.`CreateTime` 
FROM `STudent` st Left JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) 
WHERE ( `st`.`ID` =(SELECT `Id` FROM `School` WHERE ( `Id` = `st`.`ID` ) limit 0,1))

It can also be used in select

var getAll = db.Queryable((st, sc) => new JoinQueryInfos(JoinType.Left,st.Id==sc.Id))
.Select(st => new{
name = st.Name,
id = SqlFunc.Subqueryable().Where(s => s.Id == st.Id).Select(s => s.Id)

Subquery also supports join

.LeftJoin((cus,item)=> cus.Id==item . customid / * with & & append condition * /)


2. Operation of in and not in

var getAll7 = db.Queryable().Where(it => 
SqlFunc.Subqueryable().Where(s => s.Id == it.Id).Any()).ToList();

/*Generated SQL (equal to  In (select id from school) is written differently
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) )) 

var getAll8 = db.Queryable().Where(it => 
SqlFunc.Subqueryable().Where(s => s.Id == it.Id).NotAny()).ToList();

/*Generated SQL
SELECT `ID`,`SchoolId`,`Name`,`CreateTime` FROM `STudent` it 
WHERE (NOT EXISTS ( SELECT * FROM `School` WHERE ( `Id` = `it`.`ID` ) ))


3. Join table subquery

When you want to use a table and a join table for join query, or two join tables for join query can be implemented in this way

var query1 = db.Queryable((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
.Where(st => st.Name == "jack");

var query2 = db.Queryable();

db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select().ToList();

// (SELECT [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] 
// FROM [STudent] st 
// Left JOIN [School] sc ON ([st].[SchoolId]=[sc].[Id] ) 
// WHERE ( [st].[Name] = @Name0Join0 )) p1 
// Inner JOIN

// (SELECT [Int1],[Int2],[String],[Decimal1],[Decimal2],
// [Datetime1],[Datetime2],[Image1],[Image2], 
// [Guid1],[Guid2],[Money1],[Money2],[Varbinary1],
// [Varbinary2],[Float1],[Float2] FROM [DataTestInfo] )p2 

// ON ( [p1].[ID] = [p2].[Int1] )


4. Multiple in one subquery

In general, the dynamic use of search criteria and sorting after multi table query requires the use of St SC and other prefixes. After multi table synthesis, it becomes a single table query, so there is no need to add an alias

var pageJoin = db.Queryable((st, sc) => new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))
.Select((st,sc) => new{ 
                        id = st.Id,
                        name = sc.Name})
.Where(it=>"name asc").ToList();

The generated SQL is as follows:


[st].[Id] AS [id] , [sc].[Name] AS [name] 
FROM [Student] st Left 
Join [school] SC on ([st]. [customid] = [SC]. [ID]) mergetable -- turns the result into a table 

WHERE ( [id] = @id0 )ORDER BY name asc


5. One in one subquery

I just want to implement it on the package layer outside a single table. The difference between mergetable and singletable is that the former needs to add select, while the latter does not

var listx=db.Queryable(db.Queryable()).ToList();

The SQL code is as follows:

SELECT t.* FROM (SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] ) t


4、 Navigation query

1. Out of the box without configuration

For other ORM navigation queries, entities are generally required to configure the association relationship between entities, while sqlsugar is used out of the box. There is no configuration except adding ignore to non database fields

2. High performance

The automatic mapping mode will not generate circular SQL reading. For example, if one record is queried for one to many queries, two SQL will be generated. If 200 records are queried, two SQL will be generated,

Some people say why there are two rather than one. A complex SQL may not perform better than two simple SQL without aggregation grouping. You can throw three SQL in the SQL server interface and then use it

Look at the cost ratio of the execution plan. If one SQL is greater than or equal to 50%, its performance is lower than that of two SQL


1. One to one query

The first mock exam mode of Mapper is 2-3 parameters, Mapper (1) child object, 2 main table associated field, and 3 associated with table (default from table primary key).

First parameter: it.Order Is the child of the fill

The second parameter it.OrderId Is the associated field in the main table

The third parameter is not equal to the primary key of the secondary table. You can also specify a field of the secondary table

It’s in the main table it.OrderId Associated with the primary key from the table, it can be automatically filled into the OrderItem.Order inside


var list= db.Queryable().Mapper(it => it.Order, it => it.OrderId).ToList(); 
foreach(var item in list)
   Console.WriteLine ( item.Order.Name ); // output the value in the sub object
//New function: conditional filtering is supported in 1-to-1 mode
var list= db.Queryable()
.Mapper(it => it.Order, it => it.OrderId)
.Where(it=> it.Order.Name== "Ha ha"). Tolist(); // we can use level 2 object attributes to filter
public class OrderItem {
 [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
 public int ItemId { get; set; }
 public string ItemCode { get; set; }
 public decimal Pirce { get; set; }
 public int OrderId { get; set; }
 [SugarColumn(IsIgnore =true)]
 public Order Order{ get; set; }
public class Order {
 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
 public int Id { get; set; }
 public string Name { get; set; }


2. One to many query

var list= db.Queryable().Mapper(it => it.Items, it => it.Items.First().OrderId).ToList();
public class Order
 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
 public int Id { get; set; }
 public string Name { get; set; }
 [SugarColumn(IsIgnore = true)]
 public List Items { get; set; }


3. Many to many query

public class ABMapping
  [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
  public int AId { get; set; }
  public int BId { get; set; }
  [SugarColumn(IsIgnore = true)]
  public A A { get; set; }
  [SugarColumn(IsIgnore = true)]
  public B B { get; set; }
public class A {
 [SugarColumn(IsPrimaryKey =true ,IsIdentity =true)]
   public int Id { get; set; }
  public string Name { get; set; }
public class B
  [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
  public int Id { get; set; }
  public string Name { get; set; }
var list= db.Queryable()
.Mapper(it => it.A,it=>it.AId)
.Mapper(it => it.B, it => it.BId).ToList();

4. Same field name mapping

Sub table object = sub table object. Where (it = > 0 it.OrgId== . orgid in main table object)

var list= db.Queryable().Mapper(it => it.B,it=>it.OrgId).ToList();


5、 Query results

Support common classes, anonymous classes, arrays, dictionaries, dictionary sets, JSON, datatable, tree

//Return to list
List list = db.Queryable().ToList();
//Return datatable
DataTable dataTable = db.Queryable().Select(it => it.Id).ToDataTable();
//Back to Jason
var json = db.Queryable().ToJson();
//Return array
List listInt=db.Queryable().Select(it => it.Id).ToList();  
//Return anonymous object
var dynamic = db.Queryable().Select().ToList();  
//Expression return dictionary
Dictionary dc = db.Queryable().ToDictionary(it=>it.Id,it=>it.Name);
//Return to dictionary collection
List> dcList = db.Queryable().ToDictionaryList();;

Tree results

var tree =  db.Queryable ().ToTree(it=> it.Child ,it=> it.ParentId , 0); // if the third parameter is 0, the parent is 0, which is the first level
public class Tree
 [SqlSugar.SugarColumn(IsPrimaryKey =true)]
 public int Id { get; set; }
 public string Name { get; set; }
 public int ParentId { get; set; }
 [SqlSugar.SugarColumn(IsIgnore = true)]
 public List Child { get; set; }


6、 Advanced query

The second cache is to cache the result set and read the data from the cache when the SQL and parameters have not changed, so as to reduce the reading operation of the database

ICacheService myCache = new HttpRuntimeCache();
SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
  ConnectionString = Config.ConnectionString,
  DbType = DbType.SqlServer,
  IsAutoCloseConnection = true,
  ConfigureExternalServices = new ConfigureExternalServices()
    Datainfocacheservice = mycache // configure the cache class we created
db.Queryable ().Where(it =>  it.Id  >0). Withcache(). Tolist(); // set the cache to one day by default
db.Queryable (). Withcache (1000). Tolist(); // set specific expiration time

Asynchronous query

var task1=db.Queryable().FirstAsync();
var task2 = db.Queryable().Where(it=>it.Id==1).ToListAsync();
//Pagination requires special attention to usage
RefAsync total = 0;
Db.Queryable().ToPageListAsync(1, 2, total);

JSON type query

public class UnitJsonTest
   [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   public int Id { get; set; }
    [SqlSugar.SugarColumn(ColumnDataType ="varchar(2000)", IsJson = true)]
   public Order Order { get; set; }
   public string Name{get;set;}
Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand();
var list = Db.Queryable().ToList();

Dynamic Expression Query

var exp= Expressionable.Create()
                .Or(it =>it.Name.Contains("jack")).ToExpression();
var list=db.Queryable().Where(exp).ToList();

Dynamic condition query

var conModels = new List();
conModels.Add(new ConditionalModel{ FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1"}); 
var student = db.Queryable().Where(conModels).ToList();


Sqlsugar source code download:

I can’t guarantee that sqlsugar is the best now, that is, it will be better and better in the future. Thank you

A new open source framework, webfirst, is also under development. It is expected to be released before the Spring Festival and will support web and exe