Getting a conversion error when trying to execute a stored procedue with .NET 5.0 web app

Question

I am writing a .NET 5.0 web app using Entity Framework. I have a SQL Server stored procedure that I need to call to retrieve some data from some complex calculations. I have previously used Microsoft Access to run the stored procedure from and ODBC connection.

I have tried the web app call with FromSqlRaw and FromSqlInterpolated with and without parameters. I finally just decided to see if I can make it work with the hard coded values then worry about including parameters later.

The error I keep getting is

InvalidCastException: Unable to cast object of type 'System.Int16' to type 'System.Int32'

I'm not sure if this is THE error that is keeping it from working, but it's the common denominator in the different configurations I've tried. My initial thought is that it's an issue with the ShiftID parameter, but nothing I have tried has worked.

I'm not sure what needs to be shown to try to troubleshoot this, so here are the things I'm primarily working with.

Here is the stored procedure call when I run it from SQL Server Management Studio

USE [DATABASE]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[bds_TargetMetrics_Results_Get_S]
        @p_ShiftDate = NULL,
        @p_ShiftID = NULL,
        @p_MachineIDs = N'29',
        @p_TargetMetricIDs = N'9',
        @p_TargetMetricGroupIDs = N'1,2,3'

SELECT  'Return Value' = @return_value

Here are the expected results

Here is the Access Passthrough query I can use to return the same results.

EXECUTE dbo.bds_TargetMetrics_Results_Get_S
    @p_ShiftDate = '',
    @p_ShiftID = '',
    @p_MachineIDs = '29',
    @p_TargetMetricIDs = '9',
    @p_TargetMetricGroupIDs = '1,2,3'

Here is the code I'm trying to use in the web app. I created a TargetMetricMonitors class with the return fields from the stored procedure that I am trying to populate with the procedure results.

TargetMetricMonitors = await _context.TargetMetricMonitors.FromSqlRaw(
    "EXECUTE dbo.bds_TargetMetrics_Results_Get_S " 
    + "@p_ShiftDate = '', " 
    + "@p_ShiftID = '', " 
    + "@p_MachineIDs = '29', " 
    + "@p_TargetMetricIDs = '9', " 
    + "@p_TargetMetricGroupIDs = '1,2,3'").ToListAsync();

Show us your column type definitions and your object map for TargetMetricMonitors - somewhere, it has a short or int16 and wants a int — Nikki9696, Apr 09 '21 at 21:13
@Nikki9696 You were absolutely correct. I had to dig through the procedure to find the correct field types, but one of them was `smallint` so I changed my model for that field and it works. Thanks for the idea! — Eric Hutchins, Apr 12 '21 at 12:11

score 0 · Accepted Answer · answered Apr 12 '21 at 12:38

This was solved with the idea provided by Nikki9626 in the comments. I went through the stored procedure looking at the field types for all the temporary tables. I found one that was a smallint

I changed my model from public int TargetMetricStatus { get; set; } to public Int16 TargetMetricStatus { get; set; } and the page works.

Getting a conversion error when trying to execute a stored procedue with .NET 5.0 web app

1 Answers1