Cast Error in SQL Server when outputting an integer from stored procedure

Question

I have this for a stored procedure which outputs just an integer

ALTER PROC GetPendingReservations
AS
BEGIN
    SELECT
        COUNT(DISTINCT FacilityAndAmenityId ) 
    FROM
        [Property].[PropAndAmenReservation] with(nolock)
    WHERE
        IsGranted = 'False' 
        AND CAST(StartDate AS DATE) = CAST(GetDate() AS DATE) 
        AND FacilityAndAmenityId NOT IN (SELECT FacilityAndAmenityId 
                                         FROM [Property].[PropAndAmenReservation] with(nolock)
                                         WHERE IsGranted = 'True' 
                                           AND CAST(StartDate AS DATE) = CAST(GetDate() AS DATE))
END

and I call it in my web api through this.

[HttpGet]
[Route("api/getfacilitiesreservation")]
public int GetFacilitiesReservation()
{
            using(var db = new ApplicationDbContext())
            {
                try
                {
                    const string query = "GetPendingReservations";

                    var count = db.Database.SqlQuery<int>(query);
                    return count;
                }
                catch(Exception ex)
                {
                    return 0;
                }
            }
}

But I get this error in my catch exception:

The specified cast from a materialized 'System.Int32' type to the 'System.String' type is not valid.

If I use db.Database.SqlQuery<int>(query), this is what I get:

Can you please show me how to this right? Thank you.

Why Sir? I can remove that but I don't think it will resolve my issue. — Ibanez1408, Oct 08 '18 at 02:31
What if you simply do `return db.Database.SqlQuery(query);` ? — Chetan, Oct 08 '18 at 02:37
`SELECT COUNT()` query returns int, you should use `db.Database.SqlQuery(query)`. — Tetsuya Yamamoto, Oct 08 '18 at 02:38
I just did it this way..... 'const string query = "GetPendingReservations"; var count = db.Database.SqlQuery(query).ToList(); return Convert.ToInt32(count[0]);' — Ibanez1408, Oct 08 '18 at 02:45
Ok... the issue is `db.Database.SqlQuery(query)` returns the collection of `int`. You need to do `var items = db.Database.SqlQuery(query).ToList();` and then `return items[0];` — Chetan, Oct 08 '18 at 02:53
@Ibanez1408 use this `db.Database.SqlQuery(query).First();` instead — iSR5, Oct 08 '18 at 03:04

score 2 · Accepted Answer · answered Oct 08 '18 at 03:26

Some things to consider in your issue:

1) SELECT COUNT() query returns a scalar integer mentioning total row count.

2) Database.SqlQuery<T> used to execute raw SQL query returns instance of DbRawSqlQuery which implements IEnumerable collection and expects iteration to get values inside it.

Based from these considerations and method return type as int, you should return single value of query result like this:

using (var db = new ApplicationDbContext())
{
    try
    {
        const string query = "GetPendingReservations";

        // First(), FirstOrDefault() or SingleOrDefault() may also be used
        return db.Database.SqlQuery<int>(query).Single();
    }
    catch (Exception ex)
    {
        // exception handling
    }
}

Similar issue: Entity Framework Code-First Execute Scalar-Valued Functions

Thank you very much. This is a lot better than what I came up with! — Ibanez1408, Oct 08 '18 at 04:35

Cast Error in SQL Server when outputting an integer from stored procedure

1 Answers1