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.AsyncQueryingEnumerable
1.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 1061.MoveNext(CancellationToken cancellationToken) in D:\a\1\s\Ix.NET\Source\System.Interactive.Async\AsyncIterator.cs:line 98 at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor
at System.Linq.AsyncEnumerable.AsyncIterator1.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?