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

Entity Framework Inheritance

Inheritance is the basic requirement when we go about making a model for our business. Inheritance in SQL based systems is restricted to “has a “relationships. In real world applications which are modeled in object oriented mechanisms this is the real paint point for the developers. The essence of ORM systems is to bridge the gap between these two worlds and allow a seamless layer of abstraction to exist for the database access.
Inheritance in general can be classified into 3 chief categories: –
1) Table Per Type (TPT)
2) Table Per Hierarchy (TPH)
3) Table Per Concrete Type (TPC)
Consider that following structure of inheritance.

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 class QuestionBrief : QuestionDescriptor
    {
        public bool Short { get; set; }
    }
public class QuestionImage : QuestionDescriptor
    {
       public String ImagePath { get; set; }
    }

Table Per Type represents the “IS A” relationship through Foreign Keys. This means that we need to map QuestionBrief and QuestionImage to separate tables in the database and have Foreign Key relations with the QuestionDescriptor table.
The mapping required is as follows: –

modelBuilder.Entity<QuestionBrief>().ToTable("Brief");
modelBuilder.Entity<QuestionImage>().ToTable("Image");

TPT strategy gives rise to a normalised database and the schema lends itself nicely to evolution. The primary drawback is that the queries formed are using Joins between table.

Figure 1 Table Per Type
This strategy involves creating a single table and having a discriminator column. Since discriminator is not the key and yet determines the values of the columns that belong to the subclasses, this strategy violates the third normal form. It also requires the properties of the subclasses to be nullable. However, it is the best performing strategy for both polymorphic (queries instance of a class and all instances of the sub-class) and non-polymorphic queries (queries returning only instances of a particular subclass).
This can be achieved with the following mapping: –

modelBuilder.Entity<QuestionDescriptor>()
                .Map<QuestionBrief>(m => m.Requires("QuestionType").HasValue("Brief"))
                .Map<QuestionImage>(m => m.Requires("QuestionType").HasValue("Image"));

The HasValue() method takes as a parameter an Object type so we can pass Boolean, integers etc. The type of parameter we pass to the HasValue() method will determine the datatype of the column in the database.

         
   modelBuilder.Entity<QuestionDescriptor>()
                .Map<QuestionBrief>(m => m.Requires("QuestionType").HasValue(1))
                .Map<QuestionImage>(m => m.Requires("QuestionType").HasValue(2));


Figure 2 Table Per Hierarchy
Table Per Concrete Type
Table per concrete type strategy pushes down the attributes of the base class to the sub-class. So, if we do not have many attributes in the base class and don’t care much for the polymorphism in our model we can also go for this strategy.
• TPC becomes tough when we have changes in the schema and the base class evolves to have more attributes and relationships.
• This approach also suffers with major ideological challenge in the sense that it discards polymorphism altogether and hence doesn’t support a dynamic environment with changing business requirements.
• This approach is recommended only when we are confident that the base class won’t change and we are not referring to the base class for multiple queries.

Entity Framework Complex Types

In complicated models we need complex types to satisfy complex business logic .The most simple example would be having address as a complex type. Complex types are objects with no identity of their own. They are dependent on the parent for their existence. Consider the case where address has no real significance if it is not associated with a customer. This also implies that lazy loading of the complex type is not possible.

public class Address
    {
        public String Street { get; set; }
        public String City { get; set; }
        public String State { get; set; }
        public int Zip { get; set; }
    }

Add the new property to the UserDetail class and associate the instance with a new address.

public class UserDetail
    {
        public int UserDetailId { get; set; }
        public String Name { get; set; }
        public String Password { get; set; }
        public String UserRole { get; set; }
        public DateTime DateOfCreation { get; set; }
        public Address UserAddress { get; set; }
    }
context.UserDetails.Add(new UserDetail{
                                       Name = "TestName",
                                       Password = "TestPassword",
                                       UserRole = "Administrator",
                                       DateOfCreation = DateTime.Now,
                                       UserAddress = new Address{
                                                   City = "Vashi",
                                                   State = "Maharashtra",
                                                   Street = "High Street",
                                                   Zip = 400705
                                                   }
                                                 });

