ef自带的方法是没有执行原生sql的，必须在仓储实现类中注入dbContextProvider、然后就可以使用FromSqlRaw查询，ExecuteSqlRawAsync执行新增、修改、删除，efcore方法都是自带事务的，有时候我们想在自己的业务逻辑中嵌入事务，就使用CreateExecutionStrategy创建事务事务操作：
public class AccessRecordRepository : EfCoreRepository<TestDbContext, AccessRecord, Guid>, IAccessRecordRepository
{
private readonly ILogger<AccessRecordRepository> _logger;
private readonly IDbContextProvider<TestDbContext> _dbContextProvider;
public AccessRecordRepository(IDbContextProvider<TestDbContext> dbContextProvider, ILogger<AccessRecordRepository> logger) : base(dbContextProvider)
{
_logger = logger;
_dbContextProvider = dbContextProvider;
}
/// <summary>
/// join关联查询
/// </summary>
/// <returns></returns>
public async Task GetList()
{
string ZLZT = "状态";
try
{
var dbContext = _dbContextProvider.GetDbContext(); // 建议不要调用using (var dbContext = _dbContextProvider.GetDbContext())释放，如果后面有调用GetQueryableAsync，会出现链接被释放错误
var list = dbContext.DataDictionaryItems.FromSqlRaw("select A.ZidianLBID, A.Bianma, A.Mingcheng from dataDictionaryitem A " +
"join dataDictionary B ON A.ZidianLBID = B.Id " +
$"where ZidianLBMC = '{ZLZT}'") // ef已经考虑了sql注入，因此不需要new MySqlParameter("@ZLZT", "状态")，注意字符值必须添加 ''
.Select(w => new TDataDto { Bianma = w.Bianma, Mingcheng = w.Mingcheng, ZidianLBID = w.ZidianLBID }) // 定义新类接受join查询结果，不然只能select * ； TDataDto必须添加属性 [Serializable]
.ToList();
_logger.LogInformation($"查询字典结果：{JsonConvert.SerializeObject(list)}");
}
catch (Exception ex)
{
_logger.LogInformation($"查询字典异常: {ex}");
}
}
/// <summary>
/// 执行SQL
/// </summary>
/// <returns></returns>
[Obsolete]
public async Task RemoveAllAsync()
{
_logger.LogInformation($"RemoveAllAsync start");
using (var dbContext = _dbContextProvider.GetDbContext())
{
try
{
int result = await dbContext.Database.ExecuteSqlRawAsync("TRUNCATE TABLE access_record");
_logger.LogInformation($"RemoveAllAsync result = {result}"); // TRUNCATE成功影响行数0
}
catch (Exception ex)
{
_logger.LogError($"RemoveAllAsync exception: {ex}");
}
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <returns></returns>
private async Task TestTran()
{
using (var dbContext = _dbContextProvider.GetDbContext())
{
var strategy = dbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async() =>
{
using (var tran = dbContext.Database.BeginTransaction())
{
try
{
var result = await dbContext.AccessRecord.AddAsync(new AccessRecord { });
await dbContext.SaveChangesAsync();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
}
});
}
}
/// <summary>
/// 根据条件删除
/// </summary>
/// <returns></returns>
public async Task<List<MyEntity>> RemoveEntitiesAsync(string name)
{
using (var dbContext = _dbContextProvider.GetDbContext())
{
var entities = await dbContext.Set<MyEntity>()
.Where(e => e.Name.Contains(name))
.ToListAsync();
// 也可以修改状态删除
// foreach (var entity in entities)
// {
// dbContext.Entry(entity).State = EntityState.Deleted;
// }
dbContext.RemoveRange(entities);
await dbContext.SaveChangesAsync();
return entities;
}
}
}
另外说一个常见的错误：Cannot access a disposed context instance ， 特别是在你写一些定时器、TCP服务，继承BackgroudService方法中，很容易出现，网上说在方法上面加[UnitOfWork]无效，也有人说仓储实现类继承ITransientDependency，仓储默认使用IScopeDependency，这个也无效，最终只有2个方案：
1、仓储操作的方法都改成同步的
2、使用IServiceProvider获取仓储
private readonly IServiceProvider _serviceProvider;
public AccessRecord(IHost host)
{
_serviceProvider = host.Services;
}
public async Task GetList()
{
using var serviceProvider = _serviceProvider.CreateScope();
var _accessRecordRepository = serviceProvider.ServiceProvider.GetRequiredService<IAccessRecordRepository>
// 类似的，获取ObjectMapper对象，也需要使用IServiceProvider
// _= Task.Run(async () =>
// {
// using var serviceProvider = _serviceProvider.CreateScope();
// var _objectMapper = serviceProvider.ServiceProvider.GetRequiredService<IObjectMapper>
// }).ContinueWith(async t => { await CallBackActionAsync(keyid, items); });
}