EF model with code first independent association or foreign key association

Question

I have read the articles about Independent association from Ladislav. I have checked the information he provided in this link. The information is quite helpful and it shed some light. However I want to model my existing database which I was given access to. It has three tables Users, Certificates and Quiz .

At first I thought of modeling as independent association. Why? My Users table has primary key UserID and Certificates table has PK CertID and a column UserID which I can say is a foreign key. Just when I thought its a one to many relationship, I realized that some UserID in Users table are not found in Certificates table. However all UserID in Certificates can be found in Users table.

My question is should I use independent association and if so how to achieve this that my Users table become the principal class and the Certificates one the dependent. This is so that I can show or have values from my Users table then read values from my Certificate table in my asp.net mvc 3 application.

Please correct this code below which shows what I intend to achieve what I stated above:

   public class Certificates
{
   [Key]
    public Users CertID { get; set; }
    public int ID { get; set; }
    public DateTime  recvd { get; set; }
    public int QuizID { get; set; }


}

public class Users
{
    public int ID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string email { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

 public class Quiz
    {
        public int QuizID { get; set; }
        public string QuuizName { get; set; }
        public int VolumeNo { get; set; }
        public int  mark { get; set; }
        public ICollection<Certificates> Certificates { get; set; }
    }

public class cpdContext : DbContext
{
    public DbSet<Certificates> Certificates { get; set; }
    public DbSet<Users> Users { get; set; }
    public DbSet<Users> Quiz { get; set; }

Finally how do I show details view with information from these three classes with ability to add Users mark and tests. The relationship I want to model is 1 to many between Quiz and Certificates.

Answer 1

My Users table has primary key UserID and Certificates table has PK CertID and a column UserID which I can say is a foreign key. Just when I thought its a one to many relationship, I realized that some UserID in Users table are not found in Certificates table. However all UserID in Certificates can be found in Users table.

That's pretty normal for a one-to-many relationship where User is the principal and Certificate the dependent and you have a constraint enforced for the relationship.

I don't see this as an argument to decide for independent or foreign key associations. As far as I can tell you can map a database schema with both association types. The database schema shouldn't be the driving factor for the decision. Ladislav's posts you have linked explained it in all details. There are other points than the database schema that will guide the decision:

• Architecture: Strict separation of object and relational world which might lead to the decision that you don't want a "foreign key" property as a relational artifact in your object model. This goes in favor of independent associations.
• Ease of use: The additional foreign key property makes it easier to work with relationships, especially updating them, in some scenarios. This point is for foreign key associations.
• Performance: EF is faster with foreign key associations in some situations with larger models.

Personally point 2 above tips the scales for me in most cases, but as said both is possible.

Mapping with foreign key associations (I omit all properties except PK, FK and navigation properties to keep it short):

public class Certificates
{
    [Key]
    public int CertID { get; set; }

    [ForeignKey("User")]
    public int UserID { get; set; }
    [ForeignKey("Quiz")]
    public int QuizID { get; set; }

    public Users User { get; set; }
    public Quiz Quiz { get; set; }
}

public class Users
{
    public int ID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

public class Quiz
{
    public int QuizID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

This assumes that both relationships are required, i.e. FKs in the database are not nullable. If they are you need to make the FK properties nullable as well (int?). Instead of data annotations you can use Fluent API, similar (but not exactly identical!) to the following example.

Mapping with independent associations:

public class Certificates
{
    [Key]
    public int CertID { get; set; }

    public Users User { get; set; }
    public Quiz Quiz { get; set; }
}

public class Users
{
    public int ID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

public class Quiz
{
    public int QuizID { get; set; }
    public ICollection<Certificates> Certificates { get; set; }
}

public class cpdContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entitiy<Users>()
            .HasMany(u => u.Certificates)
            .WithRequired(c => c.User)  // or WithOptional
            .Map(m => m.MapKey("UserID")); //<- DB FK column name

        modelBuilder.Entitiy<Quiz>()
            .HasMany(u => u.Certificates)
            .WithRequired(c => c.Quiz)  // or WithOptional
            .Map(m => m.MapKey("QuizID")); //<- DB FK column name
    }
}

The navigation properties in Certificates are not required (neither in the first nor the second example), you can remove them, if you don't need them, just use the parameterless WithRequired() then, and use Fluent API in the first example. (Possibly a [ForeignKey("UserID")] annotation on the Certificates collection in the Users class will work as well.)