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

.net-core asp.net-core ef-core-3.0 entity-framework-core stored-procedures

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.

1
2
12/3/2019 6:53:00 AM

Popular Answer

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://docs.microsoft.com/en-us/ef/core/querying/raw-sql

1
12/9/2019 9:07:49 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow