title: Casting error on EF Core when calling stored proc on MySQL (using Pomelo)
body: <p>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).</p>
  
  <p>It's complaining about not being able to convert from DbNull to String:</p>
  
  <blockquote>
    <p><b>Error: Unable to cast object of type 'System.DBNull' to type
    'System.String'.</b>   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<code>1.AsyncEnumerator.BufferlessMoveNext(DbContext
    _, Boolean buffer, CancellationToken cancellationToken)    at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState
    state, Func</code>4 operation, Func<code>4 verifySucceeded, CancellationToken
    cancellationToken)    at
    Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable</code>1.AsyncEnumerator.MoveNext(CancellationToken
    cancellationToken)    at
    System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator<code>2.MoveNextCore(CancellationToken
    cancellationToken) in
    D:\a\1\s\Ix.NET\Source\System.Interactive.Async\Select.cs:line 106<br>
    at
    System.Linq.AsyncEnumerable.AsyncIterator</code>1.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<code>1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken
    cancellationToken)    at
    System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable</code>1
    source, TAccumulate seed, Func<code>3 accumulator, Func</code>2 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&amp;
    next, Scope&amp; scope, Object&amp; state, Boolean&amp; 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&amp; next,
    Scope&amp; scope, Object&amp; state, Boolean&amp; isCompleted)    at
    Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
    at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()<br>
    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)</p>
  </blockquote>
  
  <p>The problem line is:</p>
  
  <pre><code>List&lt;FileActivity&gt; fileActivities = await _context.FileActivities.FromSql("CALL file_activity_procedure(@fromDate, @toDate, @fileId)", param1, param2, param3).ToListAsync();
  </code></pre>
  
  <p>the FileActivity model is:</p>
  
  <pre><code>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; }
  }
  </code></pre>
  
  <p>And finally, the mapping in the DbContext is:</p>
  
  <pre><code>modelBuilder.Entity&lt;FileActivity&gt;(entity =&gt;
  {
      entity.Property(e =&gt; e.Id)
          .HasColumnName("ID")
          .HasColumnType("int(11)");
  
      entity.Property(e =&gt; e.Date)
          .HasColumnName("Date")
          .HasColumnType("datetime");
  
      entity.Property(e =&gt; e.FileId)
          .HasColumnName("file_id")
          .HasColumnType("int(11)");
  
      entity.Property(e =&gt; e.FileNumber)
          .IsRequired()
          .HasColumnName("file_num")
          .HasColumnType("varchar(50)");
  
      entity.Property(e =&gt; e.Description)
          .IsRequired()
          .HasColumnName("description")
          .HasColumnType("varchar(255)");
  
      entity.Property(e =&gt; e.Type)
          .HasColumnName("type")
          .HasColumnType("varchar(255)");
  
      entity.Property(e =&gt; e.TypeInfo)
          .HasColumnName("type_info")
          .HasColumnType("varchar(255)");
  
      entity.Property(e =&gt; e.Debit)
         .HasColumnName("Debit")
         .HasColumnType("decimal(13,4)")
         .HasDefaultValueSql("'0.0000'");
  
      entity.Property(e =&gt; e.Credit)
         .HasColumnName("Credit")
         .HasColumnType("decimal(13,4)")
         .HasDefaultValueSql("'0.0000'");
  
      entity.Property(e =&gt; e.RecordId)
         .HasColumnName("record_id")
         .HasColumnType("int(11)");
  
      entity.Property(e =&gt; e.AddedBy)
          .HasColumnName("Added_By")
          .HasColumnType("varchar(45)");
  
      entity.Property(e =&gt; e.AddedDate)
          .HasColumnName("added_date")
          .HasColumnType("datetime");
  
      entity.Property(e =&gt; e.Comments)
          .HasColumnName("comment")
          .HasColumnType("text");
  });
  </code></pre>
  
  <p>I've been wrecking my brain all day to try and figure this one out... Any ideas?</p>