borza.dev

• 18 December 2016
• C#, Entity Framework

EF Code First - Views and Stored Procedures

---

Programming an application with Entity Framework and Code First does not mean you won't write any raw SQL anymore. C# code is great for the basic CRUD operations, but as the application grows, there are cases when you need to write complex queries containing various joins (on multiple columns) and aggregate functions, or you need to do some huge bulk updates. These are the situations, when you don't want to rely on SQL generated from LINQ and you rather create a view or a stored procedure, using all the power of SQL Server to improve its performance.

Let's suppose that you were given an assignment to develop a simple application, which should basically work only with couple of entities and performing some CRUD operations.
You have decided to go with Entity Framework and Code First approach, as it's easy to use and speeds the development up. Everything runs smoothly and you get by with only writing LINQ queries and using basic Entity Framework's functions. But as the application and its database grows, there come new requirements for dashboards displaying various statistics on the top of your data.
You start working on LINQ query for the first dashboard, which requires joins on more than 5 tables, subqueries and various aggregate functions. As you write the 20th line of code of your LINQ query, struggling to make it work, you realize that it may be cleaner and more efficient to write a database view or a stored procedure. This way you could use temp tables and another features of your database server (cached execution plans), to improve the responsiveness of your dashboards.

We will take a look how to handle this situation in the way, that it will play nicely with Entity Framework and Code First approach.

Using a database view is handy when you need to reuse its rows in multiple queries. You can map a database view to an entity in your database context, so you can treat it as a regular table in your LINQ queries.

Steps to handle a view:

1. Create a SomeEntityVw class with properties mapped to view columns. You can use EF features like virtual properties and ForeignKey annotations to be able to load entities related to the view in your queries.
2. Add the created entity as a new property DbSet<SomeEntityVw> SomeEntitiesVw to your database context.
3. Add new migration using Package Manager Console and add migration command.
4. As you can notice, the generated migration contains statements for creating a new table for the NewEntityVw in the Up() method. Delete this code and replace it with Sql(@"CREATE VIEW dbo.vwSomeEntities AS...") statement.
   You should also replace the code in the Down() method to be able to revert the migrations properly. Its code should contain call to Sql(@"DROP VIEW dbo.vwSomeEntities;").
5. Update the database.

Now you can use the new entity in your LINQ statements as it were a regular table (e.g. dbContext.SomeEntitiesVw.Where(x =>..)).

Of course, when you try to update some properties in the entity mapped to a view, a call to saveChanges() will fail, unless you create a writable view, which is quite rare. For that reason you should give the entities which are mapped to views some specific names, to make clear that they should not be updated (e.g. their names should end with 'Vw').

In some cases, a stored procedure is more suitable than a database view. Imagine a scenario when you have to show some complex statistics for a concrete user. Using a sql view, you would have the rows with statistics for all the users generated. Then you will have to call Where(x => x.UserId = id) statement in the code to get the row for the related user. As there would be many joins and group by clauses in the all users statistics view, it will take much more time as creating the statistics for just one user.
Probably more efficient would be a parametrized stored procedure with @userid parameter. This way, you could filter the rows directly when joining the tables using clauses like join on t1.userid = @userId. This way only the rows for the given user will be processed by SQL Server.

Another common scenario where stored procedures beat ORM frameworks are bulk updates and deletes. In Entity Framework we need to load the objects to the memory first, then we update or delete them and call dbContext.SaveChanges() to promote the changes to database. This is very inefficient when you deal with thousands of rows and stored procedures handle these use cases much better.

How to create a stored procedure?

1. Add a new (empty) migration to your project.
2. Add code to the Up() method as Sql(@"CREATE PROCEDURE dbo.spUserstatistics @userId int ...")
   Implement the Down() method's body with Sql(@"DROP PROCEDURE dbo.spUserstatistics;")
3. Update the database

How to call the stored procedure?

1. If your stored procedure returns a result set, create a class that match the columns of the returned table, e.g. SpResult.
   Then call the stored procedure as

   using (var context = new DbContext()) 
   { 
        var result = context.Database.SqlQuery<SpResult>("dbo.spUserstatistics @userId", 
                        new SqlParameter("@userId", id)).FirstOrDefault(); //or .ToList() 
   } 

2. If your stored procedure does not return a result set, only some value like success code, number of updated rows, etc. (it has a RETURN statement instead of SELECT), then you can call it as follows

   using (var context = new DbContext()) 
   { 
       var returnCode = new SqlParameter("@returnCode", SqlDbType.Int);
       returnCode.Direction = ParameterDirection.Output;
   
       context.Database.SqlQuery<object>("exec @returnCode = dbo.spBulkUpdateUserRecords @userId", 
                                   returnCode, new SqlParameter("@userId", id)).FirstOrDefault(); 
       var returnCodeValue = (int)returnCode.Value;
   } 

Using the migrations, your database views and stored procedures are deployed to the SQL Server the same way as the migrations for the tables, which were generated automatically from your code. This way, you don't need to use any additional mechanism for deploying your SQL scripts. More importantly, with properly written migrations (Down() methods implemented correctly), you can still use features of code-first migrations like reverting the database schema to a specific state, which is a huge benefit.

There is one important thing we loose when we use raw SQL instead of LINQ - full type and error checking at compile-time. When you have a stored procedure or a view defined on database level, your code will compile without errors even if you delete/modify the tables which the stored procedure or view operates on. For that reason it is very important to have unit tests also on database level, permanently checking that your views and stored procedures works as expected. If some of your tests starts failing and you detect that some stored procedure or view has to be updated, create a migration with proper Sql("ALTER...") statement before deploying the application.

If you are interested how to easily setup data access tests on a testing database with the same context and schema as your app has, check my post Entity Framework & Database Testing for more details.