在ABP项目中做执行SQL语句的实现时,遇到如下错误:
ERROR 2020-09-26 11:16:16,098 [7 ] oryRepositories.xxxRepository - SELECT * FROM xxx Error.
System.ArgumentNullException: 值不能为 null。
参数名: unitOfWork
在 Niue.Abp.Abp.EntityFramework.EntityFramework.Uow.UnitOfWorkExtensions.GetDbContext[TDbContext](IActiveUnitOfWork unitOfWork, Nullable1 multiTenancySide) 位置 C:\xxx\Abp.EntityFramework\EntityFramework\Uow\UnitOfWorkExtensions.cs:行号 33 在 Niue.Abp.Abp.EntityFramework.EntityFramework.Uow.UnitOfWorkDbContextProvider
1.GetDbContext(Nullable1 multiTenancySide) 位置 C:\xxx\Abp.EntityFramework\EntityFramework\Uow\UnitOfWorkDbContextProvider.cs:行号 33 在 Niue.Abp.Abp.EntityFramework.EntityFramework.Uow.UnitOfWorkDbContextProvider
1.GetDbContext() 位置 C:\xxx\Abp.EntityFramework\EntityFramework\Uow\UnitOfWorkDbContextProvider.cs:行号 28
在 Niue.EntityFramework.EntityFramework.Repositories.SqlRepositories.SqlRepository.get_Context() 位置 C:\xxx\EntityFramework\Repositories\SqlRepositories\SqlRepository.cs:行号 25
在 Niue.EntityFramework.EntityFramework.Repositories.SqlRepositories.SqlRepository.GetDbContext() 位置 C:\xxx\EntityFramework\Repositories\SqlRepositories\SqlRepository.cs:行号 29
在 Niue.EntityFramework.EntityFramework.Repositories.SqlRepositories.SqlRepository.ExecuteGenericsList[T](String sql) 位置 C:\xxx\EntityFramework\Repositories\SqlRepositories\SqlRepository.cs:行号 39
在 Niue.EntityFramework.EntityFramework.Repositories.xxxRepository.FindByOuterId(Int32 masterId, String outerId) 位置 C:\xxx\EntityFramework\Repositories\xxxRepository.cs:行号 156
解决方法:在方法前加上virtual就行了。
[UnitOfWork]
public virtual List<T> ExecuteGenericsList<T>(string sql) where T : class, new()
{
var db = _dbContextProvider.GetDbContext().Database;
var query = db.SqlQuery<T>(sql);
return query.ToList();
}
下面贴上SqlRepository代码。
public interface ISqlRepository<TDbContext> : ITransientDependency
{
/// <summary>
/// 执行命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <returns>执行命令后由数据库返回的结果</returns>
int Execute(string sql);
/// <summary>
/// 执行泛型列表查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">命令字符串</param>
/// <returns>泛型列表</returns>
List<T> ExecuteGenericsList<T>(string sql) where T : class, new();
T ExecuteScalar<T>(string sql);
/// <summary>
/// 执行命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <returns>执行命令后由数据库返回的结果</returns>
Task<int> ExecuteAsync(string sql);
/// <summary>
/// 执行泛型列表查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">命令字符串</param>
/// <returns>泛型列表</returns>
Task<List<T>> ExecuteGenericsListAsync<T>(string sql) where T : class, new();
Task<List<T>> ExecuteGenericsPageListAsync<T, TPagination>(TPagination pagination) where T : class, new() where TPagination : SqlPagination;
Task<T> ExecuteScalarAsync<T>(string sql);
}
public class SqlRepository<TDbContext> : ISqlRepository<TDbContext>, IRepositoryWithDbContext
where TDbContext : DbContext
{
private readonly IDbContextProvider<TDbContext> _dbContextProvider;
public SqlRepository(IDbContextProvider<TDbContext> dbContextProvider)
{
_dbContextProvider = dbContextProvider;
}
/// <summary>
/// Gets EF DbContext object.
/// </summary>
public virtual TDbContext Context { get { return _dbContextProvider.GetDbContext(); } }
public virtual DbContext GetDbContext()
{
return Context;
}
[UnitOfWork]
public virtual int Execute(string sql)
{
return _dbContextProvider.GetDbContext().Database.ExecuteSqlCommand(sql);
}
[UnitOfWork]
public virtual List<T> ExecuteGenericsList<T>(string sql) where T : class, new()
{
var db = _dbContextProvider.GetDbContext().Database;
var query = db.SqlQuery<T>(sql);
return query.ToList();
}
[UnitOfWork]
public virtual T ExecuteScalar<T>(string sql)
{
var db = _dbContextProvider.GetDbContext().Database;
var query = db.SqlQuery<T>(sql);
return query.FirstOrDefault();
}
[UnitOfWork]
public virtual async Task<int> ExecuteAsync(string sql)
{
return await _dbContextProvider.GetDbContext().Database.ExecuteSqlCommandAsync(sql);
}
[UnitOfWork]
public virtual async Task<List<T>> ExecuteGenericsListAsync<T>(string sql) where T : class, new()
{
var db = _dbContextProvider.GetDbContext().Database;
var query = db.SqlQuery<T>(sql);
return await query.ToListAsync();
}
[UnitOfWork]
public virtual async Task<List<T>> ExecuteGenericsPageListAsync<T, TPagination>(TPagination pagination) where T : class, new() where TPagination : SqlPagination
{
if (string.IsNullOrWhiteSpace(pagination.Sql))
{
return new List<T>();
}
var db = _dbContextProvider.GetDbContext().Database;
StringBuilder sb = new StringBuilder();
var versonQuery = db.SqlQuery<int>("SELECT CASE WHEN SERVERPROPERTY('productversion') > '11' THEN 1 ELSE 0 END");
var verson = await versonQuery.FirstOrDefaultAsync();
if (verson > 0)
{
//SQL SERVER 2012 以上版本
if (pagination.Sql.TrimStart().ToUpper().StartsWith("WITH"))
{
Match match = Regex.Match(pagination.Sql, @"(W|w)(I|i)(T|t)(H|h)(\S|\s)*(A|a)(S|s)(\s)*(\S|\s)*\)(\s)*SELECT");
int index = (pagination.PageNo - 1) * pagination.PageSize; //第一条数据的索引
sb.Append(match.Value.Substring(0, match.Value.Length - 6));
sb.Append(", pagelist AS ( ");
sb.Append(" " + pagination.Sql.Replace(match.Value, "SELECT") + " ");
sb.Append(") ");
sb.Append("SELECT * FROM pagelist ORDER BY " + pagination.Order + " OFFSET " + index + " ROWS FETCH NEXT " + pagination.PageSize + " ROWS ONLY ");
}
else
{
int index = (pagination.PageNo - 1) * pagination.PageSize; //第一条数据的索引
sb.Append("WITH pagelist AS ( ");
sb.Append(" " + pagination.Sql + " ");
sb.Append(") ");
sb.Append("SELECT * FROM pagelist ORDER BY " + pagination.Order + " OFFSET " + index + " ROWS FETCH NEXT " + pagination.PageSize + " ROWS ONLY ");
}
}
else
{
if (pagination.Sql.TrimStart().ToUpper().StartsWith("WITH"))
{
Match match = Regex.Match(pagination.Sql, @"(W|w)(I|i)(T|t)(H|h)(\S|\s)*(A|a)(S|s)(\s)*(\S|\s)*\)(\s)*SELECT");
int index = (pagination.PageNo - 1) * pagination.PageSize + 1; //第一条数据的索引
sb.Append(match.Value.Substring(0, match.Value.Length - 6));
sb.Append(", pagelist AS ( ");
sb.Append(" " + pagination.Sql.Replace(match.Value, "SELECT") + " ");
sb.Append("), ROW_NUMBER_T AS ( ");
sb.Append(" SELECT *,ROW_NUMBER() OVER(ORDER BY " + pagination.Order + ") AS ROW_NUMBER_ID FROM pagelist");
sb.Append(") ");
sb.Append("SELECT * FROM ROW_NUMBER_T WHERE ROW_NUMBER_ID BETWEEN " + index + " AND " + pagination.PageSize + " ");
}
else
{
int index = (pagination.PageNo - 1) * pagination.PageSize + 1; //第一条数据的索引
sb.Append("WITH pagelist AS ( ");
sb.Append(" " + pagination.Sql + " ");
sb.Append("), ROW_NUMBER_T AS ( ");
sb.Append(" SELECT *,ROW_NUMBER() OVER(ORDER BY " + pagination.Order + ") AS ROW_NUMBER_ID FROM pagelist");
sb.Append(") ");
sb.Append("SELECT * FROM ROW_NUMBER_T WHERE ROW_NUMBER_ID BETWEEN " + index + " AND " + pagination.PageSize + " ");
}
}
var query = db.SqlQuery<T>(sb.ToString());
return await query.ToListAsync();
}
[UnitOfWork]
public virtual async Task<T> ExecuteScalarAsync<T>(string sql)
{
var db = _dbContextProvider.GetDbContext().Database;
var query = db.SqlQuery<T>(sql);
return await query.FirstOrDefaultAsync();
}
}