How to execute stored procedure for multiple parameters using FromSqlInterpolated in EF Core 3.0?

Question

I have a stored procedure in Sql Server 2017 which takes three parameters.

CREATE PROCEDURE UpdateRestaurantInformation
@restaurantId nvarchar(max),
@restaurantName nvarchar(max),
@locationId int
AS
BEGIN
    UPDATE Restaurants
    SET RestaurantName = @restaurantName, LocationId = @locationId
    WHERE RestaurantId = @restaurantId;

    SELECT * FROM Restaurants WHERE RestaurantId = @restaurantId;
END

When I tried executing this stored procedure by using the code snippet below, It worked as expected.

   SqlParameter param1 = new SqlParameter("@p0",restaurant.RestaurantId);
   SqlParameter param2 = new SqlParameter("@p1", restaurant.RestaurantName);
   SqlParameter param3 = new SqlParameter("@p2", restaurant.Location.LocationId);

     var res = _context.Restaurants
    .FromSqlRaw("UpdateRestaurantInformation @p0,@p1,@p2", param1, param2, param3)
    .ToList();

But when I tried using FromSqlInterpolated like this below:

var res = await _context.Restaurants
   .FromSqlInterpolated(
   $"UpdateRestaurantInformation {restaurant.RestaurantId}, {restaurant.RestaurantName}, {restaurant.Location.LocationId}")
   .SingleAsync();

It's throwing this exception :

SqlException: Incorrect syntax near '@p0'. Microsoft.Data.SqlClient.SqlCommand+<>c.b__164_0(Task result)

What am I mistaking here? Please, somebody help me.

Answer 1

So, this is the solution I got from GitHub. All I had to do was to replace SingleOrDefault() with ToList() as @IvanStoev mentioned in the comment.

I am just adding this here if somebody needs this in future.

var res = await _context
           .Restaurants
           .FromSqlInterpolated($"UpdateRestaurantInformation {restaurant.RestaurantId}, {restaurant.RestaurantName}, {restaurant.Location.LocationId}").ToListAsync();

return res.SingleOrDefault();