How to map different navigation properties in TPH types to the same table?

I have this existing databse schema which implies self-reference many-to-many relationship using a joint table. The Location table may contain information Country, City, District, or Area according to the Disciminator field. The table RelatedLocation holds the self-reference relations.

My domain model is as follows, where the Location class is abstract one, and each inherited class conatins related navigation properties.

public abstract class Location
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Country : Location
{
    public virtual ICollection<District> Districts { get; set; }
}

public class District : Location
{
    public virtual ICollection<Country> Countries { get; set; }
    public virtual ICollection<City> Cities { get; set; }
}

public class City : Location
{
    public virtual ICollection<District> Districts { get; set; }
    public virtual ICollection<Area> Areas { get; set; }
}

public class Area : Location
{
    public virtual ICollection<City> Cities { get; set; }
}

On OnModelCreating I use the following to ma开发者_开发百科p each of inherited class many-to-many relation

modelBuilder.Entity<Country>()
            .HasMany(c => c.Districts)
            .WithMany(d => d.Countries)
            .Map(t => t.ToTable("RelatedLocations").MapLeftKey("ParentId").MapRightKey("RelatedId"));
modelBuilder.Entity<City>()
            .HasMany(c => c.Districts)
            .WithMany(d => d.Cities)
            .Map(t => t.ToTable("RelatedLocations").MapLeftKey("ParentId").MapRightKey("RelatedId"));

Upon creating the model I receive and exeption with "Each EntitySet must refer to a unique schema and table", that is EF complains about Mapping different relations to the same table "RelatedLocaions" more than once.

I do not know mapping this way is not supported in EF4.1 or I am mapping it in a wrong approach!

---

I doubt that the mapping you are trying is possible. I would try something similar to this:

public abstract class Location
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Location> ParentLocations { get; set; }
    public virtual ICollection<Location> RelatedLocations { get; set; }
}

public class Country : Location
{
    // readonly = not mapped
    public IEnumerable<District> Districts
    {
        get { return RelatedLocations.OfType<District>(); }
    }
}

public class District : Location
{
    public IEnumerable<Country> Countries
    {
        get { return ParentLocations.OfType<Country>(); }
    }

    public IEnumerable<City> Cities
    {
        get { return RelatedLocations.OfType<City>(); }
    }
}

// same approch for the other collections

And then this mapping:

modelBuilder.Entity<Location>()
            .HasMany(l => l.ParentLocations)
            .WithMany(l => l.RelatedLocations)
            .Map(t => t.ToTable("RelatedLocations")
                       .MapLeftKey("ParentId")
                       .MapRightKey("RelatedId"));

The many-to-many mapping goes always between ParentLocations and RelatedLocations but these collections are populated with different instances of the derived classes according to the concrete type you are working with. The readonly collections are only helpers which perform a type cast in memory (based on the lazily loaded ParentLocations and RelatedLocations) of the Location entities.

Edit

Perhaps instead of using .OfType<T>() which filters all objects of type T from the source collection, .Cast<T>() is preferable which tries to cast all objects in the source collection to type T and throws an exception if casting is not possible. It should basically lead to the same result because ICollection<Location> in your base class should always only be populated by the same derived type. For example: Country.RelatedLocations should only contain entities of type District. But maybe the exception is good in this case because it indicates that something is wrong instead of silently ignoring the entities of another type in the collections (which OfType would do).

Edit 2

I want to emphasize that the IEnumerable collections are helpers which allow you to retrieve the entities with the derived type. The collections just perform a type cast, nothing more. They have nothing to do with the mapping to the database, EF even doesn't "see" that they exist. You can remove them and nothing would change in the EF model and the database table columns, relationships and referential constraints.

How would you add and retrieve entities in this model? Examples:

• Add a new Country with a list of District entities:

  var country = new Country() { RelatedLocations = new List<Location>() };
  country.Name = "Palau";
  // ParentLocations stays empty because Country has no parents
  var district1 = new District { Name = "District1" };
  var district2 = new District { Name = "District2" };
  country.RelatedLocations.Add(district1); // because District is a Location
  country.RelatedLocations.Add(district2);
  context.Locations.Add(country); // because Country is a Location
  context.SaveChanges();
  

• Retrieve this entity again:

  var country = context.Locations.OfType<Country>()
      .SingleOrDefault(c => c.Name == "Palau");
  // now get the districts, RelatedLocations is lazily loaded
  var districts = country.RelatedLocations.Cast<District>();
  // What type is districts? It's an IEnumerable<District>.
  // So we can also use a helper property:
  // var districts = country.Districts;
  

• Retrieve a district:

  var district = context.Locations.OfType<District>()
      .SingleOrDefault(d => d.Name == "District1");
  var countries = district.ParentLocations.Cast<Country>();
  // or with the helper: var countries = district.Countries;
  // countries collection contains Palau, because of many-to-many relation
  

Edit 3

Instead of creating a Country with new you can create a lazy loading proxy. Then you don't need to initialize the RelatedLocations collection. I was wondering how this could work with a derived type, but I just found that there is an overload of Create with a generic parameter for this purpose:

var country = context.Locations.Create<Country>();