In my previous post, we talked about interacting with related entities using entity framework.
Introduction
Time to look at some special cases where you would want entity framework to work with views, stored procedures and plain old SQL statements.
There is a wide minconception that developers raise, “Oh it is a stored procedure, that can’t be good”.
There is nothing inherently wrong with using stored procedures. It is nice to have all business logic in one place. However, sometimes complex business needs might result in complex SQL queries, and you are better-off writing such queries with the help of someone who is familiar with SQL instead of doing it in EntityFramework. Which is probably why you have applications which has a few references to some well written stored procedures.
But EntityFramework actually does give you the flexibility to write standard SQL statements and parameterised ones in the .NET layer itself, which can be executed directly on the underlying database. Let us take a look at what’s possible and how it is done!
Views and Stored Procs
Database views and **_stored procedures _**are sometimes necessary for your otherwise entityframework only solution. A lot of time analytics queries perform better if written in SQL. Let us look at how to create a function and a view and use it in your entity framework layer.
/*
* a function to return the earliest course that a student signed up for
*/
CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
RETURNS char(30) AS
BEGIN
DECLARE @ret CHAR(30)
SELECT TOP 1 @ret = [Name]
FROM Courses
WHERE Courses.Id in (SELECT CourseId
FROM StudentCourses
WHERE StudentId = @studentId)
ORDER BY StartDate // new field on Courses here
RETURN @ret
END
/*
* a view return a list of Students, number of courses and earliest course signed up by the student
*/
CREATE VIEW dbo.StudentCoursesStatistics
AS
SELECT dbo.Students.Name,
COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
FROM dbo.StudentCourses INNER JOIN
dbo.Students ON
dbo.StudentCourses.StudentId = dbo.Students.Id
GROUP
BY dbo.Students.Name, dbo.StudentCourses.StudentId
That’s all well and good and you have written lovely SQL there. But wait, isn’t one of the advantages of using EntityFramwework to keep track of all the database changes in the source control alongside the project? You can certainly do this pretty easily. First create an empty, templated migration file using entity framework’s command line api.
add-migration StudentCoursesStatistics
This should generate a lovely migration file of the name StudentCoursesStatistics and will have the Up and Down methods all ready to be implemented. The MigrationBuilder API has a Sql method that takes in raw SQL as a parameter to which you can give any SQL definition. To give you an idea, let me show you the code snippet:
namespace StudentApp.Data.Migrations
{
public partial class StudentCoursesStatistics : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
@"CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
RETURNS char(30) AS
BEGIN
DECLARE @ret CHAR(30)
SELECT TOP 1 @ret = [Name]
FROM Courses
WHERE Courses.Id in (SELECT CourseId
FROM StudentCourses
WHERE StudentId = @studentId)
ORDER BY StartDate // new field on Courses here
RETURN @ret
END");
migrationBuilder.Sql(
@"CREATE VIEW dbo.StudentCoursesStatistics
AS
SELECT dbo.Students.Name,
COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
FROM dbo.StudentCourses INNER JOIN
dbo.Students ON
dbo.StudentCourses.StudentId = dbo.Students.Id
GROUP
BY dbo.Students.Name, dbo.StudentCourses.StudentId");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("DROP VIEW dbo.StudentCoursesStatistics");
migrationBuilder.Sql("DROP FUNCTION dbo.EarliestCourseSignedUpByStudent");
}
}
}
Go run your migration against your local db and you get it all created for you!
Views are special - entities without a key
All this while we have been working with tables and entities with keys. If you noticed the view that we created, obviously doesn’t have a key, after all it is a view. There are entities without keys that are considered to be ReadOnly. This is exactly what the view is. Up until recently, EF and EF Core could only really understand entities with keys, this is primarily because the Change Tracker up until then relied on the keys to do the entity tracking. With EF Core 3, however, we can work with keyless entities, whether it is a view or a table without a primary key, this will just work!
If you are coming from an Entity Framework 2.x background then this is a pretty big change. Something you can read in the BreakingChanges Docs.
Keyless entities will not have a key property, quite obviously, which is why it is named that way, it will never be tracked and hence maps to table/views that do not have a primary key!
So what does this mean for you? This means, you can introduce a class in your application that represents a view! So how do we do this?
namespace StudentApp.Data.Migrations
{
public partial class StudentCoursesStatistics : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
@"CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
RETURNS char(30) AS
BEGIN
DECLARE @ret CHAR(30)
SELECT TOP 1 @ret = [Name]
FROM Courses
WHERE Courses.Id in (SELECT CourseId
FROM StudentCourses
WHERE StudentId = @studentId)
ORDER BY StartDate // new field on Courses here
RETURN @ret
END");
migrationBuilder.Sql(
@"CREATE VIEW dbo.StudentCoursesStatistics
AS
SELECT dbo.Students.Name,
COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
FROM dbo.StudentCourses INNER JOIN
dbo.Students ON
dbo.StudentCourses.StudentId = dbo.Students.Id
GROUP
BY dbo.Students.Name, dbo.StudentCourses.StudentId");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("DROP VIEW dbo.StudentCoursesStatistics");
migrationBuilder.Sql("DROP FUNCTION dbo.EarliestCourseSignedUpByStudent");
}
}
}
namespace UniversityApp.Domain
{
public partial class StudentCoursesStatistics
{
public partial class StudentCoursesStatistics
{
public string Name { get; set; }
public int? NumberOfCourses { get; set; }
public string EarliestCourse { get; set; }
}
}
}
namespace UniversityApp.Data
{
public class StudentContext : DbContext
{
public DbSet<Student> Students { get; set; }
public DbSet<Course> Course { get; set; }
// by default EF Core is going to be unhappy about the keyless entity as there is no key property
// configure this in the onmodelcreating method
public DbSet<StudentCoursesStatistics> StudentCoursesStatistics { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer("Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = SamuraiAppData");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// ...more code above..
// use the ToView method to avoid entity framework migrations to unintentionally generate scripts
// for the creation of this StudentCoursesStatistics entity
// EF Core, however, doesn't know how to create views anyway, so
// stating this explicitly, ensures that EF Core will never try to create a migration for this
// database object.
// An advantage of the HasNoKey method is that this entity will never be tracked!
// if you try to explicitly set tracking using AsTracking()
// or by setting query tracking behaviour on the context,
// EF Core will just ignore it
modelBuilder.Entity<StudentCoursesStatistics>().HasNoKey().ToView("StudentCoursesStatistics");
// ...any other code here.
}
}
}
So now that we have the view accessible from the application, let us query it as usual. This is no different from regular queries, hence I would not be going to examples. However, the only thing you have to remember is that you cannot use all DBSet methods on keyless entities. The compiler/IDE may not give you any error/warning here. But this will result in a runtime NullReferenceException.
Raw SQL and entity framework
There are several DbSet methods for executing Raw Sql commands. You can find all of them in the docs. There is always an Async counterpart to every regular method. And there is a completely different method to execute SQL in interpolated strings. The methods available are:
FromSqlRawFromSqlRawAsyncFromSqlInterpolatedFromSqlInterpolatedAsync
Always parameterise your SQL statements and never concatenate values to the SQL Query. This makes sure you are protected from the most common SQL Injection attacks.
FromSQLInterpolated will parameterise any concatenated string, so that you don’t end up having to face any SQL Injection threats. FromSQLRaw on the other hand, doesn’t do this. So keep that in mind when choosing the methods.
In case you are not familiar with string interpolation in C#, I recommend reading the docs page.
Stored procedures and EF
This is not very different from what you have already seen. You can execute a stored procedure using one of the methods I mentioned earlier in the Raw SQL section.
/*
* For the sake of this example, you have to imagine that we have a stored
* procedure in the database already and we are only just calling it.
* This one takes in an integer value as param and returns Students matching
* that criteria. This probably could have been done using pure entity framework
* but I am chosing to do it using stored procedures for demonstration purposes
* only.
* If your stored procedure is called StudentsWhoScoredMoreThanXPercent,
* you could execute it using FromSQLRaw
*/
private static void QueryUsingFromRawSqlStoredProcedure()
{
var threshold = 85;
// ef core passes this command as a parameterised sql statement
// which means, better protection from SQL injection
var students = dbContext.Students.FromSqlRaw(
"EXEC dbo.StudentsWhoScoredMoreThanXPercent {0}", threshold).ToList();
}
/*
* the same result can be achieved using Interpolated method too
* EF Core still results in a parameterised query, which means
* better protection from SQL Injection
*/
private static void QueryUsingFromRawSqlStoredProcedure()
{
var threshold = 85;
// ef core passes this command as a parameterised sql statement
// which means, better protection from SQL injection
var students = dbContext.Students.FromSqlInterpolated(
$"EXEC dbo.StudentsWhoScoredMoreThanXPercent {threshold}").ToList();
}
Now if you are reading and thinking ahead, your next question might be, alright, I’ve executed a SQL Stored procedure, what if I wanted to filter the results. Could I do that too? You are correct, you can and that can be done following the instructions in the docs.
Update using SQL commands
There isn’t much about this one in the official docs apart from what I found by explicitly searching for it.
I only know these extension methods on the Database property of the DbContext because of Julie Lerman. So I really owe her a lot for what I have learned about entity framework core. The thing about these methods are that they only returns the number of rows updated and not an entity itself. I am not going to attempt to go in further detail here as it is really not worth it, this is very similar to the previous section in terms of how to write the code for it and the only change is you are running this on dbContext.Database and not on dbContext.Students
Alright! That was very satisfying, trying to explain what I learned here. If I find out more about EF Core, I’ll create a follow up to this one. Till then, have fun.