In my web development days, when I sat down to start coding a project, one of the tasks I dreaded was writing my own data access objects/layer. Sure there was ADODB and other interesting libraries like Propel, Cake, Ruby on Rails. I could have also copied my DAL between projects and customized them. But for the most part I just wanted to take my database and shove it into my program: “Look! Here’s my relational data, just create all the objects I may or may not need, let me tweak them if I want, and let me get on with the actual project!”.
I also grappled with visualizing exactly how I would deal with my relational data as objects in my code (I got really good at sketching boxes/lines on paper).
The Entity Framework and the Entity Data Model Designer attack both of these issues head-on by allowing .NET developers to visualize and interact with a conceptual model that is generated from and mapped to a database structure.
This article is the first in a series of articles intended to demonstrate some of the common features in the designer by walking through an end-to-end experience of developing a chat program. It will be geared towards first-time users of the EDM Designer but hopefully it will clear up any confusion among those who have already been using it. I’ll try not to go too much into detail about the Entity Framework as there are already many resources out there for you. I will provide links for those resources at the end.
Pre-requisites
Software:
Background:
- Visual Studio
- C#
- Some SQL/database knowledge
Model generation
The first thing we’ll do is create a simple database called chatdb through Sql Server Management Studio Express. You can execute a SQL script I’ve written up against the database (just hit the ‘New Query’ button and execute).
There are four tables here:
- users: everyone who chats has a username, and some users are moderators
- messages: any message created by a user is persisted here. A user can create multiple messages.
- profiles: a user has a single profile
- chats: this can be thought of as a ‘chat room’, containing many messages.
- users2chats: a chat room can have many users, and many users can belong to a chat room, so this is a “link table” that specifies what user is associated with what chat room at a given moment.
Fire up Visual Studio and create a new C# console project (I called mine ChatDemo). Right-click on the project’s root and select “Add -> New Item”. Select “ADO.NET Entity Data Model” and choose a name for your model. I went with ChatModel.edmx. After this, you’ll see our wizard.
We want to create classes from an existing database so choose “Generate from database” and click Next. On the next page, click on ‘New Connection’. If you are running the database from sql server express edition, type in “.\sqlexpress” for the server name and chatdb for the database name. Click OK to save the connection properties. Take a look at the Entity connection string that appears on the second page of the wizard. When your program interacts with the objects (known as Entity Types) generated from the Entity Framework, the EF will use this connection string to modify the database. Leave the entity connection settings and checkbox as it is and select Next.
Select all the tables on the next page and leave the Model Namespace as it is. Click Finish.
Boxes and lines revisited
If everything proceeds correctly the designer appears with four entity types and four correct associations between them. So what happened?
- First, a connection was made to the database and the Entity Framework obtained all the selected tables/views/stored procedures. The designer first used the framework to analyze the database and generate SSDL (Store Schema Definition Language) that is essentially a serialization of the database structure.
- The designer makes another call to the framework to generate CSDL (Conceptual Schema Definition Language).
- Finally, the mapping layer (MSL – Mapping Specification Language) is generated from the CSDL and the SSDL.
- These three schemas are grouped together and placed in the single ChatModel.edmx file that you see in the Solution Explorer in Visual Studio.
- The designer creates a diagram from the CSDL and stores the information for this diagram in the EDMX file as well. You can view the entire EDMX file in its raw XML-based form by right-clicking the file and selecting “Open With -> XML Editor”. You’ll notice the SSDL first, followed by the CSDL, the MSL, and finally the diagram information.
- Finally, the designer calls a code-generation process in the Entity Framework and generates C# or Visual Basic code. This code-behind file is ChatModel.Designer.cs and contains the actual classes that you will create object instances from to create/update/delete/retrieve messages, users, etc.
Understanding Mapping Details
Conceptual Associations were inferred based on foreign keys in the database.
Here’s something cool – Notice in our database that we have a table called users2chats but it doesn’t show up anywhere on the designer surface. That’s because it is a ‘link table’ – the user_id column is a foreign key to the users table and the chat_id column is a foreign key to the chats table. The Entity Framework identified this and automatically constructed a many-to-many relationship between the users and chats table.
A “Navigation Property” is an Entity Framework construct that corresponds to that foreign key. For example, to access the many Users associated with a Chat, in our eventual C# code that we will write we can retrieve all the users in a particular chat by calling: “chat.users”. Click on the association between the chats Entity Type and the users Entity Type and take a look at the Mapping Details tool window:
The Mapping Details window is a visualizer for the MSL. Currently it is showing how the conceptual Association is mapped. Because the users2chats table has both foreign keys, the association is mapped to the users2chats table. The Column corresponds to the database table’s column that exists in the SSDL and the Property corresponds to the conceptual Entity Type’s Property in the CSDL. Now click on the association between messages and chats and take a look at the Property window:
An Association is defined by two “Ends” which are references to Entity Types. You can edit various properties of the Association here including the name. One caveat is editing multiplicity – we can certainly change this many-to-one association into a 1-to-1 association. However, we can’t change it to a many-to-many association because our database structure doesn’t support it.
Changing the other properties works because the designer only changes the conceptual model and retains the mapping that binds the conceptual model (which we are changing through the designer) to the unchanging storage model. So here we start to see the power of the Entity Framework – we can directly change how we want to interact with the objects corresponding to the relational data without changing the database structure at all. This will come into play when we start talking about more complicated scenarios, such as defining inheritance between Entity Types so we can scope out how we want to interact with our objects.
Fixing Up Our Conceptual Model
From here we have a very intuitive, birds-eye view of how we want to interact with the data. There are some assumptions that the Entity Framework’s model generation made (and did not make) that perhaps we would like to tweak. For example, notice that there is a 1-to-many association between users and profiles , even though we defined a unique key constraint on the userId foreign key in the profiles table in our database. Ideally this should have created a 1 -> 0..1 association between users and profiles in our conceptual model since we can never have one user with many profiles. Semantically, our program could certainly allow users to have multiple profiles, but for simplicity’s sake we’ll modify this a little :). We can easily change the other End’s multiplicity using the property window information I described above. However, for the purposes of demonstration, we’ll take a different approach.
We will basically delete the profiles EntityType and re-create it, its 1:1 association with users and re-map it to our SSDL.
- Go ahead and click on the profiles EntityType and hit the Delete key on your keyboard. Notice that the association disappeared as well.
- The designer has a toolbox (View->Toolbox). You can double-click the “Entity” button to drop an EntityType on the designer surface. This EntityType has a default name and a primary key. You can also right-click on a empty area on the designer surface and select Add->Entity.
- Notice that there is no mapping for this EntityType in the Mapping Details view. Take a look at the Model Browser in the designer. This provides another view of all the conceptual EntityTypes and Associations on the designer surface as well as the SSDL types. This tool is usually in the same pane as the “Solution Explorer”. If it’s not there, right-click on any EntityType and select “Show in Model Browser”:
We will map Entity1 in our conceptual model to the selected profiles table in the storage model through the Mapping Details view. Click back on Entity1 in the designer and name this EntityType: "Profile" through the Property Window (or you can hit F2 while selected on the EntityType).
- There are two modes for the Mapping Details view: one is to map an EntityType against a table in the database and another is to map it to functions (for example, when inserting a new "Profile" into the database, we might want to run a function that will automatically insert it, possibly add some information to another table, and return us the ID of the newly inserted table). For now we won’t worry about Function Mapping. Click on “Add a new table or view” and select : "profiles" :
- So the designer already mapped the primary key of the table to the default primary key conceptual property Id. We don’t have the other two properties mapped. Right-click on the Profile EntityType and select Add -> Scalar Property. Rename this property to "ProfileText". Since the profileText field in the table is of type ‘nvarchar’, in the Property Window, set the Type of the conceptual property ProfileText to be String. Now in the Mapping Details window for the Profile EntityType, map “profileText : nvarchar” storage scalar property to the conceptual ProfileText property that you just added. Moving on, "userId" is actually a foreign key, so the corresponding conceptual property should be a Navigation Property – not a Scalar Property – in Profile. We need to add the Association…
- Click on the Association button in the toolbox. Click first on the users EntityType, then click again on the Profile EntityType. A 1-to-many association appears. Look at the Property Window and change the first AssociationEnd’s multiplicity from “Many” to “One”.
- Now we need to map the Association. Here’s the tricky part. Since the profiles table contains the foreign key to users (users doesn’t have a foreign key to profiles), we need to map the Association to the profiles table. Set the users.id property to map to userId in the table, and the Profile.Id property to map to the id field in the table.
- Finally, to make sure that everything is correct, right-click on an empty area on the designer surface and hit “Validate”. If everything goes well, you won’t see any Errors in the Error List.
Naming
Notice that the new Profile EntityType seems more in accordance with how we want to interact with our objects. "profiles" makes sense in the context of relational data since it refers to tuples of profiles in the database. However, in our code we will be constructing instances of class "Profile", and it would have been confusing to leave it as "profiles" as we will only ever refer to a single Profile object and this would break the C# naming convention of having class types capitalized. This is one of the smaller factors in the “impedance mismatch” in object-to-relational mapping. To make life easier for us later, go ahead and “depluralize” (rename/capitalize) the Entity Types ‘chats’ to ‘Chat’, ‘messages’ to ‘Message’, ‘profiles’ to ‘Profile’, and ‘users’ to ‘User’.
Feel free to rename the conceptual properties; take note that we will be directly using them in C# code so it would be good to adhere to C# naming conventions. We will eventually in our code like to say something like (we’ll go into this in later articles):
1: Profile profile = new Profile();
2: profile.ProfileText = "Yes, my new profile rocks.";
Conclusion
Hopefully you’ve got a basic idea of how the Entity Framework, through the EDM Designer, allows us to rapidly develop a data access layer that gives us a good amount of control. In the next article in this series, I’ll demonstrate stored procedure mapping using Mapping Details as well as how to handle different types of Inheritance in the designer. In later series I will also show off the “Update Model” feature, show how to import Functions into the conceptual model, perform data binding, how to work with the classes created to build our final chat program, and maybe even extend it a little :). I want to hit upon anything that is confusing or needs clarification, so please leave your comments and I’ll try to work your suggestions into future articles.
Entity Framework Links:
ADO.NET Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=533&SiteID=1
MSDN Overview: http://msdn.microsoft.com/en-us/data/aa937723.aspx
ADO.NET blog: http://blogs.msdn.com/adonet
Entity Framework reference: http://msdn.microsoft.com/en-us/library/bb399572.aspx
EDM Designer reference: http://msdn.microsoft.com/en-us/library/bb399249.aspx
