1. | Entities are usually related, your school may have more than one form, with more than one pupil, and theer may be teachers who are related to both forms and pupils separately. In the database world, these are implemented as foreign key columns on the tables, linking one row to a row on another table by a key. This is represented in the EF world by making one entity a member of another. | |||||||||||||||||||
2. |
As before, we have our Pupil entity, and now we'll create a FormYear entity to represent the class that pupil is in:using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace TestEF.Entity { public class Pupil { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int PupilId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } [Required] public DateTime DateOfBirth { get; set; } [StringLength(200)] public string PrimarySchool { get; set; } } public class FormYear { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int FormYearId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } } }We've used Data Annotations to control the database generation for now. |
|||||||||||||||||||
2. |
As with the pupil entity, we need a DbContext to add it to, we can use the same one:using System.Data; using System.Data.Entity; using System.Data.EntityModel; namespace TestEF.Contexts { public class SchoolContext : DbContext { public DbSet<TestEF.Entity.Pupil> Pupils { get; set; } public DbSet<TestEF.Entity.FormYear> FormYears { get; set; } } } |
|||||||||||||||||||
3. | If we run the code now and consume the SchoolContext.FormYears object, you'll find it creates the FormYears table in the database as expected. But this isn't very useful - we want to be able to add one or more Pupils to the FormYear object, and have that relationship reflected in the database using foreign keys to link the tables. | |||||||||||||||||||
4. |
We can add this relationship by adding a virtual collection of Pupil objects as a property of the FormYear:
public class FormYear { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int FormYearId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } public virtual ICollection<Pupil> Pupils { get; set; } }Wait a minute, that looks too easy! Ignore the virtual for now. All we've done is add a collection of another Entity type to our entity. Believe it or not, that's all you have to do in EF to make a foreign key link in your database. Let's consume it and find out if it works. |
|||||||||||||||||||
5. |
In our code behind or controller, let's create a Pupil, a FormYear, and add the Pupil to the FormYear.
using (TestEF.Contexts.SchoolContext TheSchool = new TestEF.Contexts.SchoolContext()) { // create a couple of pupils Entity.Pupil p1 = new Entity.Pupil(); p1.Name = "John Curran"; p1.DateOfBirth = DateTime.Parse("25/01/1969"); p1.PrimarySchool = "St Denis"; Entity.Pupil p2 = new Entity.Pupil(); p2.Name = "Mark Milarvie"; p2.DateOfBirth = DateTime.Parse("19/07/1969"); p2.PrimarySchool = "St Denis"; // create a form Entity.FormYear f1 = new Entity.FormYear(); f1.Name = "5 Ogilvie"; // add a new list of pupils to the form, and add the pupils f1.Pupils = new List<TestEF.Entity.Pupil>(); f1.Pupils.Add(p1); f1.Pupils.Add(p2); // add the form to the DbContext, then save TheSchool.FormYears.Add(f1); TheSchool.SaveChanges(); }Run this, and then we can look at the database to see what it's done. |
|||||||||||||||||||
6. |
Looking at the FormYears database table we see it's created our form as we expected with the two columms:
But looking at the Pupils database table we see it's created our pupils with the expected 4 columns, but with an extra column we didn't define in the Pupil object:
This extra column is named after our TableName_PrimaryKeyName and for both pupils is set to 1, which is the primary key of the row in the FormYears table these Pupils belong to. Entity Framework has created this database foreign key relationship without us asking. If you look at the structure of the Pupils table you will see it's actually added a physical FK constraint to the table: ALTER TABLE [dbo].[Pupils] WITH CHECK ADD CONSTRAINT [FormYear_Pupils] FOREIGN KEY([FormYears_FormId]) REFERENCES [dbo].[FormYears] ([FormId])We can check this works by creating a second FormYear called f2 in code, and moving one of the pupils from one to the other using Entity.Pupil pmove = f1.Pupils.Single(p => p.Name == "John Curran"); f1.Pupils.Remove(pmove); f2.Pupils.Add(pmove);And then see if the FK has changed to the second one (it does). This demonstrates a one-to-many relationship. Where one objects is linked to many others (contains many others) then the primary key of the container is stored in each many object. This means that each many object knows it's 'parent' directly by knowing its key, but the container object doesn't directly contain references to its children (because we don't know how many there are!). This is standard DB practice. |
|||||||||||||||||||
7. |
One-to-one relationships are done slightly differently. Imagine we have a Teacher object, and we only need one
Teacher per FormYear.
public class Teacher { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int TeacherId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } }And we make sure to add it to the SchoolContext: public class SchoolContext : DbContext { public DbSet<TestEF.Entity.Pupil> Pupils { get; set; } public DbSet<TestEF.Entity.FormYear> FormYears { get; set; } public DbSet<TestEF.Entity.Teacher> Teachers { get; set; } }And we make add a single instance of Teacher (not a collection this time) to the FormYear object: public class FormYear { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int FormYearId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } public virtual ICollection<Pupil> Pupils { get; set; } public virtual Teacher Teacher { get; set; } }And we make sure we create one Teacher, populate it, and add it to the FormYear object in the code: using (TestEF.Contexts.SchoolContext TheSchool = new TestEF.Contexts.SchoolContext()) { // create a couple of pupils Entity.Pupil p1 = new Entity.Pupil(); p1.Name = "John Curran"; p1.DateOfBirth = DateTime.Parse("25/01/1969"); p1.PrimarySchool = "St Denis"; Entity.Pupil p2 = new Entity.Pupil(); p2.Name = "Mark Milarvie"; p2.DateOfBirth = DateTime.Parse("19/07/1969"); p2.PrimarySchool = "St Denis"; // create a teacher Entity.Teacher t1 = new Entity.Teacher(); t1.Name = "Mr John Angry"; // create a form Entity.FormYear f1 = new Entity.FormYear(); f1.Name = "5 Ogilvie"; f1.Teacher = t1; // add a new list of pupils to the form, and add the pupils f1.Pupils = new List<TestEF.Entity.Pupil>(); f1.Pupils.Add(p1); f1.Pupils.Add(p2); // add the form to the DbContext, then save TheSchool.FormYears.Add(f1); TheSchool.SaveChanges(); }When we run this, we see that the Teachers database table is created, but because this is a one-to-one relationship, we don't get an extra column for the container element (the FormYear) as we did with Pupils. Instead, the relationship is defined the other way - the container table stores a reference column to the Teacher - so we look in the FormYear table and note that the reference Foreign Key column is stored here instead.
This has implications for design - if we think a child element might need to be a collection, it's important to make that decision early, because moving from one-to-one to one-to-many completely changes the way the tables are implemented. |
|||||||||||||||||||
8. |
As a final note, sometimes in a relationship you may want to navigate back from a child object to
the parent. Say we had a Pupil object which we'd gotten from a search of Pupils, and we wanted to find out which
FormYear the Pupil belonged to, and thus the Teacher of that Form who teaches the Pupil. At the moment, there is only a link from FormYear to Pupil, not the other way around. EF allows you to insert a navigation item back to the parent at the time you construct the object: public class Pupil { [Key] [Required] [DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int PupilId { get; set; } [Required] [StringLength(100)] public string Name { get; set; } [Required] public DateTime DateOfBirth { get; set; } [StringLength(200)] public string PrimarySchool { get; set; } public FormYear FormYear { get; set; } }This is all you need. Because Pupil objects already have a relationship with FormYear objects, when we put a FormYear element as a member, EF will use the foreign key on the Pupil table to allow you access to the parent FormYear object. You can now navigate directly from the Pupil to the FormYear and then to the Teacher in a single step: Entity.Pupil pupil = f1.Pupils.Single(p => p.Name == "John Curran"); string TeacherName = pupil.FormYear.Teacher.Name; |
|||||||||||||||||||
And that's the basics of Entity Framework complex types and foreign keys. |