mVC3 EF4.1 Model design error

Question

EF 4.1 in MVC3 and Lazy loading, using code first model

Having difficulties designing correct models. Please take a look and let me know what am I doing wrong. How can I fix it.

I am using Membership API for creating an account. Once the account is created successfully. I redirect to create a Contact record automatically. contactId (auto database generated), userid (storing the user id that was generated by membership api)

The models are:

public class Contact
{
   public int ContactID { set; get; }
   public string UserId { set; get; }
   public string LastName { set; get; }
   public int? CompanyID { set; get; } // not sure if I need this as it will be NULL
   public virtual Company CompanyInfo { set; get; }
}

next the user can click on create Company link or logout & login later to create the company record.

public class Company
{    
  public int CompanyID { set; get; }
  public int ContactID { set; get; }
  public string CompanyName { set; get; }
  public virtual Contact Contacts { set; get; }
}

When the user decides to create company record, I am checking if company already exists, if exists I am just showing the contact information and the company information OR if not found I redirect to create company.

public ActionResult chckifCompanyFound()
    {
        int contactId = 1; //Assuming I retrieved the value 
        //I think I should get the data from Company table, if company data found then contact data could be retrieved using lazy loading? 
       Company c= db.Company.Include(c => c.Contacts).Where(x => x.ContactID == contactId).FirstOrDefault();
      if(c == null)
          //redirect to create company
      else
         // view data from c company object
    }

currently it shows an exception once it tries to create contact record after membership API creates an account. I create the record like this:

Contact contact = new Contact();
contact.UserId = userId;
contact.LastName = lastName;
db.Contacts.Add(contact);
db.SaveChanges();

Exception:
Unable to determine the principal end of an association between the types 'D.Models.Contact' and 'D.Models.Company'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

thank you so much!

Answer 1

The answer is in your exception: The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

However, it looks like you have a 1 to 0..1 relationship here: Each company must have exactly 1 contact, but each contact can belong to zero or 1 company. Is this really your intent?

It seems to me that what you are really after is a 1 to * relationship, where each company can have MANY contacts, and each contact belongs to zero or 1 company.

Data annotations

public class Company // the principal
{    
  public int CompanyID { set; get; }
  //public int ContactID { set; get; } Company is the principal
  public string CompanyName { set; get; }
  public virtual ICollection<Contact> Contacts { set; get; } // has many contacts
}
public class Contact
{
   public int ContactID { set; get; }
   public string UserId { set; get; }
   public string LastName { set; get; }

   // you do need CompanyId, but it will be nullable in the db
   public int? CompanyID { set; get; } 
   [ForeignKey("CompanyID")] 
   public virtual Company CompanyInfo { set; get; }
}

Fluent API Note: with fluent API, your entity does not need the [ForeignKey] attribute

modelBuilder.Entity<Company>()
    .HasMany(principal => principal.Contacts)
    .WithOptional(dependent => dependent.CompanyInfo)
    .HasForeignKey(dependent => dependent.CompanyID);

Answer 2

You have a one-to-one relationship between Contact and Company and the exception says that EF cannot decide what's the principal (having the primary key) and what's the dependent (having the foreign key) because the navigation properties are "symmetric". EF demands that you specify this explicitly.

A few things to note about one-to-one relationships (they are more difficult to master with EF than one-to-many and even many-to-many relationships):

• EF only supports Shared Primary Key Associations to define a one-to-one relationship. This means that the primary key values of the associated entities must be the same. And for one of the entities the primary key is a foreign key at the same time. You cannot use an independent foreign key.

• Consequence of this point is that you can remove Contact.CompanyID and Company.ContactID. EF won't respect these properties as foreign key properties.

• Second consequence is that one of the primary keys cannot be an autogenerated identity in the database because it must always have the same value as the other (the principal) entity.

• You must decide which entity is the principal and which is the dependent. From your description I would guess that Contact is the principal (because you allow to have contacts without a company) and Company is the dependent (because there is no company without contact possible).

• Then you can define a mapping with Fluent API in your derived context:

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
      // ...
  
      modelBuilder.Entity<Contact>()
          .HasOptional(ct => ct.CompanyInfo)
          .WithRequired(cm => cm.Contacts);
  
      // ...
  }
  

I believe that this mapping will automatically ensure (if you create the DB with EF) that the primary key of Contact is an identity in the database, but the primary key of Company isn't. If not, you can turn this off explicitly:

modelBuilder.Entity<Company>()
    .Property(c => c.CompanyID)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);