C# abp中EfCore执行原生sql和事务操作

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); });

  }