EFCore 2.2 报错 Data is Null. This method or property cannot be called on Null values
最近在开发的过程中遇到了一个问题，在使用EFCore2.2 版本进行开发的时候，调试的时候一直报Data is Null. This method or property cannot be called on Null values这个错误，凭自己的直觉又是哪个空类型的转换出了错误，但是问题到底该怎么找呢？而且整个代码的逻辑还不算简单，怎么一步步找到问题并解决问题呢？本文主要从问题的发现到问题的解决来一步步归纳到底该如何顺利找到这类问题的解决方案。
一 背景
在使用EFCore进行开发的时候有时候考虑到一些性能的问题，我们不得不使用一些能够直接执行纯SQL的框架，比如Dapper,我们的整个项目都是使用ABP框架作为主框架，所以ABP内部会集成Dapper并提供对外的接口供我们来使用，在理解本篇文章之前建议你先看一下这篇文章从而对后面的代码有一个整体的认知。
先看一下我们代码调用出错的位置及具体内容。
public PageWithTotal<GetPartStockOutput, GetPartStockTotalOutput> GetPartStocks(GetPartStockInput input, PageRequest pageRequest) {
var queryPartStock = QueryPartStock(input);
//服务站服务信息
var dealerBusinessPermit = _dealerBusinessPermitManager.GetDefaultBranDealerBusinessPermit(SdtSession.TenantId.GetValueOrDefault());
var result = _partStockRepository.GetPartStockWithPrice(queryPartStock, SdtSession.TenantId.GetValueOrDefault(), dealerBusinessPermit.UserDefinedPriceBaseType, dealerBusinessPermit.RetailGuidePriceFactor, dealerBusinessPermit.UserDefinedPriceFactor)
.WhereIf(!string.IsNullOrWhiteSpace(input.SupplierCode), r => r.SupplierCode.Contains(input.SupplierCode))
.WhereIf(!string.IsNullOrWhiteSpace(input.SupplierName), r => r.SupplierName.Contains(input.SupplierName));
var count = result.Count(s => _part.GetAll().Any(p => p.Id == s.PartId));
var pageResults = result.PageAndOrderBy(pageRequest).ToList();
var varietySum = result.Select(q => q.PartId).Distinct().Count();
var total = new GetPartStockTotalOutput();
if (count > 0) {
total = result.GroupBy(g => 1).Select(g => new GetPartStockTotalOutput {
StockQuantity = g.Sum(r => r.StockQuantity),
TaxFreePrice = g.Sum(r => r.TaxFreeFeeSum),
StockPrice = g.Sum(r => r.StockCost),
BorrowAndNotReturnPrice = g.Sum(r => r.BorrowAndNotReturnPrice),
BorrowAndNotReturn = g.Sum(r => r.BorrowAndNotReturn)
}).First();
total.VarietySum = varietySum;
total.CostPrice = total.StockPrice + (total.BorrowAndNotReturnPrice ?? 0);
total.Tax = total.CostPrice - total.TaxFreePrice;
total.TotalQuantity = total.StockQuantity + (total.BorrowAndNotReturn ?? 0);
}
var queryResults = ObjectMapper.Map<List<GetPartStockOutput>>(pageResults);
return new PageWithTotal<GetPartStockOutput, GetPartStockTotalOutput>(pageRequest, count, queryResults, total);
}
这里面最核心的一个方法就是GetPartStockWithPrice这个，这个部分是通过ABP中集成的Dapper来实现的，这个里面调用了一个重要的子函数QueryPartStock，这个函数主要是将前端传入查询条件的参数转换成后端对应的EFCore代码，这里我也贴出具体点的代码。
private IQueryable<PartStock> QueryPartStock(GetPartStockInput input) {
var company = _companyManager.GetValidCompany(SdtSession.TenantId);
return _partStockRepository.GetAll()
.WhereIf(company.Type == CompanyCategory.服务站, r => r.DealerId == SdtSession.TenantId)
.WhereIf(input.WarehouseId.HasValue, r => r.WarehouseId == input.WarehouseId)
.WhereIf(!string.IsNullOrWhiteSpace(input.BinCode), r => r.BinCode.Contains(input.BinCode))
.WhereIf(input.PartCodes?.Count == 1, r => r.PartCode.Contains(input.PartCodes[0]))
.WhereIf(input.PartCodes?.Count > 1, r => input.PartCodes.Contains(r.PartCode))
.WhereIf(!string.IsNullOrWhiteSpace(input.PartCode), r => r.PartCode.Contains(input.PartCode))
.WhereIf(!string.IsNullOrWhiteSpace(input.PartName), r => r.PartName.Contains(input.PartName))
.WhereIf(input.ZeroStock.HasValue, r => input.ZeroStock.Value ? r.StockQuantity == 0 : r.StockQuantity != 0)
.WhereIf(input.Unassigned, r => r.BinId == null)
.WhereIf(input.BrandId.HasValue, r => _partPrice.GetAll().Any(ps => ps.PartId == r.PartId && ps.BrandId == input.BrandId))
.WhereIf(input.IsExternalPart.HasValue, r => _part.GetAll().Any(p => p.Id == r.PartId && p.IsExternalPart == input.IsExternalPart))
.WhereIf(input.PartProperty?.Length > 0, s => _partSalesProperty.GetAll()
.Where(p => p.PartProperty != PartProperty.套餐 && input.PartProperty.Contains(p.PartProperty.Value)).Any(p => p.PartId == s.PartId))
.WhereIf(input.BeginLastInTime.HasValue, ps => ps.LastInTime >= input.BeginLastInTime)
.WhereIf(input.EndLastInTime.HasValue, ps => ps.LastInTime <= input.EndLastInTime)
.WhereIf(input.BeginLastOutTime.HasValue, ps => ps.LastOutTime >= input.BeginLastOutTime)
.WhereIf(input.EndLastOutTime.HasValue, ps => ps.LastOutTime <= input.EndLastOutTime);
}
这段函数最终返回一个IQueryable<PartStock>的对象信息，其中PartStock是一个Entity和数据库中唯一的实体对应，_partStockRepository对应的是PartStock具体的仓储，在ABP框架中我们一般是通过构造函数注入IRepository<PartStock, Guid> 来实现对整个仓储类实现的，这里我们着重看一下第一个函数内部GetPartStockWithPrice的具体实现，我们先来看一下这个函数的定义。
using System;
using System.Linq;
using Abp.Domain.Repositories;
using Sunlight.Dms.Parts.Domain.PartStocks.Models;
namespace Sunlight.Dms.Parts.Domain.PartStocks {
public interface IPartStockRepository : IRepository<PartStock, Guid> {
/// <summary>
/// 查询备件库存信息
/// </summary>
/// <typeparam name="T"></typeam name="query"></param>
/// <param name="dealerId">当前登录服务站信息</param>
/// <param name="userDefinedPriceBaseType">备件自定义价格基准价类型</param>
/// <param name="retailGuidePriceFactor">备件零售价格系数</param>
/// <param name="userDefinedPriceFactor">自定义价格系数</param>
/// <returns></returns>
IQueryable<PartStockWithPriceModel> GetPartStockWithPrice<T>(IQueryable<T> query, Guid dealerId,
UserDefinedPriceBaseType userDefinedPriceBaseType, decimal retailGuidePriceFactor, decimal userDefinedPriceFactor);
}
}
这个方法是定义在一个叫做继承自IRepository<PartStock, Guid>的仓储类中的，返回的结构是一个IQueryable<PartStockWithPriceModel>类型的对象，这里我们看一下PartStockWithPriceModel这个对象，这里为了分析方便只截取了这个实体中一部分代码，我们也贴出这部分的内容，另外PartStockWithPriceModel需要定义在当前的DbContext下面，并且定义成下面的形式 public DbQuery<PartStockWithPriceModel> PartStockWithPriceModels { get; set; },这里DbQuery会映射为数据库视图类型。
using System;
using Sunlight.Abstractions;
namespace Sunlight.Dms.Parts.Domain.PartStocks.Models {
public class PartStockWithPriceModel : IRowVersion<byte[]> {
public decimal StockQuantity { get; set; }
/// <summary>
/// 批发价
/// </summary>
public decimal? WholeSalePrice { get; set; }
/// <summary>
/// 零售指导价
/// </summary>
public decimal? RetailGuidePrice { get; set; }
/// <summary>
/// 是否外采件
/// </summary>
public bool IsExternalPart { get; set; }
/// <summary>
/// 库存金额
/// </summary>
public decimal? StockCost { get; set; }
/// <summary>
/// 成本单价
/// </summary>
public decimal? CostPrice { get; set; }
public bool? IsDecimalAllowed { get; set; }
/// <summary>
/// 借出未还数量(取值：备件借用单.状态 = 生效/部分归还，取其清单.借用数量-已归还数量)
/// </summary>
public decimal? BorrowAndNotReturn { get; set; }
/// <summary>
/// 备件自定义名称
/// </summary>
public string UserDefinedPartName { get; set; }
/// <summary>
/// 本店零售价
/// </summary>
public decimal? UserDefinedRetailGuidePrice { get; set; }
/// <summary>
/// 出库价
/// </summary>
public decimal UserDefinedPartPrice { get; set; }
public decimal? TaxFreePrice { get; set; }
/// <summary>
/// 不含税成本金额
/// </summary>
public decimal? TaxFreeFeeSum { get; set; }
/// <summary>
/// 最近采购供应商编号
/// </summary>
public string SupplierCode { get; set; }
/// <summary>
/// 最近采购供应商名称
/// </summary>
public string SupplierName { get; set; }
/// <summary>
/// 借出未还金额
/// </summary>
public decimal? BorrowAndNotReturnPrice { get; set; }
/// <summary>
/// 备件库存RowVersion
/// </summary>
public byte[] RowVersion { get; set; }
}
}
然后我们来看看这个方法的具体实现，后面我们将会通过后面的具体代码来进行分析。
using System;
using System.Linq;
using Abp.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Sunlight.Dms.Parts.Domain;
using Sunlight.Dms.Parts.Domain.PartStocks;
using Sunlight.Dms.Parts.Domain.PartStocks.Models;
using Sunlight.EFCore.Extensions;
namespace Sunlight.Dms.Parts.Data.EntityFrameworkCore.Repository {
public class PartStockRepository : PartsRepositoryBase<PartStock, Guid>, IPartStockRepository {
public PartStockRepository(IDbContextProvider<PartsDbContext> dbContextProvider) : base(dbContextProvider) {
}
/// <summary>
/// 查询备件库存信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="dealerId">当前登录服务站信息</param>
/// <param name="userDefinedPriceBaseType">备件自定义价格基准价类型</param>
/// <param name="retailGuidePriceFactor">备件零售价格系数</param>
/// <param name="userDefinedPriceFactor">自定义价格系数</param>
/// <returns></returns>
public IQueryable<PartStockWithPriceModel> GetPartStockWithPrice<T>(IQueryable<T> query, Guid dealerId,
UserDefinedPriceBaseType userDefinedPriceBaseType, decimal retailGuidePriceFactor, decimal userDefinedPriceFactor) {
var stockSql = query.ToSql(Context);
var querySql = $@"SELECT [borrow].[BorrowAndNotReturn],
[borrow].[BorrowAndNotReturn] * [psc].[CostPrice] [BorrowAndNotReturnPrice],
[partStock].*,
ISNULL([psc].[CostPrice], 0) * [partStock].[StockQuantity] AS [StockCost],
[psc].[CostPrice],
[partPrice].[WholeSalePrice],
[pcp].[UserDefinedRetailGuidePrice],
case when [part].[IsExternalPart] = 1
then COALESCE([partPrice].[RetailGuidePrice], 0.0)
else
ROUND(COALESCE([partPrice].[RetailGuidePrice], 0.0) * {retailGuidePriceFactor} ,0)
end AS [RetailGuidePrice],
--定义出库价
case when [part].[IsExternalPart] = 1
then COALESCE([partPrice].[RetailGuidePrice], 0.0)
else
case when [pcp].[UserDefinedRetailGuidePrice] is not null
then [pcp].[UserDefinedRetailGuidePrice]
else
case when {(int)userDefinedPriceBaseType} =1
then ROUND(COALESCE([partPrice].[WholeSalePrice], 0.0) * {retailGuidePriceFactor} * {userDefinedPriceFactor},0)
when {(int)userDefinedPriceBaseType} =2
then ROUND(COALESCE([partPrice].[RetailGuidePrice], 0.0) * {retailGuidePriceFactor} * {userDefinedPriceFactor},0)
end
end
end AS [UserDefinedPartPrice],
--定义出库价来源
case when [part].[IsExternalPart] = 1
then 3
else
case when [pcp].[UserDefinedRetailGuidePrice] is not null
then 1
else 2
end
end AS [OutPriceSourceType],
[psc].[TaxFreePrice],
ISNULL([psc].[TaxFreePrice], 0) * [partStock].[StockQuantity] + ISNULL([outSum].[Fee], 0.0) - ISNULL([inSum].[Fee], 0.0) AS [TaxFreeFeeSum],
[psc].[Id] AS [PartStockCostId],
[psc].[SupplierCode],
[psc].[SupplierName],
[part].[IsDecimalAllowed],
[part].[IsExternalPart]
FROM
({stockSql}) [partStock]
LEFT JOIN (
SELECT [p].*
FROM [PartStockCost] AS [p]
WHERE [p].[DealerId] = '{dealerId}'
) AS [psc] ON [partStock].[PartId] = [psc].[PartId]
LEFT JOIN [PartPrice] AS [partPrice] ON [partStock].[PartId] = [partPrice].[PartId]
LEFT JOIN [PartCustomerProperty] AS [pcp] ON [partStock].[PartId] = [pcp].[PartId]
AND [partStock].DealerId = [pcp].[DealerId]
INNER JOIN [Part] AS [part] ON [partStock].[PartId] = [part].[Id]
LEFT JOIN (
SELECT [partBorrowDetail].[PartId], [t0].[BorrowWarehouseId] AS [WarehouseId],
SUM([partBorrowDetail].[BorrowQuantity] - COALESCE([partBorrowDetail].[ReturnedQuantity], 0.0)) AS [BorrowAndNotReturn]
FROM [PartBorrowDetail] AS [partBorrowDetail]
INNER JOIN (
SELECT [b].*
FROM [PartBorrowOrder] AS [b]
WHERE [b].[Status] IN ({(int)PartBorrowStatus.生效}, {(int)PartBorrowStatus.部分归还}) AND ([b].[DealerId] = '{dealerId}')
) AS [t0] ON [partBorrowDetail].[PartBorrowOrderId] = [t0].[Id]
GROUP BY [partBorrowDetail].[PartId], [t0].[BorrowWarehouseId]
) AS [borrow] ON ([partStock].[WarehouseId] = [borrow].[WarehouseId]) AND ([partStock].[PartId] = [borrow].[PartId])
LEFT JOIN (SELECT WarehouseId, PartId, sum(TaxFreeFeeSum) Fee
FROM PartOut
INNER JOIN PartOutDetail ON PartOut.Id = PartOutDetail.PartOutId
WHERE OutType = {(int)PartOutType.借用出库}
GROUP BY WarehouseId, PartId
) outSum ON ([partStock].[WarehouseId] = [outSum].[WarehouseId]) AND ([partStock].[PartId] = [outSum].[PartId])
LEFT JOIN (SELECT WarehouseId, PartId, sum(TaxFreeFeeSum) Fee
FROM PartIn
INNER JOIN PartInDetail ON PartIn.Id = PartInDetail.PartInId
WHERE InType = {(int)PartInType.借用归还入库}
GROUP BY WarehouseId, PartId
) inSum ON ([partStock].[WarehouseId] = [inSum].[WarehouseId]) AND ([partStock].[PartId] = [inSum].[PartId])
WHERE [partStock].[DealerId] = '{dealerId}'
";
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
return Context.PartStockWithPriceModels.FromSql(querySql);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
}
}
}
写到这里我们要重点讲述一下 var stockSql = query.ToSql(Context);这部分代码，在我们使用EFCore的时候不可避免使用混合方式进行编程，有了这个ToSql方法我们就很容易将EFCore中的IQueryable类型查询代码转换为SQL语句最终拼接到最终的查询语句中，这样能够在一定程度上简化代码，那么这个ToSql的方法到底是怎么实现的，我们来简单看看。
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using Sunlight.Domain.Models;
using Sunlight.EFCore.Repositories;
#if NETCOREAPP2_2
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
#else
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
#endif
namespace Sunlight.EFCore.Extensions {
/// <summary>
/// IQueryable类型的扩展方法
/// </summary>
public static class QueryableExtensions {
#if NETCOREAPP2_2
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://github.com/yangzhongke/ZackData.Net/blob/master/Tests.NetCore/IQueryableExtensions.cs" />
/// </summary>
/// <param name="query"></param>
/// <param name="dbCtx"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static string ToSql<TEntity>(this IQueryable<TEntity> query, DbContext dbCtx) {
var modelGenerator = dbCtx.GetService<IQueryModelGenerator>();
var queryModel = modelGenerator.ParseQuery(query.Expression);
var databaseDependencies = dbCtx.GetService<DatabaseDependencies>();
var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
#else
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3109335" />
/// </summary>
/// <param name="query"></param>
/// <param name="dbCtx">数据库上下文</param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static string ToSql<TEntity>(this IQueryable<TEntity> query, DbContext dbCtx = null) where TEntity : class {
return ToSql(query);
}
/// <summary>
/// 将查询语句转换成Sql, 便于进一步的Sql拼接
/// <seealso href="https://gist.github.com/rionmonster/2c59f449e67edf8cd6164e9fe66c545a#gistcomment-3109335" />
/// </summary>
/// <param name="query"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
private static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class {
using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var sql = command.CommandText;
return sql;
}
private static object Private(this object obj, string privateField) => obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
private static T Private<T>(this object obj, string privateField) => (T)obj?.GetType().GetField(privateField, BindingFlags.Instance | BindingFlags.NonPublic)?.GetValue(obj);
/// <summary>
/// 增加
/// </summary>
/// <param name="query"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static (string, IReadOnlyDictionary<string, object>) ToSqlWithParams<TEntity>(this IQueryable<TEntity> query) {
using var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.Private("_relationalCommandCache");
var selectExpression = relationalCommandCache.Private<SelectExpression>("_selectExpression");
var factory = relationalCommandCache.Private<IQuerySqlGeneratorFactory>("_querySqlGeneratorFactory");
var queryContext = enumerator.Private<RelationalQueryContext>("_relationalQueryContext");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var parametersDict = queryContext.ParameterValues;
var sql = command.CommandText;
return (sql, parametersDict);
}
#endif
}
}
到了这里整个过程的背景全部交代完了，通过上面的讲述你也可以加深对EFCore的理解，同时对整个混合方式查询访问数据库有了一个更加清晰的认识，这个里面有很多的知识点需要自己去好好消化。
二 解决过程
有了上面的代码，下面就是讲我们遇到的问题并且解决问题的过程，在这里我们在本地Swagger传入参数并点击查询的时候结果一直报错Data is Null. This method or property cannot be called on Null values，这里还看不出什么我们来看看具体的堆栈调用信息。
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values. at System.Data.SqlClient.SqlBuffer.get_Decimal() at System.Data.SqlClient.SqlDataReader.GetDecimal(Int32 i) at lambda_method(Closure , DbDataReader , DbContext ) at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider._FastQuery[TEntity](RelationalQueryContext relationalQueryContext, ShaperCommandContext shaperCommandContext, Func`3 materializer, Type contextType, IDiagnosticsLogger`1 logger)+MoveNext() at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at Sunlight.Dms.Parts.Application.PartStocks.PartStockService.GetPartStocks(GetPartStockInput input, PageRequest pageRequest) in D:\项目代码\Chery\Dms\parts-service\src\Sunlight.Dms.Parts.Application\PartStocks\PartStockService.cs:line 142 at Castle.Proxies.Invocations.IPartStockService_GetPartStocks.InvokeMethodOnTarget() at Castle.DynamicProxy.AbstractInvocation.Proceed() at Abp.Domain.Uow.UnitOfWorkInterceptor.PerformSyncUow(IInvocation invocation, UnitOfWorkOptions options) in D:\Github\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 68 at Castle.DynamicProxy.AbstractInvocation.Proceed() at Abp.Auditing.AuditingInterceptor.PerformSyncAuditing(IInvocation invocation, AuditInfo auditInfo) in D:\Github\aspnetboilerplate\src\Abp\Auditing\AuditingInterceptor.cs:line 59 at Castle.DynamicProxy.AbstractInvocation.Proceed() at Castle.DynamicProxy.AbstractInvocation.Proceed() at Castle.Proxies.PartStockServiceProxy.GetPartStocks(GetPartStockInput input, PageRequest pageRequest) at Sunlight.Dms.Parts.WebHost.Controllers.PartStockController.GetPartStock(GetPartStockInput input, PageRequest pageRequest) in D:\项目代码\Chery\Dms\parts-service\src\Sunlight.Dms.Parts.WebHost\Controllers\PartStockController.cs:line 42 at lambda_method(Closure , Object , Object[] ) at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters) at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextExceptionFilterAsync()
通过上面的堆栈调用信息我们发现内部是这个SqlDataReader.GetDecimal(Int32 i) 一直报错，看这个报错提示这个方法只能接受Int32类型的参数，可能在实际转换中出现了null值，当这个null值传入到这个方法内部的时候就会报这个错误，那么我们来看哪个decimal参数会导致这个问题呢？我们知道最终查询的所有数据都会映射成一个IQueryable<PartStockWithPriceModel>类型的对象，只有可能是这个过程中出现了问题，我们来把PartStockWithPriceModel这个实体里面所有的decimal类型的都找出来看看有什么收获，我们发现整个实体中只有StockQuantity和UserDefinedPartPrice两个属性是定义成了decimal类型的，其它的所有类型都是decimal？可为空的decimal类型，到了这里我们发现如果这两个字段最终从数据库查询到的结果是null,那么将其转换为decimal类型的时候肯定是有问题的，这个转换过程在内部应该就是调用的SqlDataReader.GetDecimal(Int32 i)这个方法，当值为null的时候就会报上面的错误，那么到底是不是这个原因呢？我们来验证一下。
我们把最终生成的SQL拿到数据库去查询发现了定义成decimal类型的两个字段，UsedDefinedPartPrice果然有很多的数据为null,这个最终转换成decimal类型的时候肯定是有问题的，那么我们的猜测到底是不是正确的呢？
图一 查询结果
三 最后验证
带着我们猜测的结果，我们把PartStockWithPriceModel里面的UsedDefinedPartPrice定义的类型修改为可为空的decimal类型，再次查询结果一切正确，至此所有问题完美解决，由上面的整个过程你应该对整个过程怎么分析问题最终找到问题的过程有一个深入的理解了，所以这里面我们可以吸取的教训就是数据库中查不到数据并返回null是一个大概率事件，所以很多时候我们才需要使用ISNULL或者是COALESCE函数来规避各种为空的问题，另外我们在实体中定义值类型的时候如果这个值要和数据库中的字段进行映射，如果可以的话最好定义成可为空的类型，这些也算是从上面的例子中得到的一点点经验吧。
浙公网安备 33010602011771号