CODEWITHSUNDEEP
asp.net-corestored-procedures.net-coreentity-framework-coreef-core-3.0
user9364154
user9364154

Reputation:

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

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.

Upvotes: 13

Views: 36169

Answers (3)

Mahmoud Elgazzar
Mahmoud Elgazzar

Reputation: 78

I came across this question and I have the same problem about how to execute your stored procedure from ef core the solutions I tried are as below

the function below tries get all persons in the person table based on the state as a given input to the stored procedure itself and did n't return the write answer as I want

public void GetPersonsByStateInterpolated()
{
    string state = "IL";
    var persons = _context.Persons.FromSqlInterpolated($"GetPersonsByState
    {state}").ToList();
    Assert.AreEqual(2, persons.Count);
}

the fix I made to solve the problem in the above function is as follows I changed the function I use to execute the proc from "FromSQLInterpolated" to "FromSqlRaw" the code below explains the change

public void GetPersonByStateInterpolated()
        {
            string state = "IL";
            var persons = _context.Persons.
                          FromSqlRaw($"GetPersonsByState @p0",
                          new[] {state}).ToList();
           
       
            Assert.AreEqual(2, persons.Count);
        }

hope this helps

for more explanation about the parameters that FromSqlRaw takes.

it takes two main parameters

  1. the first one is the name of your stored procedure concatenating with one parameter as explained in the function above
  2. the second one is an array of parameters you will send

Upvotes: 1

ws_
ws_

Reputation: 1256

You can learn how to use sql command in ef core with this artical:

https://www.learnentityframeworkcore.com/raw-sql#stored-procedures https://www.learnentityframeworkcore.com/raw-sql#database.executesqlcommand

As efcore updated. You don't need to build SqlParameter in the new efcore api.

ExecuteSqlCommandAsync and FromSql is obsolete now ,you can see this in the code comment :

[Obsolete("For the async execution of SQL queries using plain strings, use ExecuteSqlRawAsync instead. For the async execution of SQL queries using interpolated string syntax to create parameters, use ExecuteSqlInterpolatedAsync instead.")]
public static Task<int> ExecuteSqlCommandAsync([NotNull] this DatabaseFacade databaseFacade, RawSqlString sql, [NotNull] IEnumerable<object> parameters, CancellationToken cancellationToken = default);

[Obsolete("For returning objects from SQL queries using plain strings, use FromSqlRaw instead. For returning objects from SQL queries using interpolated string syntax to create parameters, use FromSqlInterpolated instead. Call either new method directly on the DbSet at the root of the query.", true)]
public static IQueryable<TEntity> FromSql<TEntity>([JetBrains.Annotations.NotNull] this IQueryable<TEntity> source, [JetBrains.Annotations.NotNull] [NotParameterized] FormattableString sql) where TEntity : class

The new apis with params are:

public static Task<int> ExecuteSqlInterpolatedAsync([JetBrains.Annotations.NotNull] this DatabaseFacade databaseFacade, [JetBrains.Annotations.NotNull] FormattableString sql, CancellationToken cancellationToken = default(CancellationToken))
public static IQueryable<TEntity> FromSqlInterpolated<TEntity>([JetBrains.Annotations.NotNull] this DbSet<TEntity> source, [JetBrains.Annotations.NotNull] [NotParameterized] FormattableString sql) where TEntity : class

These new apis use FormattableString as param, for example

string parm1="A";
string parm2="B";
_dbContext.Database.ExecuteSqlInterpolatedAsync($"EXEC proc @parm1={parm1},@parm2={parm2}");

@parm1 is defined in your database procedure , {parm1} is from csharp string parm1 value

FromSql Detail:https://learn.microsoft.com/en-us/ef/core/querying/raw-sql#passing-parameters

Upvotes: 6

user9364154
user9364154

Reputation:

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();

Upvotes: 15

Related Questions