Expression表达式转换为SQL语句，支持SqlServer、MySql、Oracle、Sqlite、PostgreSql

<p></p> <p align="center"> <img src="https://zqlovejyc.gitee.io/zqutils-js/Images/SQL.png" height="80"/> </p> <div align="center"> [](https://gitee.com/zqlovejyc/SQLBuilder.Core/stargazers) [](https://gitee.com/zqlovejyc/SQLBuilder.Core/members) [](https://github.com/zqlovejyc/SQLBuilder.Core/stargazers) [](https://github.com/zqlovejyc/SQLBuilder.Core/network) [](https://github.com/zqlovejyc/SQLBuilder.Core/blob/master/LICENSE) [](https://www.nuget.org/packages/Zq.SQLBuilder.Core) </div> <div align="left"> .NET Standard 2.1、.NET 5 版本SQLBuilder，Expression表达式转换为SQL语句，支持SqlServer、MySql、Oracle、Sqlite、PostgreSql；基于Dapper实现了不同数据库对应的数据仓储Repository； </div> ## 🌭 开源地址 - Gitee：[https://gitee.com/zqlovejyc/SQLBuilder.Core](https://gitee.com/zqlovejyc/SQLBuilder.Core) - GitHub：[https://github.com/zqlovejyc/SQLBuilder.Core](https://github.com/zqlovejyc/SQLBuilder.Core) - NuGet：[https://www.nuget.org/packages/Zq.SQLBuilder.Core](https://www.nuget.org/packages/Zq.SQLBuilder.Core) - MyGet：[https://www.myget.org/feed/zq-myget/package/nuget/Zq.SQLBuilder.Core](https://www.myget.org/feed/zq-myget/package/nuget/Zq.SQLBuilder.Core) ## 🥥 框架扩展包 | 包类型 | 名称 | 版本 | 描述 | | :---------------------------------------------------------------------------------------------------------------------------------------------: | ------------------------------------------ | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------: | -------------------------- | | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core) | Zq.SQLBuilder.Core | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core) | SQLBuilder.Core 核心包 | | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core.SkyWalking) | Zq.SQLBuilder.Core.SkyWalking | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core.SkyWalking) | SQLBuilder.Core SkyWalking 扩展包 | | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core.ElasticApm) | Zq.SQLBuilder.Core.ElasticApm | [](https://www.nuget.org/packages/Zq.SQLBuilder.Core.ElasticApm) | SQLBuilder.Core ElasticApm扩展包 | ## 🚀 快速入门 - #### ➕ 新增 ```csharp //新增 await _repository.InsertAsync(entity); //批量新增 await _repository.InsertAsync(entities); //新增 await SqlBuilder .Insert<MsdBoxEntity>(() => entity) .ExecuteAsync( _repository); //批量新增 await SqlBuilder .Insert<MsdBoxEntity>(() => new[] { new UserInfo { Name = "张三", Sex = 2 }, new UserInfo { Name = "张三", Sex = 2 } }) .ExecuteAsync( _repository); ``` - #### 🗑 删除 ```csharp //删除 await _repository.DeleteAsync(entity); //批量删除 await _repository.DeleteAsync(entitties); //条件删除 await _repository.DeleteAsync<MsdBoxEntity>(x => x.Id == "1"); //删除 await SqlBuilder .Delete<MsdBoxEntity>() .Where(x => x.Id == "1") .ExecuteAsync( _repository); //主键删除 await SqlBuilder .Delete<MsdBoxEntity>() .WithKey("1") .ExecuteAsync( _repository); ``` - #### ✏ 更新 ```csharp //更新 await _repository.UpdateAsync(entity); //批量更新 await _repository.UpdateAsync(entities); //条件更新 await _repository.UpdateAsync<MsdBoxEntity>(x => x.Id == "1", () => entity); //更新 await SqlBuilder .Update<MsdBoxEntity>(() => entity, DatabaseType.MySql, isEnableFormat:true) .Where(x => x.Id == "1") .ExecuteAsync( _repository); ``` - #### 🔍 查询 ```csharp //简单查询 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,