.net-core - 在 MySQL 上调用存储过程时在 EF Core 上发生转换错误(使用 Pomelo)

Question

我在使用 EF Core 的 .Net Core 2.2 Web API 中遇到问题。我在 MySQL 数据库上调用存储过程(我使用的是 Pomelo 2.1.4)。

它提示无法从 DbNull 转换为 String:

Error: Unable to cast object of type 'System.DBNull' to type 'System.String'. at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 ordinal) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 210 at lambda_method(Closure , DbDataReader ) at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.BufferlessMoveNext(DbContext
_, Boolean buffer, CancellationToken cancellationToken) at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState
state, Func4 operation, Func4 verifySucceeded, CancellationToken
cancellationToken) at
Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken) at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator2.MoveNextCore(CancellationToken
cancellationToken) in
D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106<br/>
at
System.Linq.AsyncEnumerable.AsyncIterator1.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98 at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken
cancellationToken) at
System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable1 source, TAccumulate seed, Func3 accumulator, Func2 resultSelector, CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Aggregate.cs:line 120 at PropWorx.API.Controllers.FileActivitiesController.GetFileActivities(Int32 fileId, String fromDate, String toDate) in C:\Users\fabsr\source\repos\PropWorx.API\PropWorx.API\Controllers\FileActivitiesController.cs:line 101 at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at System.Threading.Tasks.ValueTask`1.get_Result() 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.InvokeNextResourceFilter() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context) at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync() at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext) at PropWorx.API.Middlewares.TenantIdentifier.Invoke(HttpContext httpContext, SharedContext sharedContext) in C:\Users\fabsr\source\repos\PropWorx.API\PropWorx.API\Middlewares\TenantIdentifier.cs:line 73 at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.Invoke(HttpContext context)

问题行是:

List<FileActivity> fileActivities = await _context.FileActivities.FromSql("CALL file_activity_procedure(@fromDate, @toDate, @fileId)", param1, param2, param3).ToListAsync();

FileActivity 模型是:

public class FileActivity
{
    public int Id { get; set; }
    public DateTime? Date { get; set; }
    public int FileId { get; set; }
    public string FileNumber { get; set; }
    public string Area { get; set; }
    public int? RecordId { get; set; }
    public string Description { get; set; }
    public string Type { get; set; }
    public string TypeInfo { get; set; }
    public decimal? Debit { get; set; }
    public decimal? Credit { get; set; }
    public string AddedBy { get; set; }
    public DateTime? AddedDate { get; set; }
    public string Comments { get; set; }
}

最后，DbContext 中的映射是:

modelBuilder.Entity<FileActivity>(entity =>
{
    entity.Property(e => e.Id)
        .HasColumnName("ID")
        .HasColumnType("int(11)");

    entity.Property(e => e.Date)
        .HasColumnName("Date")
        .HasColumnType("datetime");

    entity.Property(e => e.FileId)
        .HasColumnName("file_id")
        .HasColumnType("int(11)");

    entity.Property(e => e.FileNumber)
        .IsRequired()
        .HasColumnName("file_num")
        .HasColumnType("varchar(50)");

    entity.Property(e => e.Description)
        .IsRequired()
        .HasColumnName("description")
        .HasColumnType("varchar(255)");

    entity.Property(e => e.Type)
        .HasColumnName("type")
        .HasColumnType("varchar(255)");

    entity.Property(e => e.TypeInfo)
        .HasColumnName("type_info")
        .HasColumnType("varchar(255)");

    entity.Property(e => e.Debit)
       .HasColumnName("Debit")
       .HasColumnType("decimal(13,4)")
       .HasDefaultValueSql("'0.0000'");

    entity.Property(e => e.Credit)
       .HasColumnName("Credit")
       .HasColumnType("decimal(13,4)")
       .HasDefaultValueSql("'0.0000'");

    entity.Property(e => e.RecordId)
       .HasColumnName("record_id")
       .HasColumnType("int(11)");

    entity.Property(e => e.AddedBy)
        .HasColumnName("Added_By")
        .HasColumnType("varchar(45)");

    entity.Property(e => e.AddedDate)
        .HasColumnName("added_date")
        .HasColumnType("datetime");

    entity.Property(e => e.Comments)
        .HasColumnName("comment")
        .HasColumnType("text");
});

我整天都在绞尽脑汁想弄明白这个问题……有什么想法吗？

Answer 1

正如 Ivan 在评论中提到的，“description”和“file_num”字段是必需的 (IsRequired = true)。因为某些行有 DbNulls，所以导致了异常。我删除了该限制，一切都很好。