Entity Framework Core 3.1 - Part 4

Entity Framework Core 3.1 - Part 4

Overview

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.

 1    /*
 2     * a function to return the earliest course that a student signed up for
 3    */
 4    CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
 5        RETURNS char(30) AS 
 6        BEGIN
 7            DECLARE @ret CHAR(30)
 8            SELECT TOP 1 @ret = [Name]
 9              FROM Courses
10             WHERE Courses.Id in (SELECT CourseId
11                                    FROM StudentCourses
12                                   WHERE StudentId = @studentId)
13            ORDER BY StartDate // new field on Courses here
14            RETURN @ret
15        END
16    
17    /*
18     * a view return a list of Students, number of courses and earliest course signed up by the student
19    */
20    CREATE VIEW dbo.StudentCoursesStatistics
21    AS
22    SELECT dbo.Students.Name,
23           COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
24           dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
25      FROM dbo.StudentCourses INNER JOIN
26           dbo.Students ON
27           dbo.StudentCourses.StudentId = dbo.Students.Id
28     GROUP
29        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.

1    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:

 1    namespace StudentApp.Data.Migrations
 2    {
 3        public partial class StudentCoursesStatistics : Migration
 4        {
 5            protected override void Up(MigrationBuilder migrationBuilder)
 6            {
 7                migrationBuilder.Sql(
 8                    @"CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
 9                    RETURNS char(30) AS 
10                    BEGIN
11                        DECLARE @ret CHAR(30)
12                        SELECT TOP 1 @ret = [Name]
13                          FROM Courses
14                         WHERE Courses.Id in (SELECT CourseId
15                                                FROM StudentCourses
16                                               WHERE StudentId = @studentId)
17                        ORDER BY StartDate // new field on Courses here
18                        RETURN @ret
19                    END");
20    
21                migrationBuilder.Sql(
22                    @"CREATE VIEW dbo.StudentCoursesStatistics
23                    AS
24                    SELECT dbo.Students.Name,
25                           COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
26                           dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
27                      FROM dbo.StudentCourses INNER JOIN
28                           dbo.Students ON
29                           dbo.StudentCourses.StudentId = dbo.Students.Id
30                     GROUP
31                        BY dbo.Students.Name, dbo.StudentCourses.StudentId");
32            }
33    
34            protected override void Down(MigrationBuilder migrationBuilder)
35            {
36                migrationBuilder.Sql("DROP VIEW dbo.StudentCoursesStatistics");
37                migrationBuilder.Sql("DROP FUNCTION dbo.EarliestCourseSignedUpByStudent");
38            }
39        }
40    }

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?

 1    namespace StudentApp.Data.Migrations
 2    {
 3        public partial class StudentCoursesStatistics : Migration
 4        {
 5            protected override void Up(MigrationBuilder migrationBuilder)
 6            {
 7                migrationBuilder.Sql(
 8                    @"CREATE FUNCTION [dbo].[EarliestCourseSignedUpByStudent](@studentId int)
 9                    RETURNS char(30) AS 
10                    BEGIN
11                        DECLARE @ret CHAR(30)
12                        SELECT TOP 1 @ret = [Name]
13                          FROM Courses
14                         WHERE Courses.Id in (SELECT CourseId
15                                                FROM StudentCourses
16                                               WHERE StudentId = @studentId)
17                        ORDER BY StartDate // new field on Courses here
18                        RETURN @ret
19                    END");
20    
21                migrationBuilder.Sql(
22                    @"CREATE VIEW dbo.StudentCoursesStatistics
23                    AS
24                    SELECT dbo.Students.Name,
25                           COUNT(dbo.StudentCourses.CourseId) as NumberOfCourses,
26                           dbo.EarliestCourseSignedUpByStudent(MIN(dbo.Students.Id)) AS EarliestCourse
27                      FROM dbo.StudentCourses INNER JOIN
28                           dbo.Students ON
29                           dbo.StudentCourses.StudentId = dbo.Students.Id
30                     GROUP
31                        BY dbo.Students.Name, dbo.StudentCourses.StudentId");
32            }
33    
34            protected override void Down(MigrationBuilder migrationBuilder)
35            {
36                migrationBuilder.Sql("DROP VIEW dbo.StudentCoursesStatistics");
37                migrationBuilder.Sql("DROP FUNCTION dbo.EarliestCourseSignedUpByStudent");
38            }
39        }
40    }
41
42    
43    namespace UniversityApp.Domain
44    {
45        public partial class StudentCoursesStatistics
46        {
47            public partial class StudentCoursesStatistics
48            {
49                public string Name { get; set; }
50                public int? NumberOfCourses { get; set; }
51                public string EarliestCourse { get; set; }
52            }
53        }
54    }
55
56    namespace UniversityApp.Data
57    {
58        public class StudentContext : DbContext
59        {
60            public DbSet<Student> Students { get; set; }
61            public DbSet<Course> Course { get; set; }
62            // by default EF Core is going to be unhappy about the keyless entity as there is no key property
63            // configure this in the onmodelcreating method
64            public DbSet<StudentCoursesStatistics> StudentCoursesStatistics { get; set; }
65    
66            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
67            {
68                optionsBuilder.UseSqlServer("Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = SamuraiAppData");
69            }
70    
71            protected override void OnModelCreating(ModelBuilder modelBuilder)
72            {
73               // ...more code above..
74               // use the ToView method to avoid entity framework migrations to unintentionally generate scripts
75               // for the creation of this StudentCoursesStatistics entity
76               // EF Core, however, doesn't know how to create views anyway, so 
77               // stating this explicitly, ensures that EF Core will never try to create a migration for this
78               // database object. 
79               // An advantage of the HasNoKey method is that this entity will never be tracked!
80               // if you try to explicitly set tracking using AsTracking() 
81               // or by setting query tracking behaviour on the context, 
82               // EF Core will just ignore it
83               modelBuilder.Entity<StudentCoursesStatistics>().HasNoKey().ToView("StudentCoursesStatistics");
84               // ...any other code here. 
85            }
86        }
87    }

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:

  1. FromSqlRaw
  2. FromSqlRawAsync
  3. FromSqlInterpolated
  4. FromSqlInterpolatedAsync

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.

 1    /*
 2     * For the sake of this example, you have to imagine that we have a stored 
 3     * procedure in the database already and we are only just calling it.
 4     * This one takes in an integer value as param and returns Students matching
 5     * that criteria. This probably could have been done using pure entity framework
 6     * but I am chosing to do it using stored procedures for demonstration purposes
 7     * only.
 8     * If your stored procedure is called StudentsWhoScoredMoreThanXPercent,
 9     * you could execute it using FromSQLRaw
10    */
11    private static void QueryUsingFromRawSqlStoredProcedure()
12    {
13        var threshold = 85;
14        // ef core passes this command as a parameterised sql statement
15        // which means, better protection from SQL injection
16        var students = dbContext.Students.FromSqlRaw(
17            "EXEC dbo.StudentsWhoScoredMoreThanXPercent {0}", threshold).ToList();
18    }
19    
20    /*
21     * the same result can be achieved using Interpolated method too
22     * EF Core still results in a parameterised query, which means 
23     * better protection from SQL Injection
24    */
25    private static void QueryUsingFromRawSqlStoredProcedure()
26    {
27        var threshold = 85;
28        // ef core passes this command as a parameterised sql statement
29        // which means, better protection from SQL injection
30        var students = dbContext.Students.FromSqlInterpolated(
31            $"EXEC dbo.StudentsWhoScoredMoreThanXPercent {threshold}").ToList();
32    }

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.

comments powered by Disqus