English | 中文
SqlSugar ORM is a library providing Object/Relational Mapping (ORM)
An ORM framework from the future
Using SqlSugar is very simple , And it's powerful.
- Support Cross database query
- Support SqlServer、MySql、PgSql and Oracle insert bulkcopy
- Split table big data self-processing
- Support Multi-tenant, multi-library transactions
- Support CodeFirst data migration.
- Support Join query 、 Union all 、 Subquery
- Support Configure the query
- Support DbFirst import entity class from database, or use Generation Tool.
- Support one-to-many and many-to-many navigation properties
- Support MySql、SqlServer、Sqlite、Oracle 、 postgresql 、QuestDb、ClickHouse、达梦、人大金仓 、神通数据库、瀚高、MsAccess、华为GaussDB、GBase 8s、Odbc、Custom
- Support AOP 、 Diff Log 、 Query Filter
Super simple query syntax
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)//query by nav
var list=db.Queryable<Test>()
.Includes(x => x.Provinces,x=>x.Citys ,x=>x.Street) //multi-level
.Includes(x => x.ClassInfo)
.ToList();
//insert by nav
db.InsertNav(list) //Finer operation than EFCore's SaveChange
.Include(z1 => z1.SchoolA).ThenInclude(z1 => z1.RoomList)//multi-level
.Include(z1 => z1.Books)
.ExecuteCommand();
//delete by nav
db.DeleteNav<Student>(it=>it.Id==1)
.Include(z1 => z1.SchoolA) .ThenInclude(z1 => z1.RoomList)//multi-level
.Include(z1 => z1.Books)
.ExecuteCommand();
//update by nav
db.UpdateNav(list)
.Include(z1 => z1.SchoolA) .ThenInclude(z1 => z1.RoomList)//multi-level
.Include(z1 => z1.Books)
.ExecuteCommand(); int pageIndex = 1;
int pageSize = 20;
int totalCount=0;
var page = db.Queryable<Student>().ToPageList(pageIndex, pageSize, ref totalCount);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))+'%')
)//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();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();
}//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+'%')
insert or update
var x = Db.Storageable(list2).ToStorage();
x.AsInsertable.ExecuteCommand();
x.AsUpdateable.ExecuteCommand(); insert into not exists
var x = Db.Storageable(list).SplitInsert(it => !it.Any()).ToStorage()
x.AsInsertable.ExecuteCommand(); 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<OrderSpliteTest>()
.SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now)
.ToPageList(1,2); //Insert A million only takes a few seconds
db.Fastest<RealmAuctionDatum>().BulkCopy(GetList());
//update A million only takes a few seconds
db.Fastest<RealmAuctionDatum>().BulkUpdate(GetList());//A million only takes a few seconds完
db.Fastest<RealmAuctionDatum>().BulkUpdate(GetList(),new string[]{"id"},new string[]{"name","time"})//no primary key
//if exists update, else insert
var x= db.Storageable<Order>(data).ToStorage();
x.BulkCopy();
x.BulkUpdate();
//set table name
db.Fastest<RealmAuctionDatum>().AS("tableName").BulkCopy(GetList())
//set page
db.Fastest<Order>().PageSize(300000).BulkCopy(insertObjs);