In this article, we will see how to use stored procedures in Entity Framework Core 7.
Although Entity Framework and LINQ provide a simple way to work with the data, stored procedures still have their place when considering data access and manipulation.
Setup
Let’s define our data model and stored procedures.
We will use the same structure as in our previous article.
So we have a Student class:
public class Student
{
public int Id { get; set; }
public string? Name { get; set; }
public int Mark { get; set; }
public int CourseId { get; set; }
public Course? Course { get; set; }
}
And the Course class:
public class Course
{
public int Id { get; set; }
public string? Title { get; set; }
public ICollection<Student>? Students { get; set; }
}
We will also define our stored procedures.
First FindStudents, a procedure that returns a list of Students filtered by the name:
CREATE OR ALTER PROCEDURE [dbo].[FindStudents]
@SearchFor NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Students WHERE [Name] LIKE '%' + @SearchFor + '%'
END
Then FindStudentsAlt, a procedure that returns a list of students with only student names and course titles filtered by student name:
CREATE OR ALTER PROCEDURE [dbo].[FindStudentsAlt]
@SearchFor NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT
StudentName = S.[Name],
CourseTitle = C.Title
FROM
Students S
LEFT JOIN Courses C ON S.CourseId = C.Id
WHERE
S.[Name] LIKE '%' + @SearchFor + '%'
END
We will also define a procedure to update student marks, UpdateStudentMark:
CREATE OR ALTER PROCEDURE UpdateStudentMark
@Id int,
@Mark int
AS
BEGIN
UPDATE
Students
SET
Mark = @Mark
WHERE
Id = @Id;
END
And finally, we will define a procedure that updates a student’s mark and returns an average mark for all students:
CREATE OR ALTER PROCEDURE UpdateStudentMarkWithReturnValue
@Id int,
@Mark int,
@AvgMark int OUTPUT
AS
BEGIN
UPDATE
Students
SET
Mark = @Mark
WHERE
Id = @Id;
SELECT @AvgMark = AVG(Mark) FROM Students
END
We can see that we do not use SET NOCOUNT ON; in this procedure because we want to get the number of affected rows when we call this procedure.
Using stored procedures can simplify and improve the execution of SQL queries. We can use stored procedures to define complex queries which might become too complicated to read and write in LINQ.
If we want to use a stored procedure to query the data, the query needs to return the complete set of properties (columns) for EF to map the data to the entity.
Stored procedures in EF 7
We can divide methods to execute stored procedures in EF into two groups, methods to query the data and methods to add/update/delete data.
If we want to query the data, we will use FromSql, FromSqlInterpolated, or FromSqlRaw methods that are available only on the DbSet object.
To add, update or remove records, we need to use ExecuteSql, ExecuteSqlInterpolated, or ExecuteSqlRaw methods, which are available for DbContext.Database object.
Let’s take a closer look at these methods.
DbSet.FromSql
Let’s define a method FindStudentsFromSql that uses the FromSql method to call FindStudents stored procedure and returns a list of Students:
public static List<Student>? FindStudentsFromSql(AppDbContext context, string searchFor)
{
return context?.Students?.FromSql($"FindStudents {searchFor}").ToList();
}
FromSql method is introduced in EF Core 7.0. It takes FormattableString as the only parameter, which means it is safe against SQL injection since all parameter data are wrapped in an DbParameter object. As a parameter string, we pass the name of the procedure and the value for the parameter.
DbSet.FromSqlInterpolated
FromSqlInterpolated is s available since EF Core 3.0, and it is basically the same as FromSql:
public static List<Student>? FindStudentsFromSqlInterpolated(AppDbContext context, string searchFor)
{
return context?.Students?.FromSqlInterpolated($"FindStudents {searchFor}").ToList();
}
Next, when we call the FindStudentsAlt stored procedure, we cannot map the result to our data model:
public static List<Student>? FindStudentsAltFromSqlInterpolated(AppDbContext context, string searchFor)
{
return context?.Students?.FromSqlInterpolated($"FindStudentsAlt {searchFor}").ToList();
}
As a result, we see it will throw an exception since the underlying stored procedure returns an incomplete set of results:
public void WhenFindStudentsAltFromSqlInterpolated_ThenSuccess()
{
Assert.Throws<InvalidOperationException>(
() => Methods.FindStudentsAltFromSqlInterpolated(context, "100")
);
}
DbSet.FromSqlRaw
Unlike FromSql and FromSqlInterpolated, FromSqlRaw is not safe from SQL injection attacks. Therefore, we need to pay extra attention when defining SQL commands.
We should never pass concatenated or interpolated strings into this method.
Let’s define how we can use it to call stored procedure:
public static List? FindStudentsFromSqlRaw(AppDbContext context, string searchFor)
{
return context?.Students?.FromSqlRaw("FindStudents @searchFor",
new SqlParameter("@searchFor", searchFor)).ToList();
}
Let’s now show how we can misuse this method and, therefore, abuse it to perform SQL injection attacks.
First, we need to define our unsafe method:
public static List<Student>? FindStudentsFromSqlRawUnsafe(AppDbContext context, string searchFor)
{
return context?.Students?.FromSqlRaw($"FindStudents @searchFor = '{searchFor}'").ToList();
}
Here we can see that we call the correct stored procedure, but the @searchFor parameter is not passed as a DbParameter and the SQL command is constructed using interpolated string.
We can still use the method safely:
public void WhenFindStudentsExecuteSqlRawUnsafe_ThenSuccess()
{
var results = FindMethods.FindStudentsFromSqlRawUnsafe(context, "100");
Assert.True(results?.Count == 9);
}
But we can also use it maliciously:
public void WhenFindStudentsExecuteSqlRawUnsafeSqlInjection_ThenSuccess()
{
var results = FindMethods.FindStudentsFromSqlRawUnsafe(context,
@"xyz'; UPDATE Students SET Name = 'Student 000' WHERE Id = 1; SELECT '");
Assert.True(results?.Count == 0);
}
This example will not return any Students, but it will execute an UPDATE (or any other) query and possibly compromise the whole database.
Database.ExecuteSql
As we already mentioned, if we need to perform insert, update, or delete operations, we will use ExecuteSql methods on DbContext.Database object.
Here is how we can use the ExecuteSql method to call our UpdateStudentMark stored procedure:
public static int UpdateStudentMarkSql(AppDbContext context, int id, int mark)
{
return context.Database.ExecuteSql($"UpdateStudentMark @Id={id}, @Mark={mark}");
}
We can also use the async version:
public async static Task<int> UpdateStudentMarkSqlAsync(AppDbContext context, int id, int mark)
{
return await context.Database.ExecuteSqlAsync($"UpdateStudentMark @Id={id}, @Mark={mark}");
}
Same as FromSql and FromSqlInterpolated methods, ExecuteSql and ExecuteSqlInterpolated methods are safe against SQL injection attacks.
Database.ExecuteSqlInterpolated
Usage of the ExecuteSqlInterpolated method is the same as for ExecuteSql:
public static int UpdateStudentMarkSqlInterpolated(AppDbContext context, int id, int mark)
{
return context.Database.ExecuteSqlInterpolated($"UpdateStudentMark @Id={id}, @Mark={mark}");
}
And the async version:
public async static Task<int> UpdateStudentMarkSqlInterpolatedAsync(AppDbContext context, int id, int mark)
{
return await context.Database.ExecuteSqlInterpolatedAsync($"UpdateStudentMark @Id={id}, @Mark={mark}");
}
Database.ExecuteSqlRaw
And again, the same as FromSqlRaw, the ExecuteSqlRaw method needs to be used with caution for the same reasons.
Here we see how we can use it properly with both sync:
public static int UpdateStudentMarkSqlRaw(AppDbContext context, int id, int mark)
{
return context.Database.ExecuteSqlRaw("dbo.UpdateStudentMark @Id, @Mark",
new SqlParameter("@Id", id),
new SqlParameter("@Mark", mark));
}
And async version:
public async static Task UpdateStudentMarkSqlRawAsync(AppDbContext context, int id, int mark)
{
return await context.Database.ExecuteSqlRawAsync("dbo.UpdateStudentMark @Id, @Mark",
new SqlParameter("@Id", id),
new SqlParameter("@Mark", mark));
}
Let’s see one example of calling a stored procedure with a return value. For this, a stored procedure needs to have an output parameter defined, and we need to define an output SQL parameter when we call that procedure:
public static int UpdateStudentMarkWithReturnValueSqlRaw(AppDbContext context, int id, int mark)
{
var avgMarkParameter = new SqlParameter()
{
ParameterName = "AvgMark",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};
context.Database.ExecuteSqlRaw("dbo.UpdateStudentMarkWithReturnValue @Id, @Mark, @AvgMark OUTPUT",
new SqlParameter("@Id", id),
new SqlParameter("@Mark", mark),
avgMarkParameter);
return (int)avgMarkParameter.Value;
}
Dynamic SQL
Now that we know how to use all these different methods to execute SPs, we are going to show a few more features that they support, which are not so much related only to stored procedures.
Let’s consider a situation where we need to create the query dynamically. It might seem like the interpolated string is the way to go:
public static int UpdateStudentMarkSqlDynamic(AppDbContext context, int id, int mark)
{
var field1 = "@Id";
var field2 = "@Mark";
return context.Database.ExecuteSql($"UpdateStudentMark {field1}={id}, {field2}={mark}");
}
But this is not possible because databases do not allow parameterizing parameter names or any other part of the schema, and this method would throw an SqlException.
In this situation, we can use the raw method to create the query:
public static int UpdateStudentMarkSqlRawDynamic(AppDbContext context, int id, int mark)
{
var field1 = "@Id";
var field2 = "@Mark";
return context.Database.ExecuteSqlRaw($"dbo.UpdateStudentMark {field1} = @Id, {field2}=@Mark",
new SqlParameter("@Id", id),
new SqlParameter("@Mark", mark));
}
Here we still want to send input values as SqlParameter objects to prevent possible SQL injection attacks.
Combining With LINQ
It is worth pointing out it is possible to combine SQL commands with LINQ operators.
This is not directly applicable to the stored procedure, so we will define a method that uses a simple SELECT query in combination with LINQ:
public static List? FindStudentsFromSqlAndLinq(AppDbContext context, string searchFor)
{
return context?.Students?
.FromSql($"SELECT * FROM Students")
.Where(m => m.Name != null && m.Name.IndexOf(searchFor) > -1)
.OrderBy(m => m.Mark)
.ToList();
}
We can also inspect the SQL query this method generates:
DECLARE @__searchFor_1 nvarchar(4000) = N'100';
SELECT [e].[Id], [e].[CourseId], [e].[Mark], [e].[Name]
FROM (
SELECT * FROM Students
) AS [e]
WHERE ([e].[Name] IS NOT NULL) AND
CASE WHEN @__searchFor_1 = N'' THEN 0
ELSE CAST(CHARINDEX(@__searchFor_1, [e].[Name]) AS int) - 1 END > -1
ORDER BY [e].[Mark]
We see that LINQ queries are applied on top of the initial SQL query.
Change Tracking
Queries that use the methods we just described follow the same change tracking rules as LINQ queries. EF tracks these results by default, as it tracks any other result from a LINQ query.
Let’s define a method that demonstrates this:
public static int? FindStudentsFromSqlAndUpdateMarks(AppDbContext context, string searchFor)
{
var students = context?.Students?
.FromSql($"FindStudents {searchFor}").ToList();
if (students != null)
foreach (var student in students)
student.Mark += 1;
return context?.SaveChanges();
}
The results we get from the stored procedure are valid entities, and we can work with them as with any other collection of entities.
In this case, the Context.SaveChanges method will successfully save updated data to the database.
Conclusion
In this article, we learned how to work with stored procedures from EF Core 7.0.
We also examined the limitations and possible security pitfalls when executing stored procedures through available EF Core methods.
