View and Stored Procedure Mappings with the Entity Framework
by Ira Endres on Monday, April 15, 2019
Overview
The Microsoft Entity Framework is a powerful Object Relational Mapping (ORM) library that allows developers to rapidly build applications with database back-ends. For most projects, the Entity Framework can be granted direct table access to manipulate records in the database with many useful features however some projects require that the database be accessed and modified through database views and stored procedures. There are a few reasons why application requirements may require or prefer a database-level abstraction for data access. The most common reasons is that database administrators prefer that no application code directly accesses or modifies the data in the database and that permissions can be created for select and execute permissions for a given user or role. Another benefit to using views and stored procedures is that it provides an insulation between the database and the application code; if the underlying database structure is altered or a column type is changed and the views and stored procedures correctly abstract the change the application code can still function without throwing a DbEntityValidationException. Additionally, complex structures can be abstracted as Complex Types through views and stored procedures touching multiple structures but from the codes perspective it is interacting with a single object of the complex type. A final benefit is that stored procedures can often be mapped in such a way as to leverage database constraints such as defaults or identity clauses. One caveat to using a database-first approach is that the method forgoes the Code First Migrations capability requiring that database changes be applied separately.
We will cover the basics of Entity Framework view and stored procedure mapping capabilities with some simple database objects with CRUD views and stored procedures.
Sample Database Structure
For this example, we will use a database structure with two database tables Author and Book.
create table Author (
AUTHOR_ID bigint IDENTITY(1,1) not null
constraint PK_Author primary key,
AUTHOR_DISPLAY_NAME nvarchar(2048) not null,
)
GO
create table Book (
BOOK_ID bigint IDENTITY(1,1) not null
constraint PK_Book primary key,
AUTHOR_ID bigint not null
constraint FK_Book_Author foreign key references Book(BOOK_ID),
BOOK_TITLE nvarchar(2048) not null,
BOOK_SUMMARY nvarchar(max),
BOOK_TOTAL_PAGES int,
BOOK_RELEASE_DATE date,
BOOK_ISBN nvarchar(32),
BOOK_CREATED datetimeoffset not null
constraint DF_Book_BOOK_CREATED default sysdatetimeoffset(),
BOOK_UPDATED datetimeoffset null
)
GO
Author CRUD View and Stored Procedures
The Author table is really simple in this example and will have the following CRUD view and stored procedures for insert, update, and delete.
-- Author View
create view ef_Author as
select
AUTHOR_ID,
AUTHOR_DISPLAY_NAME
from Author
GO
-- Author Insert Stored Procedure
create procedure ef_Author_Insert
@AUTHOR_DISPLAY_NAME nvarchar(2048)
as
begin
declare @ids table (AUTHOR_ID bigint)
insert into Author ( AUTHOR_DISPLAY_NAME )
output inserted.AUTHOR_ID into @ids
values ( @AUTHOR_DISPLAY_NAME )
select AUTHOR_ID from @ids
end
GO
-- Author Update Stored Procedure
create procedure ef_Author_Update
@AUTHOR_ID bigint,
@AUTHOR_DISPLAY_NAME nvarchar(2048)
as
begin
update Author set AUTHOR_DISPLAY_NAME = @AUTHOR_DISPLAY_NAME
where AUTHOR_ID = @AUTHOR_ID
end
GO
-- Author Delete Stored Procedure
create procedure ef_Author_Delete
@AUTHOR_ID bigint
as
begin
delete from Author where AUTHOR_ID = @AUTHOR_ID
end
GO
For the most part, the view and stored procedures are straightforward with the exception of the insert function; because the Author tables primary key is also an identity column, the database stored procedure should be responsible for returning the created primary key value back to the Entity Framework to be used as an Entity Key. More on this later when we get to the mapping details. Another item you may have noticed is the naming convention of ef_TableName_Action; in practice we have discovered that a naming distinction between the views and stored procedures used for mapping helps to differentiate between views and stored procedures used exclusively for Entity Framework mapping and those written for summary views and database logic.
It is important to note also that while we could use SCOPE_IDENTITY() to retrieve the created primary key value we have found the table parameter syntax useful for both integer primary keys as well as unique identifiers.
Book CRUD View and Stored Procedures
Same now for Book however we will demonstrate default constraints and results from the update stored procedure. In our example, the database table Book specifies some timestamp columns used to track when the record was created and updated.
-- Book View
create view ef_Book as
select
BOOK_ID,
AUTHOR_ID,
BOOK_TITLE,
BOOK_SUMMARY,
BOOK_TOTAL_PAGES,
BOOK_RELEASE_DATE,
BOOK_ISBN,
BOOK_CREATED,
BOOK_UPDATED
from Book
GO
-- Book Insert Stored Procedure
create procedure ef_Book_Insert
@AUTHOR_ID bigint,
@BOOK_TITLE nvarchar(2048),
@BOOK_SUMMARY nvarchar(max) = null,
@BOOK_TOTAL_PAGES int = null,
@BOOK_RELEASE_DATE date = null,
@BOOK_ISBN nvarchar(32) = null
as
begin
declare @ids table (BOOK_ID bigint, BOOK_CREATED datetimeoffset)
insert into Book (
AUTHOR_ID,
BOOK_TITLE,
BOOK_SUMMARY,
BOOK_TOTAL_PAGES,
BOOK_RELEASE_DATE,
BOOK_ISBN
)
output inserted.BOOK_ID, inserted.BOOK_CREATED into @ids
values (
@AUTHOR_ID,
@BOOK_TITLE,
@BOOK_SUMMARY,
@BOOK_TOTAL_PAGES,
@BOOK_RELEASE_DATE,
@BOOK_ISBN
)
select BOOK_ID, BOOK_CREATED from @ids
end
GO
-- Book Update Stored Procedure
create procedure ef_Book_Update
@BOOK_ID bigint,
@AUTHOR_ID bigint,
@BOOK_TITLE nvarchar(2048),
@BOOK_SUMMARY nvarchar(max) = null,
@BOOK_TOTAL_PAGES int = null,
@BOOK_RELEASE_DATE date = null,
@BOOK_ISBN nvarchar(32) = null
as
begin
declare @ids table (BOOK_UPDATED datetimeoffset)
update Book set
AUTHOR_ID = @AUTHOR_ID,
BOOK_TITLE = @BOOK_TITLE,
BOOK_SUMMARY = @BOOK_SUMMARY,
BOOK_TOTAL_PAGES = @BOOK_TOTAL_PAGES,
BOOK_RELEASE_DATE = @BOOK_RELEASE_DATE,
BOOK_ISBN = @BOOK_ISBN,
BOOK_UPDATED = SYSDATETIMEOFFSET()
output inserted.BOOK_UPDATED into @ids
where BOOK_ID = @BOOK_ID
select BOOK_UPDATED from @ids
end
GO
-- Book Delete Stored Procedure
create procedure ef_Book_Delete
@BOOK_ID bigint
as
begin
delete from Book where BOOK_ID = @BOOK_ID
end
GO
The insert database stored procedure now captures both the identity column value as well as the created timestamp correctly leveraging the default constraint on the column; both the identity column as well as the timestamp are returned in the result set of the insert stored procedure. The same applies to the update stored procedure in that it captures the timestamp value written to the Book record on update and returns the value in the result.
Entity Model Designer with View and Stored Procedure Mappings
If you already have an existing ADO.NET Entity you can skip the creation step, otherwise go to Project > Add New Item. Select from Visual C# category and the Data sub-category and the ADO.NET Entity Data Model. Give your Entity Model a name and select Add. From the create screen, select the EF Designer from Database and on the next step, provide the database connection string with which to load the schema into the dialog in the next step.
If you are starting with an existing ADO.NET Entity Model, double click on the EDMX file to open the Entity Designer window. In the space in between the entity models right-click in the Entity Designer window and select Update Model from Database.
In the update wizard, proceed to add the CRUD views and stored procedures for the Author and Book tables previously created. *Do not check any of the tables!* Next, uncheck "Include foreign key columns in the model" and uncheck "Import selected stored procedures and functions into the entity model". The first option ensures that no foreign key references will be added (and they shouldn't as views do not have foreign keys). The second option will skip creation of Function Imports for the CRUD stored procedures; stored procedure mappings do not require that the CRUD stored procedures be imported as Function Imports. Then click Finish.
Once finished, the new objects will appear in the Entity Designer window. There are a couple of code-cleanup options that we prefer to do to the objects that were scaffolded.
First, rename the ef_TableName entities to just TableName; if there is no direct table access, keeping the names similar adds to the readability of the code. Next, you will note that all non-null properties are marked as Entity Key. When importing views, the Entity Framework cannot correctly determine the Entity Key so it automatically selects all non-null columns, so uncheck any Entity Key that is not the primary key of the underlying structure.
Now that the objects are cleaned up, we can begin the stored procedure mapping. Right-click on Author and select Stored Procedure Mapping. In the Mapping Details window, start by selecting the ef_Author_Insert stored procedure for the insert stored procedure mapping. Repeat the steps for the update and delete mappings. Finally, ensure that the insert stored procedure mapping includes the result column binding of AUTHOR_ID and that it maps to the AUTHOR_ID property on the entity object.
Repeat this stored procedure mapping for the Book entity object. Ensure that the insert stored procedure maps both the BOOK_ID and CREATED_ON properties as Result Column Bindings and that the updated stored procedure mapping maps the BOOK_UPDATED property.
And that's it. Your ADO.NET Entity Model is ready to work with entity objects whose back-end functions rely on database views and stored procedures. In your code, you can work with the DbContext objects just like you would with the direct table access method using code-first.
using (SandboxEntities dbContext = new SandboxEntities())
{
Author author = new Author()
{
AUTHOR_DISPLAY_NAME = "J. R. R. Tolkien"
};
dbContext.Authors.Add(author);
dbContext.SaveChanges();
Book book = new Book()
{
AUTHOR_ID = author.AUTHOR_ID,
BOOK_TITLE = "The Hobbit",
BOOK_ISBN = "978-0261102002",
BOOK_RELEASE_DATE = new DateTime(1991, 1, 1), // HarperCollins edition
BOOK_TOTAL_PAGES = 288,
BOOK_SUMMARY = "A very small person goes on a big adventure."
};
dbContext.Books.Add(book);
dbContext.SaveChanges();
}
In addition to working with views and stored procedures, LINQ-to-SQL functions will still behave as expected for LINQ extension methods like FirstOrDefault, Where, OrderBy, etc. Further, because the Entity Keys are correctly mapped, the DbContext can still leverage entity states and return identity values from database-created identity clauses and default constraints. The only real loss in this approach is that Navigation Properties are not automatically created between your CRUD entity objects, however there are ways to add them dynamically at runtime (or by editing the EDMX files XML manually).
Conclusion
In this code sample, we learned how to leverage an abstraction layer at the database-level while maintaining the normal use of the Microsoft Entity Framework ORM library. In the scenario where a developer is unable to access tables directly due to security concerns or to insulate the application code from database schema modifications, software developers can still interact with the DbContext as though they were direct table access. Database administrators can rest-assured that their database tables are protected with code they can secure, and as a benefit, the back-end developers data access layer code can still work, provided the DBAs make sure to update the views and stored procedures when they do.