SqlSugar ORM

Support .NET

Support database

Description

Documentation

Feature characteristic

Feature1 : Join query

varquery=db.Queryable<Order>().LeftJoin<Custom>((o,cus)=>o.CustomId==cus.Id).LeftJoin<OrderItem>((o,cus,oritem)=>o.Id==oritem.OrderId).LeftJoin<OrderItem>((o,cus,oritem,oritem2)=>o.Id==oritem2.OrderId).Where(o=>o.Id==1).Select((o,cus)=>newViewOrder{Id=o.Id,CustomName=cus.Name}).ToList();

SELECT  [o].[Id]AS [Id],  [cus].[Name]AS [CustomName]FROM  [Order] oLeft JOIN [Custom] cusON ([o].[CustomId]= [cus].[Id])Left JOIN [OrderDetail] oritemON ([o].[Id]= [oritem].[OrderId])Left JOIN [OrderDetail] oritem2ON ([o].[Id]= [oritem2].[OrderId])WHERE  ([o].[Id]= @Id0)

Feature2 :Include Query、Insert、Delete and Update

//Includesvarlist=db.Queryable<Test>().Includes(x=>x.Provinces,x=>x.Citys,x=>x.Street)//multi-level.Includes(x=>x.ClassInfo).ToList();//Includes+left joinvarlist5=db.Queryable<Student_004>().Includes(x=>x.school_001, x=>x.rooms).Includes(x=>x.books).LeftJoin<Order>((x,y)=>x.Id==y.sid).Select((x,y)=>newStudent_004DTO{SchoolId=x.SchoolId,books=x.books,school_001=x.school_001,Name=y.Name}).ToList();

Feature3 : Page query

intpageIndex=1;intpageSize=20;inttotalCount=0;varpage=db.Queryable<Student>().ToPageList(pageIndex,pageSize,reftotalCount);

Feature4 : Dynamic expression

varnames=newstring[]{"a","b"};Expressionable<Order>exp=newExpressionable<Order>();foreach(variteminnames){exp.Or(it=>it.Name.Contains(item.ToString()));}varlist=db.Queryable<Order>().Where(exp.ToExpression()).ToList();

SELECT [Id],[Name],[Price],[CreateTime],[CustomId]FROM [Order]WHERE (                     ([Name]like'%'+ CAST(@MethodConst0AS NVARCHAR(MAX))+'%')OR                      ([Name]like'%'+ CAST(@MethodConst1AS NVARCHAR(MAX))+'%')                    )

Feature5 : Multi-tenant transaction

//Creaate  database objectSqlSugarClientdb=newSqlSugarClient(newList<ConnectionConfig>(){newConnectionConfig(){ConfigId="0",DbType=DbType.SqlServer,ConnectionString=Config.ConnectionString,IsAutoCloseConnection=true},newConnectionConfig(){ConfigId="1",DbType=DbType.MySql,ConnectionString=Config.ConnectionString4,IsAutoCloseConnection=true}});varmysqldb=db.GetConnection("1");//mysql dbvarsqlServerdb=db.GetConnection("0");// sqlserver dbdb.BeginTran();mysqldb.Insertable(newOrder(){CreateTime=DateTime.Now,CustomId=1,Name="a",Price=1}).ExecuteCommand();mysqldb.Queryable<Order>().ToList();sqlServerdb.Queryable<Order>().ToList();db.CommitTran();

Feature6 : Singleton Pattern

publicstaticSqlSugarScopeDb=newSqlSugarScope(newConnectionConfig(){DbType=SqlSugar.DbType.SqlServer,ConnectionString=Config.ConnectionString,IsAutoCloseConnection=true},  db=>{db.Aop.OnLogExecuting=(s,p)=>{Console.WriteLine(s);};});using(vartran=Db.UseTran()){newTest2().Insert(XX);newTest1().Insert(XX);               .....                 ....tran.CommitTran();}

Feature7 : Query filter

//set filterdb.QueryFilter.Add(newTableFilterItem<Order>(it=>it.Name.Contains("a")));db.Queryable<Order>().ToList();//SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order]  WHERE  ([Name] like '%'+@MethodConst0+'%')db.Queryable<OrderItem,Order>((i,o)=>i.OrderId==o.Id).Where(i=>i.OrderId!=0).Select("i.*").ToList();//SELECT i.* FROM [OrderDetail] i  ,[Order]  o  WHERE ( [i].[OrderId] = [o].[Id] )  AND//( [i].[OrderId] <> @OrderId0 )  AND  ([o].[Name] like '%'+@MethodConst1+'%')

Feature8 : Insert or update

Db.Storageable(list2).ExecuteCommand();Db.Storageable(list2).PageSize(1000).ExecuteCommand();Db.Storageable(list2).PageSize(1000,exrows=>{}).ExecuteCommand();

Feature9 : Auto split table

[SplitTable(SplitType.Year)]//Table by year (the table supports year, quarter, month, week and day)[SugarTable("SplitTestTable_{year}{month}{day}")]publicclassSplitTestTable{[SugarColumn(IsPrimaryKey=true)]publiclongId{get;set;}publicstringName{get;set;}//When the sub-table field is inserted, which table will be inserted according to this field.//When it is updated and deleted, it can also be convenient to use this field to//find out the related table[SplitField]publicDateTimeCreateTime{get;set;}}

varlis2t=db.Queryable<OrderSpliteTest>().SplitTable(DateTime.Now.Date.AddYears(-1),DateTime.Now).ToPageList(1,2);　

Feature10 : Big data insert or update

10.1 BulkCopydb.Fastest<Order>().BulkCopy(lstData);//insertdb.Fastest<Order>().PageSize(100000).BulkCopy(insertObjs);db.Fastest<System.Data.DataTable>().AS("order").BulkCopy(dataTable);10.2 BulkUpdatedb.Fastest<Order>().BulkUpdate(GetList())//updatedb.Fastest<Order>().PageSize(100000).BulkUpdate(GetList()) db.Fastest<Order>().BulkUpdate(GetList(),newstring[]{"Id"});//no primary keydb.Fastest<Order>().BulkUpdate(GetList(),newstring[]{"id"},newstring[]{"name","time"})//Set the updated column//DataTabledb.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id");//Id is primary keydb.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id",Setthe updated column);10.3 BulkMerge （5.1.4.109）db.Fastest<Order>().BulkMerge(List);db.Fastest<Order>().PageSize(100000).BulkMerge(List);10.4 BulkQuerydb.Queryable<Order>().ToList();//Slightly faster than Dapper//Suitable for big data exportList<Order>order=newList<Order>();db.Queryable<Order>().ForEach(it=>{order.Add(it);},2000);10.5 BulkDeletedb.Deleteable<Order>(list).PageSize(1000).ExecuteCommand();