August 2010

Volume 25 Number 08

Data Points - Deny Table Access to the Entity Framework Without Causing a Mutiny

By Julie Lerman | August 2010

One of the first things I hear from database owners when they see the Entity Framework command creation at work is: “What? I have to provide access to my tables?” They react this way because one of the core capabilities of the Entity Framework is generation of SELECT, UPDATE, INSERT and DELETE commands.

In this column, I will give database administrators some insight into how the Entity Framework generates commands and then introduce features that let you limit its access to your database by allowing it to work only with views and stored procedures. And you can do this without impacting application code or alienating the developers on your team.

Exploring Default Command Generation

How does this command generation work? The focal point of the Entity Framework is the Entity Data Model (EDM), a conceptual model that describes an application’s domain objects. The Entity Framework lets developers express queries against the model rather than concern themselves with details of the database. The model, its entities and their relationships are defined as XML, and developers work with strongly typed classes based on the model’s entities. The Entity Framework runtime uses the model’s XML combined with additional metadata (which describes the database schema and mappings to get from the model to the database schema) to bridge the classes with the database (see Figure 1).

Figure 1 The Entity Framework Runtime Metadata Is Used to Build Database Commands

At run time, with the help of database-specific ADO.NET providers, the Entity Framework transforms queries composed against the model into store queries—for example, T-SQL—which it then sends to the database. The Entity Framework transforms the query results into objects defined by the strongly typed entity classes as shown in Figure 2.

Figure 2 The Entity Framework Executes Queries and Processes Their Results

As the user works with these objects, the Entity Framework uses identity keys to track changes to properties as well as relationships between the objects. Finally, when the code calls the Entity Framework SaveChanges method to persist changes back to the database, the Entity Framework runtime reads through all of the change tracking information it has collected. For each entity that has been modified, added or deleted, the Entity Framework once again reads the model and engages the provider to build store commands and then executes them in a single, reversible transaction on the database.

This description of the default behavior of the Entity Framework tends to send database owners out of the room screaming, but I would like to emphasize the word “default” here. The Entity Framework has many default behaviors that can be altered.

How the Entity Framework processes requests to retrieve or persist data is one such behavior that can be modified. You’re not required to build a model that depends on the Entity Framework to have access to your data tables. You can build a model that knows only about your database’s views and stored procedures without impacting the application code that uses the model. By combining the Entity Framework’s stored procedure support with its database view support, you can base all database interaction on stored procedures and views.

Mapping Entities to Database Views, Not Tables

There are a few ways to build a model. I’ll focus on models that are built from a legacy database by reverse-engineering the database. Visual Studio has a wizard for this process.

In the wizard, users can select database tables, views and stored procedures. The stored procedure section also lists scalar-valued, user-defined functions that can be brought into the model.

Typically, a developer will select tables and let the wizard create entities from them. In the change-tracking and SaveChanges process I described earlier, the Entity Framework automatically generates INSERT, UPDATE and DELETE commands for the entities based on tables.

Let’s first take a look at how you can force the Entity Framework to query against views instead of tables.

Database views brought into the model also become entities. The Entity Framework tracks changes to those entities just as it would for entities that are mapped to tables. There’s a caveat about identity keys when using views. A database table will likely have one or more columns marked as its primary key or keys. By default, the wizard will compose an entity’s identity key from a table’s primary key(s). When creating entities that map to views (which lack primary keys), the wizard does its best job of inferring this identity key by building a composite key from all non-nullable values in the table. Consider an entity created from a view that has four non-nullable columns: ContactID, FirstName, LastName and TimeStamp.

 The four resultant properties will be marked as EntityKeys (the designer uses a key icon to indicate EntityKey properties), which means that the entity has an EntityKey composed of these four properties.

The ContactID is the only property that’s needed to uniquely identify this entity. Therefore, after the model has been created, you can use the designer to change the EntityKey attribute of the other three properties to False, leaving only the ContactID as a designated EntityKey.

Alternatively—if it’s possible—you can plan ahead, designing database views that provide the correct, non-nullable columns.

With the key in place, the Entity Framework can uniquely identify each entity and is therefore able to perform change tracking on these entities and then persist changes back to the database when SaveChanges is called.

Overriding Command Generation with Your Own Stored Procedures

For persistence back to the database, you can override the default command generation and instead direct the Entity Framework to use your own Insert, Update and Delete stored procedures when it’s time to persist changes back to the database. This is referred to as “stored procedure mapping.” Let’s take a look at how that works.

Any stored procedure that you select in the EDM Wizard (or subsequently in the Update Wizard) to come into your model becomes a function in the section of the model’s XML metadata that describes the database schema. It isn’t automatically part of the conceptual model and you won’t see any representation of it on the design surface.

Here’s a simple Insert stored procedure for a Person table in one of my databases.

ALTER procedure [dbo].[InsertPerson]

           @FirstName nchar(50),

           @LastName nchar(50),

           @Title nchar(50)

AS

INSERT INTO [Entity FrameworkWorkshop].[dbo].[Person]

           ([FirstName]

           ,[LastName]

           ,[Title]           )

     VALUES

(@FirstName,@LastName,@Title)

SELECT @@IDENTITY as PersonID

This stored procedure not only performs the database insert, it then returns the primary key value that SQL Server has created for the new row.

When you choose this procedure in the wizard, it’s represented in the model’s database schema as the following function:

