Stored Procedure call using WebApi EntityFrameWorkCore 1.1 gets and object not the integer results

Question

I have the following user defined procedure to call a calculated data from database with 2 parameters to return an integer (Count(*))

 USE [QIIS2]
 GO

 PROCEDURE [dbo].[sp.GetCansTotals] 
         @hospitalId int
 AS   
 BEGIN
     SET NOCOUNT ON;  

     SELECT COUNT(*) AS TotalCancelled
     FROM Cans
     WHERE hospitalId = @hospitalId;
END

The repository to call the procedure:

 public async Task GetCansTotals(int hospitalId)
        {
            using (appContext)
            {
                var hospitalid = new SqlParameter("@hospitalId", 1);
                var cans = appContext.Cans.FromSql("Exec GetCansTotals @hospitalId", hospitalId);
            }
        }

and the controller:

[HttpGet("byParams")]
        public IActionResult GetCanTotal(int hospitalId)
        {
            var res = _unitOfWork.Cans.GetCansTotals(hospitalId);
            return Ok(res);
        }

When passing the request with postman:

http://localhost:56963/api/cansdatas/byParams?hospitalId=2 I get an object rather than the results of COUNT(*)

{
    "result": {},
    "id": 1,
    "exception": null,
    "status": 5,
    "isCanceled": false,
    "isCompleted": true,
    "creationOptions": 0,
    "asyncState": null,
    "isFaulted": false
}

Can you help please?

As far as I know it is not possible to get the result of a stored procedure with ado.net (ICommand and that stuff that EF depends on). Can you change the sp? Did you try to do a "DECLARE ATMyCount INT; SELECT ATMyCount = COUNT(*) FROM ..; RETURN ATMyCount". Then you have to change the call in C# also. Something like appContext.Cans.FromSql("DECLARE ATResult INT; EXEC ATResult = GetCansTotals AThospitalId; RETURN ATResult") — yonexbat, Oct 22 '17 at 10:09
`GetCansTotals` is returning a `Task`, but you are not treating is like an `async` function. You need `async/await` your `GetCanTotal` in the controller. — R. Richards, Oct 22 '17 at 12:42
The way you are calling the stored procedure, the system will expect it to return the data of type Cans. — Rakesh Burbure, Oct 22 '17 at 17:36

alwayslearning · Accepted Answer · 2017-10-22T13:07:57.213

Couple of issues with your code. Here's what you can do -

You are returning a scalar value (count) from the stored procedure and calling it on the DbSet via the FromSql method. This method should be used when the stored procedure returns a result set that maps to the properties of the 'Can' type (or whatever type the 'Cans' property is returning in the DbSet). This will not work.
It seems that to execute stored procedures that return a scalar value you still need to fall back on plain ADO.NET . You can add an extension method on your DbContext or in your repository to do this. Check this answer for more details - Entity Framework 7 FromSql stored procedure return value

You could also return the count without a stored procedure but using LINQ. Also change the return type of the method to Task and make it async.

public async Task<int> GetCansTotals(int hospitalId)
{
    using (appContext)
    {
        return appContext.Cans.CountAsync(c => c.HospitalId == hospitalId);
    }
}

Consider marking your controller method also as async and call your repository method using await. Also you would need to change the return type of the controller method to Task.
```
[HttpGet("byParams")]
public async Task<IActionResult> GetCanTotal(int hospitalId)
{
    var res = await this._unitOfWork.GetCansTotals(hostpitalId);
    return Ok(res);
}
```

Stored Procedure call using WebApi EntityFrameWorkCore 1.1 gets and object not the integer results

1 Answers1