一、简化查询
1. 先看一下查询的例子
/* by yours.tools - online tools website : yours.tools/zh/md5.html */ /// <summary> /// 账户获取服务 /// </summary> /// <param name="table"></param> /// <param name="builder"></param> public class AccountGetService(AccountTable table, IShadowBuilder builder) { private readonly SqlSource _source = new(builder.DataSource); private readonly IParamQuery<Account, Account> _accountQuery = builder.BuildResult<Account, Account>( table.ToQuery() .And(account => account.Id.Equal()) .ToSelect() .SelectSelfColumns() ); /// <summary> /// 获取账户 /// </summary> /// <param name="param"></param> /// <param name="token"></param> /// <returns></returns> public Task<Account?> GetAsync(Account param, CancellationToken token = default) => _accountQuery.GetFirstAsync(_source, param, token); }2. 调用方式如下
/* by yours.tools - online tools website : yours.tools/zh/md5.html */ var account = await accountGetService.GetAsync(new Account { Id = 1L });3. 这个例子我们可以简化一下
- 参数直接使用Id(类型由Account简化为long)
- 很大一部分数据库操作都只有一个参数(GetById、GetByName等)
- 为此定义一个含单个属性类有点浪费
- 按上面的例子使用实体类作为参数也感觉怪怪的
- DBShadow.net支持这种简化操作
- 单个参数无需封装,直接使用参数类型即可
public class AccountGetService(AccountTable table, IShadowBuilder builder) { private readonly SqlSource _source = new(builder.DataSource); private readonly IParamQuery<long, Account> _accountQuery = builder.BuildResult<long, Account>( table.ToQuery() .And(account => account.Id.Equal()) .ToSelect() .SelectSelfColumns() ); public Task<Account?> GetAsync(long accountId, CancellationToken token = default) => _accountQuery.GetFirstAsync(_source, param, token); } var account = await accountGetService.GetAsync(1L);4. 特别注意不要在Dapper中这样使用
- Dapper是不支持这种简化操作
- 以下Dapper错误示例会抛出异常
- System.InvalidOperationException:“Must add values for the following parameters: @Id”
await using var conn = _dataSource.CreateConnection(); var sql = "SELECT \"Id\",\"Title\",\"Content\",\"Done\",\"LastTime\" FROM \"Todo\" WHERE \"Id\"=@Id"; var first = await conn.QueryFirstOrDefaultAsync<Todo>(sql, 1L);二、 集合参数也支持简化操作
1. IN查询简化的例子
- In的参数化名是可选和前面例子的Equal是一样的,默认字段名
- In查询的实参支持数组、集合及字典
- 只有In一个集合参数是支持直接传数组、集合或字典
- 这时参数名(eg: AccountIds)就无所谓取什么名了
- 因为不需要反射获取属性值了
/// <summary> /// 批量账户获取服务 /// </summary> /// <param name="table"></param> /// <param name="builder"></param> public class AccountBatchService(AccountTable table, IShadowBuilder builder) { private readonly SqlSource _source = new(builder.DataSource); private readonly IParamQuery<long[], Account> _accountQuery = builder.BuildResult<long[], Account>( table.ToQuery() .And(account => account.Id.In("AccountIds")) .ToSelect() .SelectSelfColumns() ); /// <summary> /// 批量获取账户 /// </summary> /// <param name="accountIds"></param> /// <param name="token"></param> /// <returns></returns> public IAsyncEnumerable<Account> GetAsync(long[] accountIds, CancellationToken token = default) => _accountQuery.QueryAsync(_source, accountIds, token); }2. 以上调用的例子如下
[Fact] public async Task Batch() { var count = 0; var service = new AccountBatchService(table, builder); var list = service.GetAsync([1L, 2L, 3L]); await foreach (var item in list) { _output.WriteLine($"{item.Id}:{item.Amount}"); count++; } Assert.Equal(3, count); } // 1:100 // 2:200 // 3:300三、泛型查询
1. 泛型服务类代码
- 该服务用来按字段Id查询表Account
- 参数TParam是泛型,这样可以直接使用DTO参数来查询,减少类型转化的开销
- 返回值TAccount也是泛型,这样就可以直接返回视图模型或者领域模型
- 这样的泛型服务类就非常的通用
/// <summary> /// 账户获取泛型服务 /// </summary> /// <param name="table"></param> /// <param name="builder"></param> public class AccountGetService<TParam, TAccount>(AccountTable table, IShadowBuilder builder) { private readonly SqlSource _source = new(builder.DataSource); private readonly IParamQuery<TParam, TAccount> _accountQuery = builder.BuildResult<TParam, TAccount>( table.ToQuery() .And(account => account.Id.Equal()) .ToSelect() .SelectSelfColumns() ); /// <summary> /// 获取账户 /// </summary> /// <param name="param"></param> /// <param name="token"></param> /// <returns></returns> public Task<TAccount?> GetAsync(TParam param, CancellationToken token = default) => _accountQuery.GetFirstAsync(_source, param, token); }2. 中规中矩的调用方式
var service = new AccountGetService<Account, Account>(_table, _builder); var account = await service.GetAsync(new Account { Id = 1L }); Assert.NotNull(account);3. 支持简单调用方式
var service = new AccountGetService<long, Account>(_table, _builder); var account = await service.GetAsync(1L); Assert.NotNull(account);四、总结
- DBShadow.net预编译比较智能
- 只有1个参数时支持化繁为简,支持直接传值做为参数值
- 这样可以节约定义只有一个属性的参数类
- 参数和返回值类型还可以定义为泛型,可以做到更加灵活
另外源码托管地址: https://github.com/donetsoftwork/DBShadow.net ，欢迎大家直接查看源码。
gitee同步更新:https://gitee.com/donetsoftwork/DBShadow.net
如果大家喜欢请动动您发财的小手手帮忙点一下Star,谢谢！！！
