title: Conditional Mapping on Relationships
body: <p>Using Entity Framework (Code First), I'm trying to map a conditional/filtered relationship between the following 2 entities:</p>
  
  <p><strong>Building</strong><br/>
  BuildingId<br/>
  BuildingName</p>
  
  <p><strong>Area</strong><br/>
  AreaId<br/>
  ParentId<br/>
  AreaName<br/>
  IsSubArea<br/></p>
  
  <p>A <strong>Building</strong> can have many <strong>Areas</strong><br/>
  An <strong>Area</strong> can have many (Sub)<strong>Areas</strong></p>
  
  <p>I would like to create the relationship between <strong>Building</strong> and <strong>Area</strong> where the areas marked with 'IsSubArea' are filtered out of the relationship.  In this context, ParentId would relate to a <strong>Building</strong>, otherwise, ParentId would be another <strong>Area</strong>.  This would allow me to create a building with many areas, and each area could have many sub-areas, creating a tree style structure.</p>
  
  <p>The closest to a solution I have found relates to 'soft delete' functionality (<a href="https://stackoverflow.com/a/19248216/2064829">source</a>):</p>
  
  <pre><code>modelBuilder.Entity&lt;Foo&gt;().Map(m =&gt; m.Requires("IsDeleted").HasValue(false));
  </code></pre>
  
  <p>Converted to fit my example:</p>
  
  <pre><code>modelBuilder.Entity&lt;Area&gt;().Map(m =&gt; m.Requires("IsSubArea").HasValue(false));
  </code></pre>
  
  <p>But as far as I can tell, this has no bearing on the relationship to the Building.</p>
  
  <p>Another solution would be to create a property on the <strong>Building</strong> which specifies the query definition to use to return related areas (<a href="https://stackoverflow.com/a/8161796/2064829">source</a>):</p>
  
  <pre><code>public class Building
  {
      public int BuildingId {get; set;}
      public string BuildingName {get; set;}
  
      public IQueryable&lt;Area&gt; BuildingAreas
      {
          get 
          {
              return from area in areas
                     where area.IsSubArea == false
                     and   area.ParentId == BuildingId
                     select area;
  
              //Assume I have a reference to relevant DbSets
          }
      }
  }
  </code></pre>
  
  <p>This solution would work but doesn't feel as elegant as a conditional mapping.</p>
  
  <p>Another solution would be to inherit from <strong>Area</strong> and create the 2 sub-classes:</p>
  
  <p><strong>BuildingArea</strong><br/>
  AreaId<br/>
  BuildingId<br/>
  AreaName<br/></p>
  
  <p><strong>SubArea</strong><br/>
  AreaId<br/>
  ParentAreaId<br/>
  AreaName<br/></p>
  
  <p>Each inherits from <strong>Area</strong> and sets the 'IsSubArea' field as appropriate.
  This solution feels tidier but I do not know how to implement this in Entity Framework.</p>
  
  <p>Is there a way to specify conditional mapping on relationships?<br/>
  Is there a better way to implement this structure?</p>
  
  <p><strong>Update 1</strong>:Found <a href="http://www.asp.net/mvc/tutorials/getting-started-with-ef-5-using-mvc-4/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application" rel="nofollow noreferrer">this</a> &amp; <a href="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" rel="nofollow noreferrer">this</a> guide on inheritance which seems to match my requirements.  However, neither of these tutorials define relationships between derived types.  I'll update the question tonight with what I have tried with regards to the Table per Hierarchy (TPH) method.</p>
  
  <p><strong>Update 2</strong>:<br/>
  I'm going to try an describe the Table per Hierarchy (TPH) method I tried to implement based on the tutorial links above.  Forgive me if this gets a little complicated (maybe I'm over thinking it).</p>
  
  <p><strong>Models</strong><br/>
  The building class remains the same as the OP.</p>
  
  <p>I have created an abstract base class defining the Area properties common to each derived type (BuildingArea and SubArea):</p>
  
  <pre><code>public abstract class Area
  {
      protected Area(bool isSubArea)
      {
          IsSubArea = isSubArea;
          SubAreas = new List&lt;SubArea&gt;();
      }
  
      public int AreaId { get; set; }
      public int ParentId { get; set; }
      public string AreaName { get; set; }
      public bool IsSubArea { get; private set; } //note the private set
  
      public virtual ICollection&lt;SubArea&gt; SubAreas { get; set; }
  }
  </code></pre>
  
  <p>I then have 2 derived types which inherit from <strong>Area</strong>:</p>
  
  <pre><code>public class BuildingArea : Area
  {
      public BuildingArea () : base(false)
      {}
  
      public virtual Building ParentBuilding { get; set; }        
  }
  
  public class SubArea : Area
  {
      public SubArea(): base(true)
      {}
  
      // This is of type `Area` because parent could be either `BuildingArea` or `SubArea`
      public virtual Area Parent { get; set; }        
  }
  </code></pre>
  
  <p>I then have the following 2 EntityTypeConfigurations:</p>
  
  <pre><code>public class BuildingAreaMap : EntityTypeConfiguration&lt;BuildingArea&gt;
  {
      public BuildingAreaMap ()
      {
          // Primary Key
          HasKey(t =&gt; t.AreaId);
  
          // Properties
          Property(t =&gt; t.AreaName)
              .IsRequired()
              .HasMaxLength(256);
  
          // Table &amp; Column Mappings 
          ToTable("Areas");
          Property(t =&gt; t.AreaId).HasColumnName("AreaId");
          Property(t =&gt; t.ParentId).HasColumnName("ParentId");
          Property(t =&gt; t.AreaName).HasColumnName("AreaName");
          Property(t =&gt; t.IsSubArea).HasColumnName("IsSubArea");
  
          // This is the discriminator column
          Map(m =&gt; m.Requires("IsSubArea").HasValue(false));
  
          HasRequired(a =&gt; a.Site).WithMany(s =&gt; s.SiteAreas).HasForeignKey(k =&gt; k.ParentId);
      }
  
  
  public class SubAreaMap : EntityTypeConfiguration&lt;SubArea&gt;
  {
      public SubAreaMap()
      {
          // Primary Key
          HasKey(t =&gt; t.AreaId);
  
          // Properties
          Property(t =&gt; t.AreaName)
              .IsRequired()
              .HasMaxLength(256);
  
          // Table &amp; Column Mappings 
          ToTable("AssetHealthAreas");
          Property(t =&gt; t.AreaId).HasColumnName("AreaId");
          Property(t =&gt; t.ParentId).HasColumnName("ParentId");
          Property(t =&gt; t.AreaName).HasColumnName("AreaName");
          Property(t =&gt; t.IsSubArea).HasColumnName("IsSubArea");
  
          // This is the discriminator column
          Map(m =&gt; m.Requires("IsSubArea").HasValue(true));
  
          HasRequired(a =&gt; a.Parent).WithMany(s =&gt; s.SubAreas).HasForeignKey(k =&gt; k.ParentId);
      }
  }
  </code></pre>
  
  <p>This code builds successfully, but I do get the following runtime error:</p>
  
  <p><code>Map was called more than once for type 'SiteArea' and at least one of the calls didn't specify the target table name.</code></p>
  
  <p>But I am specifying the target table name (once in each EntityTypeConfiguration class).
  So I removed the EntityTypeConfiguration for <code>SubArea</code> but I get the same error.</p>
  
  <p>One of the tutorials pulls the mapping out of the EntityTypeConfiguration class and puts it in the <code>OnModelCreating</code> handler as follows:</p>
  
  <pre><code>modelBuilder.Entity&lt;Area&gt;()
      .Map&lt;BuildingArea&gt;(m =&gt; m.Requires("IsSubArea").HasValue(false))
      .Map&lt;SubArea&gt;(m =&gt; m.Requires("IsSubArea").HasValue(true));
  </code></pre>
  
  <p>This also gives me the same error.</p>
  
  <p>If I remove relationships from the equation, I get a different error regarding the <code>ParentId</code> property:</p>
  
  <p><code>The foreign key component 'ParentId' is not a declared property on type 'SiteArea'. Verify that it has not been explicitly excluded from the model and that it is a valid primitive property.</code></p>
  
  <p><strong>Update 3</strong></p>
  
  <p>An image of the model I'm trying to create...</p>
  
  <p><img src="https://i.stack.imgur.com/VQL5V.png" alt="ERD"></p>
  
  <p><strong>Update 4</strong></p>
  
  <p>I'm going to try and simplify my model to match the following.
  If the solution below works, I will need to have a little more business logic to navigate the tree but it should be manageable.</p>
  
  <p><img src="https://i.stack.imgur.com/oMc27.png" alt="ERD2"></p>