I wanted to learn about the Entity Framework using some basic database patterns:
to see how the Entity Framework handles these relationship patterns.
To use the Entity Framework and related wizards, you have to have VS2008 SP1 and .NET 3.5 SP1 installed.
Basic information on creating a project that uses the Entity Framework:
Actually, I'm going to be creating the data model in steps. When all is said and done, we should have a good understanding of how the Entity Framework works with some basic database architecture patterns.
First, we'll start off with a simple PersonName table that includes an ID, FirstName and LastName fields. This table will be used for demonstrating using the Entity Framework with a simple table.
The Person table will contain the ID, Gender, and PersonNameID fields, and will demonstrate a simple one-to-one relationship in which one Person references one PersonName. The idea here is to explore how the Entity Framework handles "including" or "joining" two tables.
I'm going to change the PersonName table later to have a PersonID field to establish a one-to-many relationship so we can support the concept that a Person can have several names: maiden name, real name, aliases, etc. Put another way, I'll be creating a master-detail relationship and seeing how that works in the Entity Framework.
The Location table will consist of the ID, StreetNumber, StreetName, and Zip fields. There will also be a PersonLocationAssociation table to establish a many-to-many association between Person and Location. For example, several people can "live at" a location. An office has many people that "work at" that location. The initial association table will simply have ID, PersonID, and LocationID fields.
So, to begin, let's create a form for a simple PersonName table. In your favorite database (one supported by the Entity Framework, harhar), create a new database and add the PersonName table:
| ID | integer, primary key, not nullable, identity |
| FirstName | nvarchar(64), not nullable |
| LastName | nvarchar(64), not nullable |
Remember that ID is an identity value. All the ID fields in tables we're going to create are designated as identity values.
These next two steps are very important:
Finally:
A bunch of references will be added to your project; after generating the code, a diagram of your data model (showing just the PersonName table) will be displayed.
Now, let's create a simple user interface to edit the PersonName table.
DataGridView to the form and name it "dgvPersonName". Load event handler. using statements at the top of the code file:
using System.Data.Objects;
using System.Data.Objects.DataClasses;
protected PersonNameEntities personNameContext;
personNameContext field in the form's constructor after the InitializeComponent() call:
public Form1()
{
InitializeComponent();
personNameContext = new PersonNameEntities();
}
Load event handler, add a few lines of code so it looks like this:
private void Form1_Load(object sender, EventArgs e)
{
ObjectQuery<PersonName> personNameQuery = personNameContext.PersonName;
dgvPersonName.DataSource = personNameQuery;
dgvPersonName.Columns["ID"].Visible = false;
}
We're hiding the ID because this is an identity field assigned by the database.
Click event handler that calls SaveChanges() for personNameContext.
private void btnSave_Click(object sender, EventArgs e)
{
personNameContext.SaveChanges();
}
You should have something similar to the above, where you can fill in names, edit them, delete them, and so forth, and save your changes to the database.
OK, this is pretty nifty, as I haven't had to write a single SQL statement in this, albeit, it being a two-tier application.
PersonNameEntities is auto-generated and derived from ObjectContext. What is this ObjectContext class? Well, MSDN probably says it best:
"Provides facilities for querying and working with entity data as objects."
and:
The ObjectContext class is the primary class for interacting with data as objects that are instances of entity types that are defined in an Entity Data Model (EDM). An instance of the ObjectContext class encapsulates the following:
EntityConnection object. MetadataWorkspace object. ObjectStateManager object that manages objects persisted in the cache. The auto-generated subclass, PersonNameEntities, has a useful method and property:
AddToPersonName(PersonName personName)
which you would use to programmatically add a PersonName to the collection managed by the context. What I find a little odd is that there isn't a "RemovePersonName" method, although the base ObjectContext class does have a DeleteObject(object entity) method for programmatically marking entities for deletion.
There is also a getter property for PersonName, which we used in this line:
ObjectQuery<PersonName> personNameQuery = personNameContext.PersonName;
The ObjectQuery class represents a typed query, whose type is the class specified in the generic parameter. Of note is that the data is not loaded until the object is actually used in a foreach statement, assigned to fill a list collection, or when you explicitly call the Execute method. Also of note is that the ObjectQuery class implements IListSource, so it can be used as a data source for a list control like a grid, which is what we're doing in the next line:
dgvPersonName.DataSource = personNameQuery;
Finally, the table fields are exposed as properties in the PersonName class (which was generated for us as well), so you can take advantage of strong typing and the field names when accessing the fields in code.
In this section, I will look at how a one-to-one association works. This is not a common database association pattern, but it's useful to look at because it has similarities to how a view would work with the Entity Framework.
Delete out the data in the PersonName table by executing this SQL statement (or use the app above to delete all the rows!):
delete from PersonName
Now, let's create a Person table that has a one-to-one relationship with the PersonName table:
| ID | integer, primary key, identity, not nullable |
| PersonNameID | integer, foreign key to PersonName.ID, not nullable |
| Gender | char(1), not nullable |
It's a bit contrived, but will suit our purposes. Let's add a name directly with SQL:
insert into personname (firstname, lastname) values ('Marc', 'Clifton')
select * from personname
Using the ID that is returned from the PersonName table, populate the Person table:
insert into person (gender, personnameid) values ('M', 12)
Replace "12" with whatever the correct ID is.
Create a new WinForms application called PersonPersonName, following the steps above. This time however, when you select the tables, select both the Person and PersonName tables. Add the code to initialize the context as we did above.
Add a grid view to the form. Since this is a one-to-one association, we want to be able to edit the name and gender on a single row of the grid, and we want the Entity Framework to handle the separate CRUD operations (or at least, that's the idea.)
As before, double-click on the form to create the Load event handler.
Now, we can load the data in a couple different ways. For example, we can suck in the contents of both tables and do the join in LINQ. This obviously isn't the preferred way of doing a join, because you get everything from both tables then do the join on the client side, rather than having the database do the join and return only the qualified rows.
private void Form1_Load(object sender, EventArgs e)
{
ObjectQuery<Person> person = ppnContext.Person;
ObjectQuery<PersonName> personName = ppnContext.PersonName;
var ppnQuery = from p in person
join pn in personName
on p.PersonName.ID equals pn.ID
select new { p.Gender, pn.FirstName, pn.LastName };
dgvPersonPersonName.DataSource = ppnQuery;
}
You can also write it like this:
private void Form1_Load(object sender, EventArgs e)
{
var ppnQuery=ppnContext.Person.Join(
ppnContext.PersonName,
person => person.PersonName,
personName => personName,
(person, personName) => new
{
Gender=person.Gender,
LastName=personName.LastName,
FirstName=personName.FirstName});
dgvPersonPersonName.DataSource = ppnQuery;
}
Now, is the database doing the join for you, or does this just decompose to something similar to the first example? I have no idea! There is a debugger visualizer that Scott Gu has posted on his blog; however, I'm using a Vista-64 and it appears not to work. Someone else posted a comment regarding this problem as well. Since I'm using SQL 2005 Express, I don't have a profiler. When I get this set up with SQL 2005 or SQL 2008, I'll update the article with more information on the SQL statement that the server is executing.
Notice that the DataGridView control is in a read-only state:
Because the resulting data set is constructed by joining two tables, we can no longer edit (change, insert, or delete) the data using the control's edit capabilities. I've dealt with this issue successfully in other frameworks, and I'm disappointed that the Entity Framework does not provide the smarts to provide you with an editable data set when the data set is constructed from a join statement(s).
So, let's modify our UI so we can add, edit, and delete rows "manually". To begin, we will add a BindingSource so we can get the currently selected row and modify the query so that we get something more concrete than an anonymous type, which means we need to define a class for the Person-PersonName record:
public class PPN
{
public string Gender { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Person PersonObject { get; set; }
public PersonName PersonNameObject {get; set;}
}
The query is modified to return an IQueryable<PPN> collection, assign that collection to a BindingSource, which is itself assigned to the DataGridView control.
private void Form1_Load(object sender, EventArgs e)
{
IQueryable<PPN> ppnQuery=ppnContext.Person.Join(
ppnContext.PersonName,
person => person.PersonName,
personName => personName,
(person, personName) => new PPN()
{
Gender=person.Gender,
LastName=personName.LastName,
FirstName = personName.FirstName,
PersonObject=person,
PersonNameObject=personName
}).AsQueryable();
bsppn.DataSource = ppnQuery;
dgvPersonPersonName.DataSource = bsppn;
dgvPersonPersonName.Columns["PersonObject"].Visible = false;
dgvPersonPersonName.Columns["PersonNameObject"].Visible = false;
}
We can now delete the selected record when the user clicks on the "Delete" button (which we added to our UI):
private void btnDelete_Click(object sender, EventArgs e)
{
if (bsppn.Current != null)
{
PPN current = bsppn.Current as PPN;
ppnContext.DeleteObject(current.PersonObject);
ppnContext.DeleteObject(current.PersonNameObject);
ppnContext.SaveChanges();
Form1_Load(null, EventArgs.Empty);
}
}
The above quote acquires the PPN record from the binding source, deletes the Person and PersonName records, and then saves the changes. The form data is reloaded. This is not a best practice, but I've implemented it this way so that you can see the change reflected in the DataGridView.
If you think about what is going on in the above handler, this seems like it would have been more natural:
ppnContext.DeleteObject(current.PersonObject);
ppnContext.DeleteObject(current.PersonObject.PersonName);
However, PersonObject.PersonName is null. Similarly, one might imagine that deleting current.PersonObject would be sufficient, expecting perhaps that the Entity Framework will perform a cascading delete. But, since PersonObject.PersonName is null, we can imagine that a cascading delete probably shouldn't work. In fact, this gives us a clue that we aren't really using the Entity Framework with best practices in mind--it seems that behind the scenes, the Entity Framework is still acquiring the records of both tables independently and joining them at the client side.
At this point, you probably have already asked, "Why not use a View?" The answer (for now) is that I want to keep things as "simple" (even if that means "stupid") as possible. If I use a View, it's also probably a best practice to use Stored Procedures for the CRUD operations against that View. Instead, I want to explore the capabilities of the Entity Framework as much as possible.
Later on, I'll be looking at real master-detail relationships and the Include(string path) method rather than the Join<> operation.
As the DataGridView control is in a read-only state, I created separate controls to add a new record:
The code is simple enough (again, not necessarily representative of a best practice):
private void btnAdd_Click(object sender, EventArgs e)
{
PersonName pn = new PersonName();
pn.FirstName = tbFirstName.Text;
pn.LastName = tbLastName.Text;
Person p = new Person();
p.Gender = tbGender.Text;
p.PersonName = pn;
ppnContext.AddToPerson(p);
ppnContext.SaveChanges();
Form1_Load(null, EventArgs.Empty);
}
Note that I'm assigning the PersonName instance to p.PersonName, as the Entity Framework then knows about the association, so I don't have to deal with the primary and foreign keys.
Let's add some simple data binding to the TextBox controls (by this time, I've refactored the Form Load event, so the binding only happens once):
private void Form1_Load(object sender, EventArgs e)
{
LoadData();
tbGender.DataBindings.Add(new Binding("Text", bsppn, "Gender"));
tbFirstName.DataBindings.Add(new Binding("Text", bsppn, "FirstName"));
tbLastName.DataBindings.Add(new Binding("Text", bsppn, "LastName"));
}
As the DataGridView control is in a read-only state, the data binding is one way. This means we have to manually update the Person and PersonName objects of the selected row:
private void btnUpdate_Click(object sender, EventArgs e)
{
if (bsppn.Current != null)
{
PPN current = bsppn.Current as PPN;
current.PersonObject.Gender = tbGender.Text;
current.PersonNameObject.FirstName = tbFirstName.Text;
current.PersonNameObject.LastName = tbLastName.Text;
ppnContext.SaveChanges();
LoadData();
}
}
A more common association pattern is the one-to-many association. Here, I will look at creating this pattern between Person and PersonName by reversing the foreign key field, so that a Person can have many names, like maiden name, alias, and so forth.
Change the model so that PersonName has a PersonID which is a foreign key to the Person.ID table. Remove the PersonNameID field from the Person table.
Also, create a foreign key associating PersonName.PersonID with Person.ID.
As previously, create a WinForms application and add the Entity Framework information for the Person and PersonName tables using the ADO.NET Data Model Wizard.
Once you've done this, notice how the Person.PersonName property is a collection! The Entity Framework has correctly understood that the relationship between Person and PersonName is a one-to-many relationship, and creates a PersonName collection allowing us to associate many names to one person.
For this example, I created two DataGridView controls, one for the master (Person) data, and the other for the detail (PersonName) data. A "Save" button saves the context, and as usual, the Form Load event is used to load the data set. Notice, when we run the application, that both grids are editable:
The Load event handler looks like this:
private void Form1_Load(object sender, EventArgs e)
{
ObjectQuery<Person> personQuery = mpnContext.Person.Include("PersonName");
bsPerson.DataSource = personQuery;
dgvPerson.DataSource = bsPerson;
dgvPerson.Columns["ID"].Visible = false;
dgvPerson.Columns["PersonName"].Visible = false;
dgvPersonName.DataBindings.Add(new Binding("DataSource",
bsPerson, "PersonName"));
dgvPersonName.Columns["ID"].Visible = false;
dgvPersonName.Columns["Person"].Visible = false;
}
Notice the use of the Include(string path) method, which loads the data into a master-detail object model, meaning that each Person instance has a collection of PersonName objects specific to that association.
A BindingSource tracks the currently selected row in the person DataGridView, and this binding source is used to dynamically bind the Person.PersonName collection with the detail DataGridView control.
This is all that is necessary to add, edit, and delete rows (caveat, see cascading deletes next) in this master-detail model, which is certainly nifty. However, when using a highly normalized database, it's unrealistic to assume that a master-detail relationship can be represented by one master table and one detail table. Rather, the master and detail are probably Views. This makes working with a master-detail data set more complex, as we saw above.
If we try to delete a master record and save the results, the following exception occurs (this assumes you did not specify "Cascade" for the OnDelete rule of the foreign key):
What we really want is to cascade the delete. We "should have" specified Cascade for the OnDelete rule of the foreign key, and then this would have generated the correct EDM. So instead, let's edit the EDM manually.
OnDelete rule for the foreign key to Cascade Association tag in the SSDL portion of the EDM, and modify the End tag, adding the cascade rule:
...
<Association Name="FK_PersonName_Person">
<End Role="Person" Type="ManyPersonNameModel.Store.Person" Multiplicity="1">
<OnDelete Action="Cascade"></OnDelete>
</End>
<End Role="PersonName"
Type="ManyPersonNameModel.Store.PersonName" Multiplicity="*" />
...
Now, we can delete master records without generating an error, and the detail records associated with the master will be automatically removed.
Integrity checking often depends on the context in which the application is run. For example, a standard user should probably not be able to remove Person records if there are associated detail records. However, a super-user or a high level administrator probably ought to have this capability. Implementing a cascade rule in the database and the EDM doesn't capture the context information that makes this determination. Yes, one can do this programmatically. But, do you really think doing this programmatically is the best approach when you're designing an enterprise architecture application? Personally, I think not.
In this last example, we'll look at a many-to-many association, between PersonName and a new table, Location, and the PersonNameLocationAssociation table.
Modify PersonName, removing the PersonID column, since we don't want to confuse the issue by requiring a Person record.
The Location table looks like this:
| ID | integer, primary key, identity |
| Number | nvarchar(16), not nullable |
| Street | nvarchar(64), not nullable |
| Zip | nvarchar(5), not nullable |
The PersonNameLocationAssocation table is defined as:
| PersonNameID | integer, foreign key to PersonName.ID, cascade on delete |
| LocationID | integer, foreign key to Location.ID, cascade on delete |
The composite primary key is defined consisting of both fields.
This should be pretty routine by now. Create a WinForms application and use the ADO.NET Entity Data Model wizard to generate the classes for the PersonName, Location, and PersonNameLocationAssociation tables.
Notice that the wizard generates an entity data model that consists only of the PersonName and Location tables:
This is an artifact of the association table: it contains only the foreign keys. Later, I'll add another field (the association type) and we'll look at the EDM again. Also notice the "Navigation Properties"--we can link the Location direction to PersonName, and vice versa, without explicitly going through the association table.
This is a sort of cute shortcut, but is it really practical? In my experience, an association table will have additional properties about the association, perhaps describing the association type, the begin and end dates of the association, and so forth. So realistically, this shortcut is not something I would expect to be able to take advantage of in the real world.
The UI I created for this is rather simplistic, allowing you to create PersonName and Location records, and then associate them by selecting a row in each grid and clicking on the "Associate" button:
The Form Load event handler should look familiar:
private void Form1_Load(object sender, EventArgs e)
{
ObjectQuery<PersonName> personQuery = context.PersonName;
ObjectQuery<Location> locationQuery = context.Location;
bsPersonName.DataSource = personQuery;
bsLocation.DataSource = locationQuery;
dgvPersonName.DataSource = bsPersonName;
dgvLocation.DataSource = bsLocation;
dgvPersonName.Columns["ID"].Visible = false;
dgvPersonName.Columns["Location"].Visible = false;
dgvLocation.Columns["ID"].Visible = false;
dgvLocation.Columns["PersonName"].Visible = false;
}
The Associate button event handler creates the association between the selected records:
private void btnAssociate_Click(object sender, EventArgs e)
{
if ((bsLocation.Current != null) && (bsPersonName.Current != null))
{
PersonName pn = (PersonName)bsPersonName.Current;
Location loc = (Location)bsLocation.Current;
pn.Location.Add(loc);
loc.PersonName.Add(pn);
context.SaveChanges();
}
}
As you can see, I've used a BindingSource to manage the currency (the selected row) in the two DataGridView controls.
After making a couple associations, we can verify that they were saved by querying the association table:
So, that's straightforward.
The last thing I want to look at in this article is, what happens when you add some property fields to the association. For example, let's add the AssocType field (I'm really lazy, so we'll make an nvarchar(64)) to the PersonNameLocationAssociation table, so we can enter some information about the association, like "lives at", "works at", and so forth.
Delete the .edmx file from the project and use the ADO.NET Entity Data Model wizard to add the PersonName, Location, and PersonNameLocationAssociation tables again (I don't see an easy way of updating an existing EDM).
Notice that now the association table is explicitly included in the EDM:
To make the association, we now have to work with all three entities:
private void btnAssociate_Click(object sender, EventArgs e)
{
if ((bsLocation.Current != null) && (bsPersonName.Current != null))
{
PersonName pn = (PersonName)bsPersonName.Current;
Location loc = (Location)bsLocation.Current;
PersonNameLocationAssociation assoc = new PersonNameLocationAssociation();
assoc.PersonName = pn;
assoc.Location = loc;
assoc.AssocType = "Lives At";
pn.PersonNameLocationAssociation.Add(assoc);
loc.PersonNameLocationAssociation.Add(assoc);
context.SaveChanges();
}
}
Yes, I hard-coded the AssocType value. However, you can see from the above code that it's easy to work with an explicit association table. Oddly, I didn't have to add the association to the context's collection:
context.AddToPersonNameLocationAssociation(assoc);
so there must be some behind-the-scenes magic going on.
The code that is generated includes factory methods, such as:
public static PersonName CreatePersonName(int id, string firstName, string lastName)
but notice that these methods include the table's ID, which is pointless (or supposedly so) because the IDs, at least in the data model I've been using, are identity fields, so they're assigned by the server, not the client. I did read something about the values being temporarily used in relationships, but it would seem unnecessary.
The factory method for the association table seems particularly useless as it requires the ID values of the associated tables (again, assigned by the server in my model, as they are identity fields):
public static PersonNameLocationAssociation
CreatePersonNameLocationAssociation(
int personNameID,
int locationID,
string assocType)
Other things that might be interesting to look at in future articles:
Basically, the common things one would have to deal with in an enterprise architecture, all of which touch upon the data service architecture, in this case being the Entity Framework.
So, that's an introduction on the Entity Framework with regards to different database association patterns. Being new to the Entity Framework, I hope that I haven't made any gross errors, but if I have, please leave a comment and I'll update the article. The Entity Framework seems to be pretty easy to use for basic stuff, and hopefully, Microsoft will address my pet peeve, not being able to update a joined data set easily, in some future release. Another problem that bothers me is not being able to easily view the generated SQL. And of course, I feel like I've just scratched the surface in determining whether the Entity Framework is a viable technology, and the list above seems rather daunting.
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
