SqlSugar ORM
优点:
SqlSugar 是 .NET 开源 ORM 框架,由 Fructose 大数据技术团队维护和更新,是开箱即用最易用的 ORM
优点: 【低代码】【高性能】【超简单】【功能综合】【多数据库兼容】【适用产品】
支持 .NET
.NET framework.net core3.1.ne5.net6.net7.net8 .net9 .net10
支持数据库
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
描述
- 真正实现零SQL、ORM表构建、索引和CRUD全部支持
- Support.NET 百万大数据写入、更新、子表,并拥有数十亿查询统计成熟的解决方案
- 支持SAAS完整应用:跨库查询、审计、租户子数据库、租户子表、租户数据隔离
- 支持低代码+工作流(动态类构建、动态表构建、兼容 CRUD、JSON TO SQL、自定义 XML 等的非实体多库)
- 支持 ValueObject、鉴别器、存储库、UnitOfWork、DbContext、AOP
特征
特点一:联接查询
超级简单的查询语法
var query = 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) => 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)
特点二:包括查询、插入、删除和更新
//Includes var list=db.Queryable<Test>() .Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street) //multi-level .Includes(x => x.ClassInfo) .ToList(); //Includes+left join var list5= 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) => new Student_004DTO { SchoolId = x.SchoolId, books = x.books, school_001 = x.school_001, Name=y.Name }) .ToList();
Feature3 : 页面查询
int pageIndex = 1; int pageSize = 20; int totalCount=0; var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);
特点4:动态表达
var names= new string [] { "a","b"}; Expressionable<Order> exp = new Expressionable<Order>(); foreach (var item in names) { exp.Or(it => it.Name.Contains(item.ToString())); } var list= db.Queryable<Order>().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))+'%') )
特点5:多租户事务
//Creaate database object链接多数据库 SqlSugarClient db = new SqlSugarClient(new List<ConnectionConfig>() { 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<Order>().ToList(); sqlServerdb.Queryable<Order>().ToList(); db.CommitTran();
特点6:单例模式
跨方法实现事务
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 : 查询过滤器
//set filter db.QueryFilter.Add(new TableFilterItem<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+'%')
功能8:插入或更新
插入或更新
Db.Storageable(list2).ExecuteCommand(); Db.Storageable(list2).PageSize(1000).ExecuteCommand(); Db.Storageable(list2).PageSize(1000,exrows=> { }).ExecuteCommand();
特点9:自动拆分表
拆分实体
[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; } }
拆分查询
var lis2t = db.Queryable<OrderSpliteTest>() .SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now) .ToPageList(1,2);
Feature10 : 大数据插入或更新
10.1 批量拷贝 db.Fastest<Order>().BulkCopy(lstData);//insert db.Fastest<Order>().PageSize(100000).BulkCopy(insertObjs); db.Fastest<System.Data.DataTable>().AS("order").BulkCopy(dataTable); 10.2 批量更新 db.Fastest<Order>().BulkUpdate(GetList())//update db.Fastest<Order>().PageSize(100000).BulkUpdate(GetList()) db.Fastest<Order>().BulkUpdate(GetList(),new string[] { "Id"});//no primary key db.Fastest<Order>().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 批量合并(5.1.4.109) db.Fastest<Order>().BulkMerge(List); db.Fastest<Order>().PageSize(100000).BulkMerge(List); 10.4 批量查询 db.Queryable<Order>().ToList();//Slightly faster than Dapper //Suitable for big data export List<Order> order = new List<Order>(); db.Queryable<Order>().ForEach(it=> { order.Add(it); } ,2000); 10.5批量删除 db.Deleteable<Order>(list).PageSize(1000).ExecuteCommand();
中文文档地址:
SqlSugar .Net ORM 5.X 官网 、文档、教程 - SqlSugar 5x - .NET果糖网 (donet5.com)
码字不易,如果您觉的我的文章对您有帮助的话,建议您在经济能力之内慷慨打赏一元给我买瓶水, 这将是我下一步继续书写本题目的动力;如果您囊肿羞涩也没有关系,希望您点个关注,写点评论;您的支持将是我创作之路上的无线动力;青山依旧绿水长流,希望我们下期来能再见。