//简单查询
await _repository.FindListAsync<MsdBoxEntity>(x => x.Id == "1");
//连接查询
await SqlBuilder
.Select<UserInfo, UserInfo, Account, Student, Class, City, Country>((u, t, a, s, d, e, f) =>
new { u.Id, UId = t.Id, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name })
.Join<UserInfo>((x, t) =>
x.Id == t.Id) //注意此处单表多次Join所以要指明具体表别名，否则都会读取第一个表别名
.Join<Account>((x, y) =>
x.Id == y.UserId)
.LeftJoin<Account, Student>((x, y) =>
x.Id == y.AccountId)
.RightJoin<Student, Class>((x, y) =>
x.Id == y.UserId)
.InnerJoin<Class, City>((x, y) =>
x.CityId == y.Id)
.FullJoin<City, Country>((x, y) =>
x.CountryId == y.Id)
.Where(x =>
x.Id != null)
.ToListAsync(
_repository);
//分页查询
var condition = LinqExtensions
.True<UserInfo, Account>()
.And((x, y) =>
x.Id == y.UserId)
.WhereIf(
!name.IsNullOrEmpty(),
(x, y) => name.EndsWith("∞")
? x.Name.Contains(name.Trim('∞'))
: x.Name == name);
var hasWhere = false;
await SqlBuilder
.Select<UserInfo, Account>(
(u, a) => new { u.Id, UserName = "u.Name" })
.InnerJoin<Account>(
condition)
.WhereIf(
!name.IsNullOrEmpty(),
x => x.Email != null &&
(!name.EndsWith("∞") ? x.Name.Contains(name.TrimEnd('∞', '*')) : x.Name == name),
ref hasWhere)
.WhereIf(
!email.IsNullOrEmpty(),
x => x.Email == email,
ref hasWhere)
.ToPageAsync(
_repository.UseMasterOrSlave(false),
input.OrderField,
input.Ascending,
input.PageSize,
input.PageIndex);
//仓储分页查询
await _repository.FindListAsync(condition, input.OrderField, input.Ascending, input.PageSize, input.PageIndex);
//高级查询
Func<string[], string> @delegate = x => $"ks.{x[0]}{x[1]}{x[2]} WITH(NOLOCK)";
await SqlBuilder
.Select<UserInfo, Account, Student, Class, City, Country>((u, a, s, d, e, f) =>
new { u, a.Name, StudentName = s.Name, ClassName = d.Name, e.CityName, CountryName = f.Name },
tableNameFunc: @delegate)
.Join<Account>((x, y) =>
x.Id == y.UserId,
@delegate)
.LeftJoin<Account, Student>((x, y) =>
x.Id == y.AccountId,
@delegate)
.RightJoin<Class, Student>((x, y) =>
y.Id == x.UserId,
@delegate)
.InnerJoin<Class, City>((x, y) =>
x.CityId == y.Id,
@delegate)
.FullJoin<City, Country>((x, y) =>
x.CountryId == y.Id,
@delegate)
.Where(u =>
u.Id != null)
.ToListAsync(
_repository);
//appsettions.json
"ConnectionStrings": {
"Base": [
"Oracle",
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.102)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=name)));Persist Security Info=True;User ID=test;Password=123;",
"Cap": [
"MySql",
"Server=127.0.0.1;Database=db;Uid=root;Pwd=123456;SslMode=None;"
"Oracle": [ "Oracle", "数据库连接字符串" ],
"MySql": [ "MySql", "数据库连接字符串" ],
"Sqlserver": [ "SqlServer", "数据库连接字符串" ],
"Sqlite": [ "Sqlite", "数据库连接字符串" ],
"Pgsql": [ "PostgreSql", "数据库连接字符串" ]
//方式一
IRepository trans = null;
//开启事务
trans = await _repository.BeginTransactionAsync();
//数据库写操作
await trans.InsertAsync(entity);
//提交事务
await trans.CommitAsync();
catch (Exception)
//回滚事务
if(trans != null)
await tran.RollbackAsync();
throw;
//方式二
var res = await _repository.ExecuteTransactionAsync(async trans =>
var retval = (await trans.InsertAsync(entity)) > 0;
if (input.Action.EqualIgnoreCase(UnitAction.InDryBox))
code = await _unitInfoService.InDryBoxAsync(dryBoxInput);
code = await _unitInfoService.OutDryBoxAsync(dryBoxInput);
return code == ErrorCode.Successful && retval;
});private readonly Func<string, IRepository> _handler;
private readonly IRepository _repository;
public MyService(Func<string, IRepository> hander)
_handler = hander;
//默认base数据仓储
_repository = hander(null);
//base仓储
var baseRepository = _handler("Base");
//cap仓储
var capRepository = _handler("Cap");//方式一
_repository.Master = false;
//方式二
_repository.UseMasterOrSlave(master)
//注入SQLBuilder SkyWalking链路追踪
services.AddSqlBuilderSkyApm()
//使用SQLBuilder ElasticApm链路追踪
app.UseSqlBuilderElasticApm(Configuration)
单元测试 https://github.com/zqlovejyc/SQLBuilder.Core/tree/master/SQLBuilder.Core.UnitTest
SQLBuilder.Core 遵循 Apache-2.0 开源协议，欢迎大家提交 PR 或 Issue。
.NET Standard 2.1、.NET 5、.NET 6、.NET 7 版本SQLBuilder，Expression表达式转换为SQL语句，支持SqlServer、MySql、Oracle、Sqlite、PostgreSql；基于Dapper实现了不同数据库对应的数据仓储Repository；
Topics
repository
expression
dapper
sqlbuilder