<Function Name="InsertPerson" Aggregate="false" BuiltIn="false"   

 NiladicFunction="false" IsComposable="false" 

 ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">

  <Parameter Name="FirstName" Type="nchar" Mode="In" />

  <Parameter Name="LastName" Type="nchar" Mode="In" />

  <Parameter Name="Title" Type="nchar" Mode="In" />

</Function>

You can then use the designer’s Mapping Details window to map this InsertPerson function to the Person entity that was created based on the Person table, as shown in Figure 3.

Figure 3 Mapping Stored Procedures to an Entity

Notice that in Figure 3, the PersonID property maps to the return value from the stored procedure. This particular mapping will cause the Entity Framework to update the in-memory Person object with the database-generated key once the insert has been executed in the database.

A critical requirement when mapping functions is that every parameter in the function must map to a property in the entity. You can’t map a formula or a value to the parameters. However, developers have many opportunities to customize the Microsoft .NET Framework classes that represent these entities.

You can also map the Update and Delete functions. While it isn’t necessary to map all three actions (Insert, Update and Delete), developers will have to pay attention to some rules described in the documentation pertaining to mapping only some of the functions.

In Figure 3, notice that there are two columns to the right of property (abbreviated due to column width): Use Original Value and Rows Affected. The Entity Framework supports optimistic concurrency, and you can use these attributes to provide concurrency checking with the Update and Delete functions. Check the MSDN document, “Walkthrough: Mapping an Entity to Stored Procedures (Entity Data Model Tools),” for more information on this feature.

At run time, if a user has created a new Person type and then triggers the SaveChanges method, the Entity Framework will see the Insert function mapping in the metadata (based on the mapping defined in Figure 3). It will send the following command, executing the stored procedure, rather than generating its own INSERT command on the fly:

exec [dbo].[InsertPerson] @FirstName=N'Julie',@LastName=N'Lerman',

@Title=N'Ms.'

Closing the Gap and Preventing Table Access by the Entity Framework

The Entity Framework will generate commands to persist data from view-based entities, but views may not be updatable. In the case of non-updatable views, you can map Insert, Update and Delete stored procedures to the entities and get the full roundtrip of retrieving and persisting data without providing direct access to the database tables.

You could do something as simple as create database views that match the tables and create stored procedures that update the table columns. Or you might have more complex views and complex stored procedures that contain advanced logic for performing updates. You could even supplant some of your read stored procedures with views that will enable developers to compose queries over the views—something that can’t be done against stored procedures.

As an example of this composability, the application could request a query against the CustomersInPastYear entity, filtering the view even further using the customer’s LastName property:

from c in context.CustomersInPastYears

 where c.LastName.StartsWith("B")

 select c;

This results in the following command being executed on the database:

SELECT

[Extent1].[CustomerID] AS [CustomerID], [Extent1].[FirstName] AS [FirstName], 

[Extent1].[LastName] AS [LastName], [Extent1].[EmailAddress] AS [EmailAddress], 

[Extent1].[TimeStamp] AS [TimeStamp]

FROM (SELECT 

      [CustomersInPastYear].[CustomerID] AS [CustomerID], 

      [CustomersInPastYear].[FirstName] AS [FirstName], 

      [CustomersInPastYear].[LastName] AS [LastName], 

      [CustomersInPastYear].[EmailAddress] AS [EmailAddress], 

      [CustomersInPastYear].[TimeStamp] AS [TimeStamp]

      FROM [dbo].[CustomersInPastYear] AS [CustomersInPastYear]) AS [Extent1]

WHERE [Extent1].[LastName] LIKE N'B%'

The .NET compiler would accept a similar query composed over a stored procedure that’s been mapped into the model. However, the Entity Framework would execute the stored procedure on the database, return all of its results to the application and then apply the filter to the in-memory objects returned by the stored procedure. This could potentially waste resources and hurt performance without the developer’s knowledge.

Figure 4shows a stored procedure that updates the Customer table using the same columns that participate in the Customers­InPastYear view. It can be used as the Update function for the CustomersInPastYear entity.

Figure 4 UpdateCustomerFirstNameLastNameEmail Stored Procedure

ALTER PROCEDURE UpdateCustomerFirstNameLastNameEmail

@FirstName nvarchar(50),

@LastName nvarchar(50),

@Email nvarchar(50),

@CustomerId int,

@TimeStamp timestamp



AS



UPDATE Customer

   SET [FirstName] = @FirstName

      ,[LastName] = @LastName

      ,[EmailAddress] = @Email

 WHERE CustomerID=@CustomerId AND TimeStamp=@TimeStamp

 

 SELECT TimeStamp 

 FROM Customer

 WHERE CustomerID=@CustomerId

Now you can map this stored procedure to the entity. The mapping shown in Figure 5 sends the original TimeStamp to the stored procedure and then, using the Result Column Bindings, captures the updated TimeStamp returned by the stored procedure.

Figure 5 Mapping a Stored Procedure to an Entity Based on a View

Wrapping up, as long as the model is designed well, the view-based entities have appropriate identity keys and the functions are properly mapped, there’s no need to expose your database tables to an application that uses the Entity Framework for its data access strategy. Database views and stored procedures can provide the EDM and the Entity Framework all that they need to successfully interact with your database.

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET topics at user groups and conferences around the world. Lerman blogs at thedatafarm.com/blog and is the author of the highly acclaimed book, “Programming Entity Framework” (O’Reilly Media, 2009). Follow her on Twitter.com: julielerman.

Thanks to the following technical experts for reviewing this article: Noam Ben-Ami and Srikanth Mandadi