DBShadow.net之化繁为简

一、简化查询

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,谢谢！！！