Casting error on EF Core when calling stored proc on MySQL (using Pomelo)

Question

I have a problem in a .Net Core 2.2 Web API using EF Core. I am calling a stored procedure on a MySQL database (I'm using Pomelo 2.1.4).

It's complaining about not being able to convert from DbNull to 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
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)

The problem line is:

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

the FileActivity model is:

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

And finally, the mapping in the DbContext is:

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

I've been wrecking my brain all day to try and figure this one out... Any ideas?

Answer 1

The problem is that those properties that are not required in modelBuilder must be defined nullable in your entities and models with ?. Like FileId and it is because if there is a record in the table with a null value for those fields then it will return null in your entity class but your entity class can not accept a null value for those not nullable properties and then it will throw an error.

Answer 2

As Ivan mentioned in the comment, the "description" and "file_num" fields were required (IsRequired = true). Because some of the rows had DbNulls, it caused an exception. I removed that restriction and all was fine.