Entity Framework Modeling: Action Stored Procedures

 

Entity Framework Modeling: Action Stored Procedures

One of the biggest concerns I hear when discussing the Entity Framework is that of security.  People are initially impressed and excited about the ease of developing a conceptual layer.  Thanks to the magic of the Entity Framework and object services, you have an object layer that you can interact with that is mapped to your database.  The excitement, however, wears off when people figure out that the default behavior for the action queries (insert, update and delete), along with selects is to generate dynamic SQL.  In my humble opinion, this is a viable concern.  In this post, I will discuss the security concerns with this default behavior and illustrate how you can map stored procedures to the actions.

View the Screencast

You can watch the screencast here.

Index of Related Entity Framework Blog Posts and Screencasts

Here is an index of other blog posts and screencasts I have done on this subject.

The benefits of SPs over Dynamic SQL

You are, no doubt, aware of the security benefits of using stored procedures.  SPs are parameterized (unless you create dynamic SQL within the proc) and are therefore not susceptible to SQL Injection attacks.   Further, you can limit the damage a user account can do by just assigning them execute permissions on specific stored procedures.  You do not need to give them any permissions to the underlying tables.  So, how does this compare to dynamic SQL?

Again, most of you know that parameterized SQL statements are also not susceptible to SQL Injection attacks, so on this account they are equal (well stored procs force you to parameterize so maybe not absolutely equal).  The SQL generated by the EF are parameterized and are therefore safe in this regard.  I’ll illustrate that in the next section.

The big difference between the two approaches is assigning permissions.  For stored procedures, all you need to do is grant Execute permissions for the SP.  However, for SQL statements, you must grant permissions on the underlying table or tables (see below).  That can be quite scary.  Whereas a stored procedure can limit a users capabilities to only deleting specific records (perhaps only those associated with them), delete permissions on the underlying table give you no such control.  It should be crystal clear why the use of stored procedures for actionable queries are preferable.

vs.

Creating the Entity Data Model

I’m going to start with creating an Entity Data Model and pointing out the default behavior.  For the purposes of this post, I am going to use a very simple Entity Data Model.  I will have one entity: Person that is mapped to a Person table.  I am using this table, as it has only a few required fields and will be easy to demo with.

Step 1: Add the Entity Data Model

Step 2: Generate the Model From The Database

Step 3: Choose the source table

We only need the Person table here. 

Step 4: Name the Person entity appropriately

You should give the entities and entity set appropriate names.  I named the entity Person and changed the EntitySet name to People.  The easiest way to rename the entities and entity sets is to single-click on the entity in the designer and make the updates in the properties pane:

Examining the Default Behavior for Actions

I’m going to put together some code that, along with SQL Profiler, will illustrate the default behavior.  The following is the code in a console app that inserts a new Person, utilizing the entity we just built.

Code:

Profiler Result:

As you can see, the default behavior is for the framework to generate some SQL dynamically and execute it.  You can further see that the generated SQL is parameterized and you are safe from SQL Injection attacks.  However, the user under which this sql is executing must have insert permissions on the table.  For delete actions, the user must have delete permissions on the Person table, and so forth.

Mapping Stored Procedures To Your Entities

Well, now that we know about the benefits of stored procedures and we have seen the default behavior, let’s illustrate how to map some stored procedures to your entities.  Let’s get started:

Step 1: Write the Stored Procedures

Insert:

Update:

Delete:

Step 2: Import the procedures

In the model browser, right-click and choose to update the model from the database

Add the 3 procedures we just wrote:

Step 3: Map the entities to the procedures

Bring up the stored procedure mapping

Map the appropriate procedure to the appropriate function.  Seen below, I am mappting the spAddPerson procedure to the Insert Function.

Because the parameters for the stored procedure line up with the property names on the entity, the designer will map everything appropriately.  If there are naming differences, you simply need to update the map. 

You have to do one other thing before you are done.  In our data model, the Person table has an identity column: PersonID.  The identity is assigned by the database when a row in inserted.  That is why our stored procedure has the clause: SELECT SCOPE_IDENTITY() as Id.  We need to map this result column to the PersonID on our entity.  That way, when the entity is added to the database, the new PersonID is then reflected in the entity.

The insert in the hardest of the 3.  Simply map the spDelPerson to the Delete function and spUpdPerson to the Update function.  The properties are named appropriately, so they will line up.

That is it!

Running the Insert Again

I’m ran the exact same code as before.  Here is what Profiler showed now:

Conclusion

As you can see, it was quite simple to map the actions to stored procedures.  There are no changes required to the calling code.  You simply map the procs to the appropriate action in each entity.  This simple functionality should go a long way toward making you feel comfortable taking advantage of the Entity Framework.  In my next post, I will illustrate how you can map select stored procedures into the entity framework.

Published Thursday, March 12, 2009 8:25 PM by rob.bagby

Demystifying The Code : Entity Framework Modeling: Action Stored Procedures