70-451 Designing a Database Solution – Entity Framework


Design a data model by using the Entity Framework. This objective may include but is not limited to: defining and maintaining mapping (query vs. stored proc), defining a data model, entity SQL

My Take

Understand the basics of the Entity Framework, such as how where and why it is used. We will also want to understand some of the basics pertaining to the framework relating to how you define the entity model through mapping the physical and conceptual models. Last we would like to understanding one of the many ways you can access the data in the entities through Entity SQL .

A confession, the ADO.NET Entity Framework is something I have looked at briefly at two or three conferences where I poked my head into the developer side of the house. Other than that, we don’t play with Data Access Layers very much while working on the data warehouse, and I wasn’t allowed to play with this much at my last job where I actually used .net. So, everything I present here should be taken with a HUGE grain of salt. I am not an expert on this subject and am really just guessing at what these requirements mean from the somewhat cryptic snippets given above. Furthermore, I am a bit concerned with how much time and effort I should put in to learning this section. I know that it would probably be a good skill to have, but I don’t want to go overboard with something that I might not have to pick up again for some time. So, I am limiting myself to this evening for both looking over the material and typing up the notes.

I have reordered the requirement elements to better describe what I have found.

Defining a Data Model

At this point, I’m really not certain if this is supposed to be going over the information which is relevant to 2008 and .Net 3.5 or 2010 and .Net 4.0. I have gone through and watched a few videos on defining the data model as it relates to 2010, as both a reverse engineering effort from an existing database and as the "model-first" effort.

The data model consists of three parts: The storage model, the conceptual model and the mapping model. The storage model relates to how the data is stored in the underlying database. The conceptual model is how the data will be exposed through the entity data model and the mapping model is how the other two fit together.

As you define the conceptual model, you create entities which will usually relate to tables. These entities might combine one or more tables and might provide constraints or filters on which records in those tables will be represented in the entity. One interesting point is that entities can contain complex types, such as an address type that contains three properties – StreetAddress, City and State. Another interesting area are entities which "inherit" from other entities. This goes back to the whole subtype supertype relationship.

In addition to the entities, you will also create the relationships between entities. In the entities you have created, you can create navigation properties to link the entity objects to the related entity objects. These navigation properties will expose the other entity as either an object or a collection within the object containing the navigation property.

Furthermore, you can set the Insert, Update and Delete through stored procedures, defined queries, or model generated queries. Apparently in 2010 you can map one or more of the Insert, Update and Delete, but in 2008 you had to map none or all three.

Entity SQL

Entity SQL is similar to the T-SQL query language (Using LINQ), this language works against the entities exposed as objects in the entity framework. (See also How Entity SQL Differs from Transact SQL) I think it is probably worthwhile to look over the Entity SQL Quick Reference to get an idea of how things are handled and to browse the Entity SQL Reference.

Defining and Maintaining Mapping (Query vs Stored Procedure)

This is the requirement which I am most concerned about. Unfortunately, I do not know what this is talking about in regards to Query vs. Stored Procedure. I could not find anything concrete on this example that really fit what I would expect in terms of documentation. I would assume that query portion of this comparison relates to combining tables and potentially constraining or filtering these tables to produce entities (The ADO.NET Entity Framework Overview from 2006 does a decent job of explaining this concept). In other words, which would be the better place for the abstraction of the conceptual entity vs the physical tables (or entities) and what would be the trade-offs for the query approach versus the stored procedure approach when we have to maintain these definitions. And this question isn’t even taking into account views or user defined functions that could similarly be considered in this argument.

The data that I am finding which matches the stored procedure portion relates to the visual studio 2008 pages for the entity framework and these are probably where we need to look. For instance, the Stored Procedure Support page has a good table describing the requirements for using sprocs in the EDM for selecting data as well as inserting, updating and deleting… with appropriate links.

The How to define a model with stored procedures page describes the steps which are needed for selecting data using sprocs:

  1. In the store schema definition language file (SSDL) add the function elements to the EntityContainer tag which describes the stored procedure.
  2. In the conceptual schema definition language file (CSDL) add the FunctionImport elements to the EntityContainer tag which also describes the stored procedure.
  3. In the Mapping Specification Language file (MSL) add the FunctionImportMapping element.

I believe that one of the videos that I linked to above also had a (very) brief discussion relating to where it was most appropriate to define this layer of abstraction. It was stated that the EDM was the best place as it could be defined on an application by application basis and that by providing views or sprocs for this function we would potentially be cluttering the underlying database with the various views of the these entities. I am not certain if this is the best argument against storing the information in sprocs or views, but I believe that using the EDM to define the entities is probably more beneficial in the long run… I have no proof for this though.


As a side note, you can download the Entity Framework Documentation Samples for Visual Studio 2008 from codeplex to look through some of the entity framework sample material.

I could almost certainly have taken a much deeper dive into this subject to understand it more fully. However, I want to ensure that I get through all of the material in a reasonable amount of time. I might attempt to circle back to this after I am done with the exam if it continues to stick out in my mind as something I should look in to.

This entry was posted in SQL and tagged , . Bookmark the permalink.

2 Responses to 70-451 Designing a Database Solution – Entity Framework

  1. Mindy says:

    Test Comment

  2. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s