Entity Framework 4.1 Associations

Associations are the basic feature of any ORM framework and as such are fairly simple to understand and implement in Entity Framework.
First we will create a simple one -many bi-directional relationship. This relationship is created between two new classes Subject and Standard.

public class Standard
    {
        public int StandardId { get; set; }
        public String StandardName { get; set; }
	 public virtual ICollection<Subject> Subjects{ get; set;}
    }
public class Subject
    {
        public int SubjectId { get; set; }
        public String SubjectName { get; set; }
        public String SubjectCategory { get; set; }
        public int  StandardId { get; set; }
        public virtual Standard Standard { get; set; }
    }

The 1..M relationship between Standard and Subject is created by having an ICollection for the standard and a foreign key for the Subject entity.
We can change the name of the Foreign Key to deviate from the convention but then we will have to explicitly specify which property will serve as the Foreign Key for the navigation property.
The bi-directionality is established by having navigation properties in both the entities. These properties are declared as virtual to enable lazy loading.
Now, to configure this we will require the following mapping.

       modelBuilder.Entity<Subject>()
                         .HasRequired(s => s.Standard)
                         .HasMany( sd  => sd.Subjects)
                         .HasForeignKey(s => s.StandardId)
                         .WillCascadeOnDelete(true);


Figure 1 A One-Many relationship between Standards and Subjects

More complicated mapping can be done when we connect the different tables together.
Assume that we do not want the questions created by a user to be deleted when we delete the user. But if delete the subject then the questions related to that subject must also be deleted. First, we modify the POCO classes to establish a relationship and then map our requirement to the database.

public class QuestionDescriptor
    {
        public int QuestionDescriptorId { get; set; }
        public int Rating { get; set; }
        public int Difficulty { get; set; }
        public DateTime DateOfCreation { get; set; }
        public String QuestionText { get; set; }
        public String Answer { get; set; }
        public int UserDetailId { get; set; }
        public virtual UserDetail Creator { get; set; }
        public int SubjectId { get; set; }
        public virtual Subject Subject { get; set; }        
    }

The relationships are Uni-directional for now (can be made bi-directional for querying requirements).
We configure our requirements as follows.

    modelBuilder.Entity<QuestionDescriptor>()
                .HasRequired(qd => qd.Creator)
                .WithMany()
                .HasForeignKey(qd => qd.UserDetailId)
                .WillCascadeOnDelete(false);

    modelBuilder.Entity<QuestionDescriptor>()
                .HasRequired(qd => qd.Subject)
                .WithMany()
                .HasForeignKey(qd => qd.SubjectId)
                .WillCascadeOnDelete(true);

The default behavior of the Entity Framework is to have cascade delete as on for the relationships. We can turn this off as per our requirements. Also note how the Creator navigational property has been associated with the UserDetailId.


Figure 2 OnDeleteCascade in OFF state


Figure 3 OnDeleteCascade in ON state

Another requirement we often have is of many-many mapping between entities. To demonstrate this we can create an entity Test which will have many questions and a question can belong to more than one Test.

public class Test
    {
        public int TestId { get; set; }
        public DateTime DateOfCreation { get; set; } 
        public virtual ICollection<QuestionDescriptor> Questions {get;set;}
    }

In the configuration mapping we will configure it by assigning the keys and creating a table for the relationship.

modelBuilder.Entity<QuestionDescriptor>()
                .HasMany(qd => qd.Tests)
                .WithMany(t => t.Questions)
                .Map(mc =>
                         {
                             mc.MapLeftKey("QuestionDescriptorId");
                             mc.MapRightKey("TestId");
                             mc.ToTable("Test_Questions");});


Figure 4 Many – Many Relationship


Figure 5 The Database Diagram

Advertisements

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