• A good practice is to always explicitly register a complex type.
• Always initialize them; otherwise we will get a null reference exception.
So, in the mapping configuration we call explicitly register our complex type. There are two ways:-
1) Use the DataAnnotations to mark the Address class as a ComplexType.
2) Use the FluentApi to register the complex type.

modelBuilder.ComplexType<Address>();

We can further override the default naming conventions of the EntityFramework to have more readable column names in the database. This again can be done using both DataAnnotations and FluentApi.

modelBuilder.Entity<UserDetail>().
                Property(p => p.UserAddress.State)
                .HasColumnName("State");

modelBuilder.Entity<UserDetail>()
                .Property(p => p.UserAddress.City)
                .HasColumnName("City");


Figure 1 Users Table with a Complex Type before and after configuration

Entity Framework Code First

The ADO.net team came out with its first offering for the Code-first development in the Entity Framework. This is an alternative to the existing methodologies of Database-first and the Model-first approaches. Now the suite has 3 alternatives and any approach can be chosen depending on the requirement.

Briefly explained below are the 3 approaches:-

Database-first: – Allows you to plug-in an existing database into the application. Any change to the database also changes the data access layer in the application.

Model-first: – Allows you to make a data model (domain model) in the designer surface through entities and relationships among them. A database can be generated from the model and any changes in the model can be pushed down to the database through re-creation of the database.

Code-first: -Allows you to create a set of classes to model your business domain. These classes can be configured and tables can be created using the public attributes with these classes. Changes in these classes will trigger automatic changes in the database which can be controlled by code.

Code-first borrows two important concepts from the dynamic language paradigm, Convention over Configuration and fluent API. Before we dive into the topic below are a few basic terms that will be used: –

Entity: – Any business object that needs to be mapped to a persistent storage (e.g. sales representative, account, commission).

Mapping: -It is the act of determining how entities and their relationships will be persisted in the database.

Relationship Mapping: – It is the act of determining how the relationships (association, aggregation, composition) will be mapped to the database.

Association: -An association is the definition of the relationship between two entities (e.g. a foreign key)

Navigation Property: -It is the property that allows you to traverse from one end of the association to the other end. Navigation properties are optional but are dependent on the presence of an association for their existence.

Association Set: -Association set is the logical grouping of the association instances of the same type. It is not a data modeling construct.

Association End Multiplicity: -It defines the number of entities that can be at either end of the association. It can be 1(exactly one), 0…1(zero or one) or *(zero, one or many).

The implementation is done as follows: –

· When the multiplicity is singular (1), define a navigational property.

· When the multiplicity is plural (many) then use ICollection<T> of the Target entity involved.

Directionality of relationships: – The relationships can be Uni-directional or Bi-directional. Uni-directionality indicates that only one of the entities involved is aware of the other i.e. the navigational property exists only at one end. Bi-directionality indicates that both the entities are aware of each other and navigational property exists at both the ends.

A Simple Model

First add the entity framework package to the visual studio project. The new way is to do it through

Nu-get .

Figure 1 Adding Entity Framework Through NuGet Command Line

In the package manager console (which is powershell) do the following: –

PM> Install-Package EntityFramework

The package manager will go to the official nu-get package source and look for the package and bring it down and put it in your assemblies. The end message will be something similar to this (the version numbers can differ, the last thing represents your project name).

Successfully added ‘EntityFramework 4.1.10715.0’ to EFCodeFirst

The alternative is to use the Add Library Package Reference

Figure 2 Adding Entity Framework through NuGet Web Interface

Note: – Nuget is not available for visual studio 2010 C# express edition; we will need the standalone installer for Entity Framework.

Now, create a simple POCO class


using System;
using System.ComponentModel.DataAnnotations;
namespace EFCodeFirst
{ public class UserDetail
//[Key] public int UserDetailId
{ get; set; }
[Required] [MaxLength(10, ErrorMessage = "UserName must be 10 characters or less"), MinLength(5)]
public String Name { get; set; }
[Required] [MaxLength(20, ErrorMessage = "Password must be between 6 and 20 characters"), MinLength(6)]
public String Password { get; set; }
public String UserRole { get; set; }
public DateTime DateOfCreation { get; set; }
 }
}

Firstly, the notion of convention over configuration allows us to have UserDetailId as the primary key. EF will pick the property with class name appended with Id as the default primary key. Of course, if our requirements differ we can also use the data annotations to explicitly mark a property as key. Similarly, Foreign key can also marked in the POCO class.

