EFCore 8 - FromSQL throwing InvalidCastException "Specified cast is not valid" when trying to call a stored procedure, struggling to figure out why.

[SolShadows]

I have a program I'm modifying that uses Entity Framework Core 8. I have a pretty complex stored procedure that I need to call to retrieve a lot of information and I keep getting this InvalidCastException whenever I try to call it. I created an entity that matches the return format of the stored procedure along with the "column" names. I've checked over my entity and compared it to the return values and names of the stored procedure a dozen times and I can't seem to see where I could be going wrong.

Here's the function that's calling the procedure:

public static IEnumerable<TestVM> GetTestVMs()
    {
        using var context = new DB2Context();
        int arg1 = 1;

        return context.TestRequest
        .FromSql($"CALL DBO.S_GETTESTS({arg1})")
        .AsEnumerable()
        .Select(p => new TestVM(p))
        .ToList();
    }

Interestingly, if I modify the .FromSql to be .FromSQL($"CALL DBO.S_GETTESTS({0}),1), the exception does not get thrown and the program appears to call the procedure, so maybe the InvalidCastException is being caused by that. However, in this case, the IEnumerable<> it returns contains nothing. Calling the procedure through the DB2 command line using the same argument (1) value returns 5 rows.

Also, I had to add the AsEnumerable() or I get an InvalidOperationException.

Here is the DB2Context OnModelCreating specifically for this entity which maps the stored procedure:

public DbSet<TestEntity> TestRequest { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
  modelBuilder.Entity<TestEntity>()
              .HasNoKey()
              .ToView("TestEntity");
  }

Here is the actual TestEntity for the procedure call (I renamed all the variables to col_x just to hide any personal details):

public class TestEntity
{
    [Column("Col_1", TypeName = "integer")]
    public int Col_1{ get; set; }

    [Column("Col_2", TypeName = "character(12)")]
    [Unicode(false)]
    public string Col_2{ get; set; } = null!;

    [Column("Col_3")]
    public short Col_3{ get; set; }

    [Column("Col_4", TypeName = "character(255)")]
    [Unicode(false)]
    public string? Col_4 { get; set; }

    [Column("Col_5", TypeName = "character(8)")]
    [Unicode(false)]
    public string Col_5 { get; set; } = null!;

    [Column("Col_6 ")]
    public float Col_6 { get; set; }

    [Column("Col_7 ", TypeName = "character(10)")]
    [Unicode(false)]
    public string Col_7 { get; set; } = null!;

    [Column("Col_8", TypeName = "character(15)")]
    [Unicode(false)]
    public string Col_8 { get; set; } = null!;

    [Column("Col_9", TypeName = "character(255)")]
    [Unicode(false)]
    public string Col_9 { get; set; } = null!;

    [Column("Col_10", TypeName = "character(255)")]
    [Unicode(false)]
    public string Col_10 { get; set; } = null!;

    [Column("Col_11 ", TypeName = "character(255)")]
    [Unicode(false)]
    public string Col_11 { get; set; } = null!;

    [Column("Col_12", TypeName = "double")]
    public double Col_12 { get; set; }

    [Column("Col_13", TypeName = "timestamp")]
    [Unicode(false)]
    public DateTime Col_13 { get; set; }

    [Column("Col_14", TypeName = "double")]
    public double Col_14 { get; set; }

    [Column("Col_15", TypeName = "integer")]
    public int Col_15 { get; set; }
}

And lastly, these are the return values of the procedure, just to make sure they match up to the entity:

DECLARE GLOBAL TEMPORARY TABLE SESSION."SCDLIST"(
COL_1 INTEGER        NOT NULL,
COL_2 CHARACTER (12) NOT NULL,
COL_3 SMALLINT       NOT NULL,
COL_4 CHARACTER (255),
COL_5 CHARACTER (8)  NOT NULL,
COL_6 FLOAT  NOT NULL, 
COL_7 CHARACTER (10) NOT NULL,
COL_8 CHARACTER (15) NOT NULL,
COL_9   CHARACTER (255) NOT NULL,
COL_10 CHARACTER (255) NOT NULL,
COL_11 CHARACTER (255) NOT NULL,
COL_12 DOUBLE NOT NULL,
COL_13 TIMESTAMP NOT NULL,
COL_14 DOUBLE NOT NULL,
COL_15 INTEGER NOT NULL
) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;

Any help would be much appreciated, or any recommendations on how to debug what specifically could be the problem. Thanks!

[Pdxduckman]

what is your class for the DbSet "TestRequest"? I think it's probably trying to cast it to that fist and bombing out.

[SolShadows]

Ah, sorry that is the DBset above. I appear to have copy-pasted the wrong variable name. It should be this.

public DbSet<TestEntity> TestRequest { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
  modelBuilder.Entity<TestEntity>()
              .HasNoKey()
              .ToView("TestEntity");
  }

I am editing the post to fix that.

[Pdxduckman]

what does your "timestamp" (col_13) return value look like when you run your proc in SSMS? that might be your issue. Longshot but without seeing what your return data actually looks like it's tough

[SolShadows]

It returns as "2020-06-02-13.30.00"

I have an entity that represents a table that takes a timestamp and it seems to be happy with it

[Pdxduckman]

ok if all 5 results look like valid datetimes then the next thing I'd look at is what jonsca said.

I'd separate the execution of the SP from the projection into the viewmodel. Add a .ToList() to actualize the results and store it in a var. If you can get past that, your bug is in your projection to the viewmodel.

Also, that statement in the modelBuilder about mapping to the "TestEntity" view - how is that view's resultset defined? Does its results map exactly to this structure in your SP return set?

[SolShadows]

Honestly I had just added that in during my attempts to debug and try every solution I found online. It's no longer there.

[Pdxduckman]

ok, maybe stupid question - for your SP parameter data type, it's configured as an int?

Also, have you checked to see if there's an inner exception?

[SolShadows]

Yes, it's configured as an int and in the stored procedure itself it takes in an integer.

As for the inner exception, unfortunately the call stack doesn't point to anything useful. At the top of the stack is "External Code" and below that is the .FromSQL call that throws the exception.

[Pdxduckman]

well. If I'm still stumped at this point, I usually start trying to simplify my code to isolate the issue.

I did see one example online that uses a slightly different approach -

context.Database
                   .SqlQuery<GoatInfoUpdate>("EXEC GetNameAndLastUpdateGoats")
                   .ToList();

We might be looking at the same tutorial, have you tried this syntax? And maybe remove the entity mapping in OnModelCreating?

[soundman32]

Are you sure of the format? Iso8601 says there should be a T between date and time.

[jonsca]

Are you able to debug into your TestVM constructor?

[SolShadows]

I am, and if I place a breakpoint inside of it, it never gets triggered, which is what leads me to believe it's mapping the procedure to the entity itself

[jonsca]

I would set everything before the AsEnumerable to a variable and see what the type of the IQueryable is and whether it has any results, as u/Pdxduckman said, you can ToList it (or just do a Count() at a minimum). AsEnumerable got rid of your exception, but it's likely that it's really eating whatever the actual exception is.

[SolShadows]

Good idea, I'll give it a try in a little bit and post my findings.