GitHub - DotNetNext/SqlSugar: .Net aot ORM Fastest ORM DB2 Hana Simple Easy VB.NET Sqlite orm Oracle ORM Mysql Orm 虚谷数据库 postgresql ORm SqlServer oRm 达梦 ORM 人大金仓 ORM 神通ORM C# ORM , C# ORM .NET ORM NET5 ORM .NET6 ORM ClickHouse orm QuestDb ,TDengine ORM,OceanBase orm,GaussDB orm ,Tidb orm Object/Relational Mapping (original) (raw)

English | 中文

SqlSugar ORM

SqlSugar is .NET open source ORM framework, maintained and updated by Fructose Big Data Technology team, the most easy-to-use ORM out of the box

Advantages: [Low code] [High performance] [Super simple] [Comprehensive features] [ Multi-database compatible] [Suitable products]

Support .NET

.net framework.net core3.1.ne5.net6.net7.net8 .net9 .net10

Support database

MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、 人大金仓(国产推荐)、神通数据库、瀚高、Access 、OceanBase TDengine QuestDb Clickhouse MySqlConnector、华为 GaussDB 南大通用 GBase、MariaDB、Tidb、Odbc、Percona Server, Amazon Aurora、Azure Database for MySQL、 Google Cloud SQL for MySQL、custom database

Description

  1. Truly achieve zero SQL ORM table building, index and CRUD all support
  2. Support.NET millions of big data write, update, subtable and has billions of query statistics mature solutions
  3. Support SAAS complete application: cross-database query, audit, tenant sub-database, tenant sub-table and tenant data isolation
  4. Support low code + workflow (dynamic class building, dynamic table building, non-entity multi-library compatible with CRUD, JSON TO SQL, custom XML, etc.)
  5. Support ValueObject, discriminator, repository, UnitOfWork, DbContext, AOP

Documentation

Other Select Insert Update Delete
Nuget Query Insert Update Delete
Start guide Join query Insert without entity Update without entity Delete without entity
Multiple databases Include query Include Insert Include Update Include Delete
中文文档 Cross database query Insert by json Update by json Delete by json

Feature characteristic

Feature1 : Join query

Super simple query syntax

var query = db.Queryable() .LeftJoin ((o, cus) => o.CustomId == cus.Id) .LeftJoin ((o, cus, oritem ) => o.Id == oritem.OrderId) .LeftJoin ((o, cus, oritem , oritem2) => o.Id == oritem2.OrderId) .Where(o => o.Id == 1)
.Select((o, cus) => new ViewOrder { Id = o.Id, CustomName = cus.Name }) .ToList();

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

Feature2 :Include Query、Insert、Delete and Update

//Includes var list=db.Queryable() .Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street) //multi-level .Includes(x => x.ClassInfo) .ToList();

//Includes+left join
var list5= db.Queryable() .Includes(x => x.school_001, x => x.rooms) .Includes(x => x.books) .LeftJoin((x, y) => x.Id==y.sid) .Select((x,y) => new Student_004DTO { SchoolId = x.SchoolId, books = x.books, school_001 = x.school_001, Name=y.Name }) .ToList();

Feature3 : Page query

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

Feature4 : Dynamic expression

var names= new string [] { "a","b"}; Expressionable exp = new Expressionable(); foreach (var item in names) { exp.Or(it => it.Name.Contains(item.ToString())); } var list= db.Queryable().Where(exp.ToExpression()).ToList();

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

Feature5 : Multi-tenant transaction

//Creaate database object SqlSugarClient db = new SqlSugarClient(new List() { new ConnectionConfig(){ ConfigId="0", DbType=DbType.SqlServer, ConnectionString=Config.ConnectionString, IsAutoCloseConnection=true }, new ConnectionConfig(){ ConfigId="1", DbType=DbType.MySql, ConnectionString=Config.ConnectionString4 ,IsAutoCloseConnection=true} });

var mysqldb = db.GetConnection("1");//mysql db var sqlServerdb = db.GetConnection("0");// sqlserver db

db.BeginTran(); mysqldb.Insertable(new Order() { CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1 }).ExecuteCommand(); mysqldb.Queryable().ToList(); sqlServerdb.Queryable().ToList();

db.CommitTran();

Feature6 : Singleton Pattern

Implement transactions across methods

public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig() { DbType = SqlSugar.DbType.SqlServer, ConnectionString = Config.ConnectionString, IsAutoCloseConnection = true }, db=> { db.Aop.OnLogExecuting = (s, p) => { Console.WriteLine(s); }; });

using (var tran = Db.UseTran()) {

           new Test2().Insert(XX);
           new Test1().Insert(XX);
           ..... 
            ....
                     
         tran.CommitTran();

}

Feature7 : Query filter

//set filter db.QueryFilter.Add(new TableFilterItem(it => it.Name.Contains("a")));

db.Queryable().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

insert or update

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

Feature9 : Auto split table

Split entity

[SplitTable(SplitType.Year)]//Table by year (the table supports year, quarter, month, week and day) [SugarTable("SplitTestTable_{year}{month}{day}")] public class SplitTestTable { [SugarColumn(IsPrimaryKey =true)] public long Id { get; set; }

 public string Name { 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] 
 public DateTime CreateTime { get; set; }

}

Split query

var lis2t = db.Queryable() .SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now) .ToPageList(1,2);　

Feature10 : Big data insert or update

10.1 BulkCopy db.Fastest().BulkCopy(lstData);//insert db.Fastest().PageSize(100000).BulkCopy(insertObjs); db.Fastest<System.Data.DataTable>().AS("order").BulkCopy(dataTable);

10.2 BulkUpdate db.Fastest().BulkUpdate(GetList())//update db.Fastest().PageSize(100000).BulkUpdate(GetList()) db.Fastest().BulkUpdate(GetList(),new string[] { "Id"});//no primary key db.Fastest().BulkUpdate(GetList(), new string[]{"id"}, new string[]{"name","time"})//Set the updated column //DataTable
db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id");//Id is primary key db.Fastest<System.Data.DataTable>().AS("Order").BulkUpdate(dataTable,"Id",Set the updated column);

10.3 BulkMerge （5.1.4.109） db.Fastest().BulkMerge(List); db.Fastest().PageSize(100000).BulkMerge(List);

10.4 BulkQuery db.Queryable().ToList();//Slightly faster than Dapper //Suitable for big data export List order = new List(); db.Queryable().ForEach(it=> { order.Add(it); } ,2000);

10.5 BulkDelete db.Deleteable(list).PageSize(1000).ExecuteCommand();