Help mapping Domain model with EF4.1 fluent API?

Question

I'm new to EF and the fluent API. My DBA decided he wanted to map the "primary" citizenship of a person with an Is_Primary flag in the citizenships table. So our DB looks something like this:

**Person Table**
Person_Id    int           identity  (PK)
First_Name   nvarchar(30)
...

**Citizenship_Table**
Citizenship_Person_Id  int identity  (PK)
Person_Id              int
Country_Code           char(2)
Is_Primary             byte
...

**Country_Table**
Country_Code     char(2)  (PK)
Country_Name     varchar(30)
...

I really don't want the Is_Primary flag in my domain model. Instead, I want my domain to look something like this:

public class Person
{
   public int Id {get; set;}
   ...
   public virtual ICollection<Country> Citizenships {get; set;}
   public Country PrimaryCitizenship {get; set;}
}

public class Country
{
   public int Code {get; set;}
   public string Name {get; set;}
}

Is there a way to map this scenario?

Answer 1

It's not possible to map your domain model proposal to the given table structure. In my opinion it's also wrong - from the domain viewpoint - to introduce an Is_Primary flag into the Citizenship table (which is basically a join table between Person and Country).

The domain says that a person can have one primary citizenship (or perhaps none) but never many. This is not correctly expressed in the table structure: The Is_Primary column could be set for more than one country for a given person. It's also difficult to change the primary citizenship for a person because you would have to search for all entries in the citizenship table belonging to that person if there is another country marked as primary, then reset this flag before you set the flag for the new country.

This is correctly expressed in your model which means that the Person should have a foreign key to the Country table (either required or optional). Changing the primary citizenship would only require to set this FK to another value. Duplicate primary flags are impossible in this model.

If you change the table structure accordingly to the following ...

**Person Table**
Person_Id                int           identity  (PK)
First_Name               nvarchar(30)
PrimaryCountry_Code      char(2)                 (FK to Country table)
...

**Citizenship_Table**
Person_Id                int                     (PK)
Country_Code             char(2)                 (PK)

**Country_Table**
Country_Code             char(2)                 (PK)
Country_Name             varchar(30)
...

... a mapping to your model would be possible:

modelBuilder.Entity<Person>()
    .Property(p => p.Id)
    .HasColumnName("Person_Id");

modelBuilder.Entity<Person>()
    .Property(p => p.Name)
    .HasColumnName("First_Name")
    .HasMaxLength(30);

modelBuilder.Entity<Person>()
    .HasMany(p => p.Citizenships)
    .WithMany()
    .Map(a => {
        a.MapLeftKey("Person_Id");
        a.MapRightKey("Country_Code");
        a.ToTable("Citizenship");
    });

modelBuilder.Entity<Person>()
    .HasOptional(p => p.PrimaryCitizenship) // or .HasRequired(...)
    .WithMany()
    .Map(a => a.MapKey("PrimaryCountry_Code"))
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Country>()
    .HasKey(c => c.Code)
    .Property(c => c.Code)
    .HasColumnName("Country_Code")
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasMaxLength(2)
    .IsFixedLength()
    .IsUnicode(false);

modelBuilder.Entity<Country>()
    .Property(c => c.Name)
    .HasColumnName("Country_Name")
    .HasMaxLength(30)
    .IsUnicode(false);

Not a solution to your problem, but some food for discussion with your DBA.