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.
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