code first - Entity Framework mapping

Question

I've made simple classes that simulate the classes I have (sorry I had to make up the classes, the usual example databases do not have the structure I wanted to ask about):

public class Animal
{  
    public System.Guid ID { get; set; }
    public string SpeciesName { get; set; }  
    public virtual ICollection<AnimalSpecies> AnimalSpecies { get; set; }
}

Species Fish:

public class Fish 
{     
    public System.Guid ID { get; set; }
    public int Freshwater { get; set; } 
}

Spieces Reptile:

public class Reptile
{     
    public System.Guid ID { get; set; }
    public int LifeExpectancy { get; set; }     
}

AnimalSpecies class:

public class AnimalSpecies
{
    public System.Guid Animal_ID { get; set; }
    public System.Guid Species_ID { get; set; }
    public virtual Animal Animal { get; set; }
} 

Mapping of the AnimalSpecies:

public AnimalSpeciesMap()
{       
    this.HasKey(t => new { t.Animal_ID, t.Spieces_ID });

    this.Property(t => t.Animal_ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    this.Property(t => t.Spieces_ID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

    this.ToTable("AnimalSpecies");
    this.Property(t => t.Animal_ID).HasColumnName("Animal_ID");
    this.Property(t => t.Spieces_ID).HasColumnName("Spieces_ID");

    // Relationship between Animal and AnimalSpieces: 
    this.HasRequired(t => t.Animal)
            .WithMany(t => t.AnimalSpecies)
            .HasForeignKey(d => d.Animal_ID);               
}

Since Spieces_ID doesn’t have the foreign key, is there a way to map relationship between AnimalSpecies and Fish/Reptile?

Answer 1

I don't think that it's possible to define a mapping where AnimalSpecies.Species_ID participates as the foreign key in two different relationships - one between AnimalSpecies and Fish and a second between AnimalSpecies and Reptile.

For me it looks like your model is missing a Species base class for Fish and Reptile. If you would have such a base class your model could look like this:

public class Animal
{
    public System.Guid ID { get; set; }
    //...
    public virtual ICollection<AnimalSpecies> AnimalSpecies { get; set; }
}

public class Species // I think the base class could also be abstract
{
    public System.Guid ID { get; set; }
    //...
    public virtual ICollection<AnimalSpecies> AnimalSpecies { get; set; }
}

public class Fish : Species
{
    public int Freshwater { get; set; } 
}

public class Reptile : Species
{
    public int LifeExpectancy { get; set; }
}

public class AnimalSpecies
{
    public System.Guid Animal_ID { get; set; }
    public System.Guid Species_ID { get; set; }
    public virtual Animal Animal { get; set; }
    public virtual Species Species { get; set; }
}

And the mapping:

public AnimalSpeciesMap()
{       
    this.HasKey(t => new { t.Animal_ID, t.Spieces_ID });

    this.Property(t => t.Animal_ID)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    this.Property(t => t.Spieces_ID)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

    this.ToTable("AnimalSpecies");

    this.HasRequired(t => t.Animal)
        .WithMany(t => t.AnimalSpecies)
        .HasForeignKey(d => d.Animal_ID);               

    this.HasRequired(t => t.Species)
        .WithMany(t => t.AnimalSpecies)
        .HasForeignKey(d => d.Species_ID);               
}

If your AnimalSpecies class does not have other members than the keys and navigation properties you could also remove this class from the model and map a direct many-to-many relationship between Animal and Species (doesn't make sense from domain viewpoint because an animal belongs only to one species, does it?):

public class Animal
{
    public System.Guid ID { get; set; }
    //...
    public virtual ICollection<Species> Species { get; set; }
}

public class Species // I think the base class could also be abstract
{
    public System.Guid ID { get; set; }
    //...
    public virtual ICollection<Animal> Animals { get; set; }
}

public class Fish : Species
{
    public int Freshwater { get; set; } 
}

public class Reptile : Species
{
    public int LifeExpectancy { get; set; }
}

// no AnimalSpecies class anymore

Mapping:

public AnimalMap()
{       
    this.HasMany(a => a.Species)
        .WithMany(s => s.Animals)
        .Map(x =>
        {
            x.MapLeftKey("Animal_ID");
            x.MapRightKey("Species_ID");
            x.ToTable("AnimalSpecies");
        });
}

AnimalSpecies is now a hidden table which is managed by EF for the many-to-many relationship and not exposed in the model.

I am not sure if I understand your question correctly. This is just what came to my mind.

Edit

If you don't specify any special mappings for the derived classes EF will assume TPH (Table-Per-Hierarchy) inheritance which means that all subclasses together with the base class are stored in the same database table, distinguished by a discriminator column.

If you have many derived classes with many properties each the better inheritance strategy might be TPT (Table-Per-Type). In this case you define for each subclass its own table in the mapping:

public FishMap()
{
    this.ToTable("Fishes");
}

public ReptileMap()
{
    this.ToTable("Reptiles");
}

Now every derived class gets its own table and the base class is stored in table "Species". EF will create the appropriate joins in the database when you query for a fish for example:

var result = context.Species.OfType<Fish>()   // Species is DbSet<Species>
    .Where(f => f.Freshwater == 1).ToList();

You can read more about the different inheritance mapping strategies and their benefits and drawbacks here:

• TPH: http://weblogs.asp.net/manavi/archive/2010/12/24/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph.aspx

• TPT: http://weblogs.asp.net/manavi/archive/2010/12/28/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt.aspx

• TPC: http://weblogs.asp.net/manavi/archive/2011/01/03/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines.aspx