如何使用仓储 - 基础功能
前言
Adnc使用了Mariadb与Mongodb两种数据库,分别对应基础架构层22.Adnc.Infr.EfCore
,23.Adnc.Infr.Mongo
这两个项目。Mariadb用于存储业务数据,Mongodb用于存储登录日志、操作日志与异常日志(Nlog)。Mongodb的仓储只用到了很少的功能,本文不做介绍。需要重点介绍的如何使用22.Adnc.Infr.EfCore
操作Mariadb。
总体设计
EfRepository<TEntity> : AbstractEfBaseRepository<AdncDbContext, TEntity>, IEfRepository<TEntity> where TEntity : EfEntity
该仓储实现了IEfRepository<TEntity>
接口,提供的方法是最全的且非常灵活,适合经典三层开发模式。TEntity
需要直接继承或间接继承EfEntity
。
EfBasicRepository<TEntity> : AbstractEfBaseRepository<AdncDbContext, TEntity>, IEfBasicRepository<TEntity> where TEntity : AggregateRoot
该仓储实现了IEfBasicRepository<TEntity>
接口,只提供了一些基本方法,且都是调用EF的原生方法,适合DDD开发模式使用。TEntity
需要直接继承或间接继承AggregateRoot
。
因为EfRepository<TEntity>
与EfBasicRepository<TEntity>
都继承了AbstractEfBaseRepository<TDbContext, TEntity>
这个抽象类,它们一部分相同的方法。本文重点介绍EfRepository<TEntity>
。
仓储注册
在Webapi
工程的Startup.cs
文件注册
public void ConfigureContainer(ContainerBuilder builder)
{
//注册Mongodb模块
builder.RegisterModule<Adnc.Infr.Mongo.AdncInfrMongoModule>();
//注册Efcore模块
builder.RegisterModule<Adnc.Infr.EfCore.AdncInfrEfCoreModule>();
//注册Application模块
builder.RegisterModule(new Adnc.Cus.Application.AdncCusApplicationModule());
}
构造函数注入
public CustomerAppService(IEfRepository<Customer> customerRepo)
{
_customerRepo = customerRepo;
}
仓储方法
本文所有的单元测试用例都在EfCoreRepositoryTests.cs
文件
https://github.com/AlphaYu/Adnc/blob/master/test/Adnc.UnitTest/EfCoreRepositoryTests.cs
InsertAsync
方法签名
/// <summary>
/// 插入单个实体
/// </summary>
/// <param name="entity"><see cref="TEntity"/></param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> InsertAsync(TEntity entity, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestInsert()
{
var id = IdGenerater.GetNextId(IdGenerater.DatacenterId, IdGenerater.WorkerId);
var radmon = (long)(DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
var cusotmer = new Customer
{
Id = id
,
Account = $"a{radmon}"
,
Realname = $"r{radmon}"
,
Nickname = $"n{radmon}"
,
FinanceInfo = new CustomerFinance { Id = id, Account = $"a{radmon}", Balance = 0 }
};
await _customerRsp.InsertAsync(cusotmer);
}
生成的Sql
--EF生成的Sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION
INSERT INTO `Customer` (`Id`, `Account`, `CreateBy`, `CreateTime`, `ModifyBy`, `ModifyTime`, `Nickname`, `Realname`)
VALUES (156081863398658048, 'a1615020816095', 1600000000000, TIMESTAMP('2021-03-06 16:53:37.051070'), NULL, NULL, 'n1615020816095', 'r1615020816095')
INSERT INTO `CustomerFinance` (`Id`, `Account`, `Balance`, `CreateBy`, `CreateTime`, `ModifyBy`, `ModifyTime`)
VALUES (156081863398658048, 'a1615020816095', 0, 1600000000000, TIMESTAMP('2021-03-06 16:53:37.051179'), NULL, NULL);
COMMIT
InsertRangeAsync
方法签名
/// <summary>
/// 批量插入实体
/// </summary>
/// <param name="entities"><see cref="TEntity"/></param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> InsertRangeAsync(IEnumerable<TEntity> entities, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestInsertRange()
{
var customer = await _customerRsp.FetchAsync(x => x.Id > 1);
var logs = new List<CustomerTransactionLog>
{
new CustomerTransactionLog{ Id=IdGenerater.GetNextId(IdGenerater.DatacenterId, IdGenerater.WorkerId),Account=customer.Account,ChangedAmount=0,Amount=0,ChangingAmount=0,CustomerId=customer.Id,ExchangeType=ExchangeTypeEnum.Recharge,ExchageStatus=ExchageStatusEnum.Finished,Remark="test"}
,
new CustomerTransactionLog{ Id=IdGenerater.GetNextId(IdGenerater.DatacenterId, IdGenerater.WorkerId),Account=customer.Account,ChangedAmount=0,Amount=0,ChangingAmount=0,CustomerId=customer.Id,ExchangeType=ExchangeTypeEnum.Recharge,ExchageStatus=ExchageStatusEnum.Finished,Remark="test"}
};
await _custLogsRsp.InsertRangeAsync(logs);
}
生成的Sql
--EF生成的sql
INSERT INTO `CustomerTransactionLog` (`Id`, `Account`, `Amount`, `ChangedAmount`, `ChangingAmount`, `CreateBy`, `CreateTime`, `CustomerId`, `ExchageStatus`, `ExchangeType`, `Remark`)
VALUES
(156054605791367168, 'a1615011019854', 0, 0, 0, 1600000000000, timestamp('2021-03-06 15:05:17.517754'), 156040774981652480, 2008, 8000, 'test'),
(156054605799755776, 'a1615011019854', 0, 0, 0, 1600000000000, timestamp('2021-03-06 15:05:17.517847'), 156040774981652480, 2008, 8000, 'test')
UpdateAsync
不指定更新字段,实体必须是跟踪状态。如果不是会显示抛出异常,提示需要指定更新列。
方法签名
/// <summary>
/// 更新单个实体
/// </summary>
/// <param name="entity"><see cref="TEntity"/></param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> UpdateAsync(TEntity entity, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestUpdateWithTraking()
{
//IEfRepository<>默认关闭了跟踪,需要手动开启跟踪
var customer = await _customerRsp.FetchAsync(x => x.Id > 1, noTracking: false);
//实体已经被跟踪
customer.Realname = "被跟踪";
await _customerRsp.UpdateAsync(customer);
var newCusts = await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE Id=@Id", customer);
Assert.Equal("被跟踪", newCusts.FirstOrDefault().Realname);
//实体已经被跟踪,主从表同时更新
customer = await _customerRsp.FetchAsync(x => x.Id > 1, x => x.FinanceInfo, noTracking: false);
customer.Account = "主从更新";
customer.FinanceInfo.Account = "主从更新";
await _customerRsp.UpdateAsync(customer);
var newCust = await _customerRsp.FetchAsync(x => x.Id == customer.Id, x => x.FinanceInfo);
Assert.Equal("主从更新", newCust.Account);
Assert.Equal("主从更新", newCust.FinanceInfo.Account);
}
生成的Sql
--EF生成的sql
UPDATE `Customer` SET `ModifyBy` = 1600000000000, `ModifyTime` = timestamp('2021-03-06 14:49:52.377305'), `Realname` = '被跟踪' WHERE `Id` = 156040774981652480;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION
UPDATE `Customer` SET `Account` = '主从更新', `ModifyTime` = TIMESTAMP('2021-03-06 21:39:06.906343')
WHERE `Id` = 156151316735987712;
SELECT ROW_COUNT()
UPDATE `CustomerFinance` SET `Account` = '主从更新', `ModifyBy` = 1600000000000, `ModifyTime` = TIMESTAMP('2021-03-06 21:39:06.906354')
WHERE `Id` = 156151316735987712 AND `RowVersion` = TIMESTAMP('2021-03-06 21:29:35.274606');
SELECT `RowVersion`
FROM `CustomerFinance`
WHERE ROW_COUNT() = 1 AND `Id` = 156151316735987712
COMMIT
UpdateAsync
指定更新字段,实体可以是任何状态。
方法签名
/// <summary>
/// 更新单个实体
/// </summary>
/// <param name="entity"><see cref="entity"/></param>
/// <param name="updatingExpressions">需要更新列的表达式树数组</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> UpdateAsync(TEntity entity, Expression<Func<TEntity, object>>[] updatingExpressions, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async void TestUpdateAssigns()
{
var customer = await _customerRsp.FindAsync(154951941552738304, noTracking: false);
//实体已经被跟踪并且指定更新列
customer.Nickname = "更新指定列";
customer.Realname = "不指定该列";
//更新列没有指定Realname,该列不会被更新
await _customerRsp.UpdateAsync(customer, UpdatingProps<Customer>(c => c.Nickname));
var newCus = (await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE ID=@ID", customer)).FirstOrDefault();
Assert.Equal("更新指定列", newCus.Nickname);
Assert.NotEqual("不指定该列", newCus.Realname);
//实体没有被跟踪,dbcontext中有同名实体
var id = customer.Id;
await _customerRsp.UpdateAsync(new Customer { Id = id, Realname = "没被跟踪01", Nickname = "新昵称" }, UpdatingProps<Customer>(c => c.Realname, c => c.Nickname));
newCus = (await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE ID=@ID", customer)).FirstOrDefault();
Assert.Equal("没被跟踪01", newCus.Realname);
Assert.Equal("新昵称", newCus.Nickname);
//实体没有被跟踪,dbcontext中有没有同名实体
id = 154959990543749120;
await _customerRsp.UpdateAsync(new Customer { Id = id, Realname = "没被跟踪02", Nickname = "新昵称" }, UpdatingProps<Customer>(c => c.Realname, c => c.Nickname));
newCus = await _customerRsp.FindAsync(id);
Assert.Equal("没被跟踪02", newCus.Realname);
Assert.Equal("新昵称", newCus.Nickname);
}
生成的Sql
--EF生成的sql
UPDATE `Customer` SET `ModifyTime` = timestamp('2021-03-06 15:38:51.089013'), `Nickname` = '更新指定列'
WHERE `Id` = 154951941552738304;
UPDATE `Customer` SET `ModifyTime` = timestamp('2021-03-06 15:39:09.736015'), `Nickname` = '新昵称'
WHERE `Id` = 154951941552738304;
UPDATE `Customer` SET `ModifyBy` = 1600000000000, `ModifyTime` = timestamp('2021-03-06 15:39:21.223032'), `Nickname` = '新昵称', `Realname` = '没被跟踪02'
WHERE `Id` = 154959990543749120;
UpdateRangeAsync
根据条件批量更新
方法签名
/// <summary>
/// 批量更新
/// </summary>
/// <param name="whereExpression">查询条件</param>
/// <param name="updatingExpression">需要更新的字段</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> UpdateRangeAsync(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, TEntity>> updatingExpression, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async void TestUpdateRange()
{
var cus1 = await this.InsertCustomer();
var cus2 = await this.InsertCustomer();
var total = await _customerRsp.CountAsync(c => c.Id == cus1.Id || c.Id == cus2.Id);
Assert.Equal(2, total);
await _customerRsp.UpdateRangeAsync(c => c.Id == cus1.Id || c.Id == cus2.Id, x => new Customer { Realname = "批量更新" });
var result2 = await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE ID in @ids", new { ids = new[] { cus1.Id, cus2.Id } });
Assert.NotEmpty(result2);
Assert.Equal("批量更新", result2.FirstOrDefault().Realname);
Assert.Equal("批量更新", result2.LastOrDefault().Realname);
}
生成的Sql
--EF生成的sql
UPDATE `Customer` AS A
INNER JOIN ( SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE (`c`.`Id` = 156193791336910848) OR (`c`.`Id` = 156193806604177408)
) AS B ON A.`Id` = B.`Id`
SET A.`Realname` = '批量更新'
DeleteAsync
根据Id删除
方法签名
/// <summary>
/// 删除实体
/// </summary>
/// <param name="keyValue">Id</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> DeleteAsync(long keyValue, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestDelete()
{
//single hard delete
var customer = await this.InsertCustomer();
var customerFromDb = await _customerRsp.FindAsync(customer.Id);
Assert.Equal(customer.Id, customerFromDb.Id);
await _customerRsp.DeleteAsync(customer.Id);
var result = await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE ID=@Id", new { Id = customer.Id });
Assert.Empty(result);
}
生成的Sql
--EF生成的sql
DELETE FROM `Customer`
WHERE `Id` = 156040229583720448;
DeleteRangeAsync
根据条件批量删除
方法签名
/// <summary>
/// 批量删除实体
/// </summary>
/// <param name="whereExpression">查询条件</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> DeleteRangeAsync(Expression<Func<TEntity, bool>> whereExpression, CancellationToken cancellationToken = default);
单元测试
//测试批量删除
[Fact]
public async Task TestDeleteRange()
{
//batch hand delete
var cus1 = await this.InsertCustomer();
var cus2 = await this.InsertCustomer();
var total = await _customerRsp.CountAsync(c => c.Id == cus1.Id || c.Id == cus2.Id);
Assert.Equal(2, total);
await _customerRsp.DeleteRangeAsync(c => c.Id == cus1.Id || c.Id == cus2.Id);
var result2 = await _customerRsp.QueryAsync<Customer>("SELECT * FROM Customer WHERE ID in @ids", new { ids = new[] { cus1.Id, cus2.Id } });
Assert.Empty(result2);
}
生成的Sql
--EF生成的sql
DELETE A
FROM `Customer` AS A
INNER JOIN ( SELECT `c`.`Id`
FROM `Customer` AS `c`
WHERE (`c`.`Id` = 156163586249592832) OR (`c`.`Id` = 156163592918536192) ) AS B ON A.`Id` = B.`Id`
FindAsync
根据主键Id查询
方法签名
/// <summary>
/// 根据Id查询,返回单个实体
/// </summary>
/// <param name="keyValue">Id</param>
/// <param name="navigationPropertyPath">导航属性,可选参数</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <param name="noTracking">是否开启跟踪,默认不开启,可选参数</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns><see cref="TEntity"/></returns>
Task<TEntity> FindAsync(long keyValue, Expression<Func<TEntity, dynamic>> navigationPropertyPath = null, bool writeDb = false, bool noTracking = true, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestFind()
{
//不加载导航属性
var customer3 = await _customerRsp.FindAsync(154959990543749120);
Assert.NotNull(customer3);
Assert.Null(customer3.FinanceInfo);
//加载导航属性
var customer4 = await _customerRsp.FindAsync(154959990543749120, x => x.TransactionLogs);
Assert.NotNull(customer4);
Assert.NotEmpty(customer4.TransactionLogs);
}
生成的Sql
--EF生成的sql
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE `c`.`Id` = 154959990543749120
LIMIT 1
SELECT `t`.`Id`, `t`.`Account`, `t`.`CreateBy`, `t`.`CreateTime`, `t`.`ModifyBy`, `t`.`ModifyTime`, `t`.`Nickname`, `t`.`Realname`, `c0`.`Id`, `c0`.`Account`, `c0`.`Amount`, `c0`.`ChangedAmount`, `c0`.`ChangingAmount`, `c0`.`CreateBy`, `c0`.`CreateTime`, `c0`.`CustomerId`, `c0`.`ExchageStatus`, `c0`.`ExchangeType`, `c0`.`Remark`
FROM (
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE `c`.`Id` = 154959990543749120
LIMIT 1
) AS `t`
LEFT JOIN `CustomerTransactionLog` AS `c0` ON `t`.`Id` = `c0`.`CustomerId`
ORDER BY `t`.`Id`, `c0`.`Id`
FetchAsync
根据查询条件返回第一条
方法签名
/// <summary>
/// 根据条件查询,返回单个实体
/// </summary>
/// <param name="whereExpression">查询条件</param>
/// <param name="navigationPropertyPath">导航属性,可选参数</param>
/// <param name="orderByExpression">排序字段,默认主键,可选参数</param>
/// <param name="ascending">排序方式,默认逆序,可选参数</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <param name="noTracking">是否开启跟踪,默认不开启,可选参数</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
Task<TEntity> FetchAsync(Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, dynamic>> navigationPropertyPath = null, Expression<Func<TEntity, object>> orderByExpression = null, bool ascending = false, bool writeDb = false, bool noTracking = true, CancellationToken cancellationToken = default);
/// <summary>
/// 根据条件查询,返回单个实体或对象
/// </summary>
/// <typeparam name="TResult">匿名对象</typeparam>
/// <param name="selector">选择器</param>
/// <param name="whereExpression">查询条件</param>
/// <param name="orderByExpression">排序字段,默认主键,可选参数</param>
/// <param name="ascending">排序方式,默认逆序,可选参数</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <param name="noTracking">是否开启跟踪,默认不开启,可选参数</param>
/// <param name="cancellationToken"><see cref="CancellationToken"/></param>
Task<TResult> FetchAsync<TResult>(Expression<Func<TEntity, TResult>> selector, Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression = null, bool ascending = false, bool writeDb = false, bool noTracking = true, CancellationToken cancellationToken = default);
单元测试
[Fact]
public async Task TestFetch()
{
//指定列查询
var customer = await _customerRsp.FetchAsync(x => new { x.Id, x.Account}, x => x.Id > 1);
Assert.NotNull(customer);
//指定列查询,指定列包含导航属性
var customer2 = await _customerRsp.FetchAsync(x => new { x.Id, x.Account, x.FinanceInfo }, x => x.Id > 1);
Assert.NotNull(customer2);
//不指定列查询
var customer3 = await _customerRsp.FetchAsync(x => x.Id > 1);
Assert.NotNull(customer3);
//不指定列查询,预加载导航属性
var customer4 = await _customerRsp.FetchAsync(x => x.Id > 1, x => x.FinanceInfo);
Assert.NotNull(customer4);
}
生成的Sql
--EF生成的sql
SELECT `c`.`Id`, `c`.`Account`
FROM `Customer` AS `c`
WHERE `c`.`Id` > 1
ORDER BY `c`.`Id` DESC
LIMIT 1
SELECT `c`.`Id`, `c`.`Account`, `c0`.`Id`, `c0`.`Account`, `c0`.`Balance`, `c0`.`CreateBy`, `c0`.`CreateTime`, `c0`.`ModifyBy`, `c0`.`ModifyTime`, `c0`.`RowVersion`
FROM `Customer` AS `c`
LEFT JOIN `CustomerFinance` AS `c0` ON `c`.`Id` = `c0`.`Id`
WHERE `c`.`Id` > 1
ORDER BY `c`.`Id` DESC
LIMIT 1
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE `c`.`Id` > 1
ORDER BY `c`.`Id` DESC
LIMIT 1
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`, `c0`.`Id`, `c0`.`Account`, `c0`.`Balance`, `c0`.`CreateBy`, `c0`.`CreateTime`, `c0`.`ModifyBy`, `c0`.`ModifyTime`, `c0`.`RowVersion`
FROM `Customer` AS `c`
LEFT JOIN `CustomerFinance` AS `c0` ON `c`.`Id` = `c0`.`Id`
WHERE `c`.`Id` > 1
ORDER BY `c`.`Id` DESC
LIMIT 1
AnyAsync
查询实体是否已经存在,调用Ef的原生方法。
方法签名
/// <summary>
/// 根据条件查询实体是否存在
/// </summary>
/// <param name="whereExpression">查询条件</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<bool> AnyAsync(Expression<Func<TEntity, bool>> whereExpression, bool writeDb = false, CancellationToken cancellationToken = default);
CountAsync
统计符合条件的实体数量,调用Ef的原生方法。
方法签名
/// <summary>
/// 统计符合条件的实体数量
/// </summary>
/// <param name="whereExpression">查询条件</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// param name="cancellationToken"><see cref="CancellationToken"/></param>
/// <returns></returns>
Task<int> CountAsync(Expression<Func<TEntity, bool>> whereExpression, bool writeDb = false, CancellationToken cancellationToken = default);
PagedAsync
分页方法,该方法只能处理单实体的分页。如果查询多个实体再分页,请使用Where,GetAll方法。
方法签名
/// <summary>
/// 分页方法
/// </summary>
/// <param name="pageNumber">第几页</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="whereExpression">查询条件</param>
/// <param name="orderByExpression">排序条件</param>
/// <param name="ascending">排序方式</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// param name="cancellationToken"><see cref="CancellationToken"/></param>
Task<IPagedModel<TEntity>> PagedAsync(int pageNumber, int pageSize, Expression<Func<TEntity, bool>> whereExpression, Expression<Func<TEntity, object>> orderByExpression, bool ascending = false, bool writeDb = false, CancellationToken cancellationToken = default);
Where,GetAll
这是两个万能查询方法,根据查询条件返回一个IQueryable。 GetAll()
=
Where(x=>true)
方法签名
/// <summary>
/// 根据条件查询,返回IQueryable<TEntity>
/// </summary>
/// <param name="expression">查询条件</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <param name="noTracking">是否开启跟踪,默认false,可选参数</param>
IQueryable<TEntity> Where(Expression<Func<TEntity, bool>> expression, bool writeDb = false, bool noTracking = true);
/// <summary>
/// 返回IQueryable<TEntity>
/// </summary>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <param name="noTracking">是否开启跟踪,默认false,可选参数</param>
IQueryable<TEntity> GetAll(bool writeDb = false, bool noTracking = true);
单元测试
//测试查询
[Fact]
public async Task TestWhereAndGetAll()
{
//返回集合
var customers = await _customerRsp.Where(x => x.Id > 1).ToListAsync();
Assert.NotEmpty(customers);
//返回单个
var customer = await _customerRsp.Where(x => x.Id > 1).OrderByDescending(x => x.Id).FirstOrDefaultAsync();
Assert.NotNull(customer);
//组合查询
//GetAll() = Where(x=>true)
var customerAll = _customerRsp.GetAll();
var custsLogs = _custLogsRsp.GetAll();
var logs = await customerAll.Join(custsLogs, c => c.Id, t => t.CustomerId, (c, t) => new
{
t.Id
,
t.CustomerId
,
t.Account
,
t.ChangedAmount
,
t.ChangingAmount
,
c.Realname
})
.Where(c => c.Id > 1)
.ToListAsync();
Assert.NotEmpty(logs);
}
生成的Sql
--EF生成的sql
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE `c`.`Id` > 1
SELECT `c`.`Id`, `c`.`Account`, `c`.`CreateBy`, `c`.`CreateTime`, `c`.`ModifyBy`, `c`.`ModifyTime`, `c`.`Nickname`, `c`.`Realname`
FROM `Customer` AS `c`
WHERE `c`.`Id` > 1
ORDER BY `c`.`Id` DESC
LIMIT 1
SELECT `c0`.`Id`, `c0`.`CustomerId`, `c0`.`Account`, `c0`.`ChangedAmount`, `c0`.`ChangingAmount`, `c`.`Realname`
FROM `Customer` AS `c`
INNER JOIN `CustomerTransactionLog` AS `c0` ON `c`.`Id` = `c0`.`CustomerId`
WHERE `c0`.`Id` > 1
QueryAsync
该方法集成了Dapper的QueryAsync,用于处理复杂查询,直接撸Sql。
方法签名
/// <summary>
/// Dapper查询
/// </summary>
/// <param name="sql">sql</param>
/// <param name="param">参数</param>
/// <param name="commandTimeout">commandTimeout</param>
/// <param name="commandType">commandType</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <returns></returns>
Task<IEnumerable<dynamic>> QueryAsync(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null, bool writeDb = false);
/// <summary>
/// Dapper查询
/// </summary>
/// <typeparam name="TResult"><see cref="TResult"/></typeparam>
/// <param name="sql">sql</param>
/// <param name="param">参数</param>
/// <param name="commandTimeout">commandTimeout</param>
/// <param name="commandType">commandType</param>
/// <param name="writeDb">是否读写库,默认false,可选参数</param>
/// <returns></returns>
Task<IEnumerable<TResult>> QueryAsync<TResult>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null, bool writeDb = false);
单元测试
[Fact]
public async Task TestQuery()
{
var sql = $@"SELECT `c0`.`Id`, `c0`.`CustomerId`, `c0`.`Account`, `c0`.`ChangedAmount`, `c0`.`ChangingAmount`, `c`.`Realname`
FROM `Customer` AS `c`
INNER JOIN `CustomerTransactionLog` AS `c0` ON `c`.`Id` = `c0`.`CustomerId`
WHERE `c0`.`Id` > @Id";
var logs = await _customerRsp.QueryAsync(sql, new { Id = 1 });
Assert.NotEmpty(logs);
}
生成的Sql
--sql
SELECT `c0`.`Id`, `c0`.`CustomerId`, `c0`.`Account`, `c0`.`ChangedAmount`, `c0`.`ChangingAmount`, `c`.`Realname`
FROM `Customer` AS `c`
INNER JOIN `CustomerTransactionLog` AS `c0` ON `c`.`Id` = `c0`.`CustomerId`
WHERE `c0`.`Id` > 1