The annotations associated with the properties will allow us to have the concept of DRY (Do Not Repeat Your Code).These validation will also be pushed down to the tables in the database.

Next, we will create a context for managing the entities.

using System.Data.Entity;
namespace EFCodeFirst
{
   public class MyDatabaseContext : DbContext 
   {
   public MyDatabaseContext() 
   {
   Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyDatabaseContext>());
   }
   public DbSet<UserDetail> UserDetails { get; set; }
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
       modelBuilder.Entity<UserDetail>().ToTable("Users"); 
    } 
}
}

The DropCreateDatabaseIfModelChanges is a really handy because in development there will often be rapid changes and this method allows you to do that. To customize the database that the code creates we can also specify the Connection Parameters for the database using the DefaultConnectionFactory property.

How does the EF Framework know that the model has changed?

· When we first make use of the entities the framework goes ahead and creates the database (default provider is SQLExpress, we can change it to SQLCE or Oracle etc..).

· When it creates the database (if it is successful) it will generate MetaData for the model and store it in the database.

· Next time if we change something in the model (e.g. adds a new entity, modify properties or relationships) it will check the hash of the new model against the hash of the existing model.

· If the hash does not match it will realize that the model has changed and it will drop it and re-create with the new model.

· If the creation of the database fails (e.g. foreign key is not specified properly etc…), there will be no hash generated for the model. We will have manually drop the database and then re-run the code after fixing the errors.

Figure 3 EDM Meta Data Table

DbSet represents the collection of the entities in the context. The overridden method is where the real control resides for the mapping. This is where we will create your mappings specify the foreign keys and map the POCO classes to tables etc.

In the above snippet the entity UserDetail has been mapped to a table “Users”. The parameter for the ToTable(“TableName”) ,method is the name of the table in the database. This line of code borrows the concept of fluent api (basically the code flows like method1().method2().method3()……) from the dynamic languages. Fluent Api is much more than method chaining and requires more thought in the api construction.

The final thing would be to use this context and create an instance of the entity and save it to the database.


using System; using System.Linq;
using System.Data.Entity.Validation;
namespace EFCodeFirst
{
   class Program { static void Main(string[] args) {
   using (var context = new MyDatabaseContext()) {
   try
   {
       context.UserDetails.Add(new UserDetail{
                                      Name = "Ashutosh",
                                      Password = "Shyamu",
                                      UserRole = "Administrator",
                                      DateOfCreation = DateTime.Now });
       context.SaveChanges();
       var result = from u in context.UserDetails select u;
       foreach (var temp in result) 
       {
       Console.WriteLine(temp.UserDetailId + “\n” + temp.Name);
       }
    }
 
    catch (DbEntityValidationException dbe)
    {
      foreach(var error in dbe.EntityValidationErrors)
      {
          Console.WriteLine("An error has occured");
          Console.WriteLine("Property :- " + error.ValidationErrors.First().PropertyName + "\nMessage :-" +          error.ValidationErrors.First().ErrorMessage); 
      }
   }
}
 Console.ReadKey(); 
  }
 }
} 

We create an instance of the context and use that to add a new UserDetail to the context. It is imperative to call the SaveChanges() method, since it is only then that the validations kick in and the entity is pushed down to the database. If there are any errors while validating the entities the context will throw an exception and we will see our error messages that were defined as annotations for the properties of the entity.

When we run the code a new database will be created with the “Users” table.

image

Figure 4 Users Table

If we run a simple sql select query for the users table we will see the following.
image

Figure 5 an instance of UserDetail Entity in the Users Table

We did not specify the primary key when we initialized the object, yet the key was assigned. This is because the default behavior of EF is to mark primary key as auto increment. We can turn this on or off as per our requirements.

Now, assume we had initialized the object as follows: –

new UserDetail
{
Name = "Ashu",
Password = "Shyamu",
UserRole = "Administrator",
DateOfCreation = DateTime.Now
};

image

Figure 6 Exception caught when validation rules were violated

· The validation errors would be caught and we will see the error message we specified as annotation for the property.

· These validation errors can also be thrown to a view we might have in a web application or a windows application.

· These validation messages can also be globalized providing us with the capability to have messages in different languages as well.