title: Adding condition to mapping for child collection in Entity Framework 6 similar to NHibernate's "where"
body: <p>In NHibernate, there is a <code>where</code> mapping that allows you to specify a condition on a property mapping that affects how it is pulled from the database. For example, if I wanted to implement a soft delete and exclude all deleted items from a set, I could map it like so:</p>
  
  <h3>FluentNHibernate</h3>
  
  <pre class="lang-cs prettyprint-override"><code>// in class ParentMap : ClassMap
  HasMany(x =&gt; x.Children).Where("IsDeleted = 0");
  </code></pre>
  
  <h3>Hbm.Xml</h3>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;class name="Parent" table="[Parents]"&gt;
      &lt;bag cascade="all" lazy="true" name="Children" where="IsDeleted = 0"&gt;
          &lt;!-- rest of map here --&gt;
      &lt;/bag&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p>Is there anything similar in Entity Framework 6?</p>
  
  <p>The closest thing I found was a library called <strong><a href="https://github.com/jbogard/EntityFramework.Filters/" rel="nofollow">EntityFramework.Filters</a></strong>, which allows you to add global filters for properties, but it doesn't seem to work when that property is a collection. </p>
  
  <hr>
  
  <p>To give a better example of why a mapping like this is necessary, let's say I have a class that has a collection of objects that have a recursive child entity relationship (i.e., a collection of objects of the same type). They follow this basic structure:</p>
  
  <pre class="lang-cs prettyprint-override"><code>public class ReportOutline
  {
      public long Id { get; set; }
      public string Title { get; set; }
      public string Author { get; set; }    
      public virtual ICollection&lt;OutlineItem&gt; OutlineItems { get; set; }
  }
  
  public class OutlineItem
  {
      public long Id { get; set; }
      public string Name { get; set; }
  
      public long ReportOutlineId { get; set; }
      public long? ParentOutlineItemId { get; set; }    
  
      public virtual ReportOutline ReportOutline { get; set; }
      public virtual OutlineItem ParentOutlineItem { get; set; }    
  
      public virtual ICollection&lt;OutlineItem&gt; OutlineItems { get; set; }
  }
  </code></pre>
  
  <p>And these are mapped with the EF fluent API like this:</p>
  
  <pre class="lang-cs prettyprint-override"><code>modelBuilder.Entity&lt;ReportOutline&gt;()
      .HasKey(o =&gt; o.Id)
      .HasMany(o =&gt; o.OutlineItems)
      .WithRequired(i =&gt; i.ReportOutline)
      .HasForeignKey(i =&gt; i.OutlineId);
  
  modelBuilder.Entity&lt;OutlineItem&gt;()
      .HasKey(p =&gt; p.Id)
      .HasMany(p =&gt; p.OutlineItems)
      .WithOptional(c =&gt; c.ParentOutlineItem)
      .HasForeignKey(c =&gt; c.ParentOutlineItemId);
  </code></pre>
  
  <p>This produces the correct database structure, and my records look fine. Here's an example of what the <code>OutlineItems</code> table would look like with two items on a <code>ReportOutline</code>, if one had two child items (four altogether):</p>
  
  <pre class="lang-none prettyprint-override"><code>Id    Name           ReportOutlineId    ParentOutlineItemId
  1     Introduction   1                  NULL
  2     Pets           1                  NULL
  3     Cats           1                  2
  4     Dogs           1                  2
  </code></pre>
  
  <p>When the <code>ReportOutline</code> gets loaded through the <code>DbContext</code>, however, since <code>ReportOutlineId</code> matched the outline's <code>Id</code>, the <code>ReportOutline.OutlineItems</code> is getting populated with all four items. This results in the sub-items appearing both under the parent items and the main outline itself:</p>
  
  <pre class="lang-none prettyprint-override"><code>Title:  My Report
  Author: valverij
  
  I.  Introduction  (Id: 1)
  II. Pets          (Id: 2)
      A. Cats       (Id: 3)
      B. Dogs       (Id: 4)
  III. Cats         (Id: 3) &lt;--- Duplicated
  IV.  Dogs         (Id: 4) &lt;--- Duplicated
  </code></pre>
  
  <p>Now, if I were using NHibernate with FluentNhibernate, I could specify a <code>where</code> condition on the entity mapping, so that <code>ReportOutline.OutlineItems</code> <em>only</em> pulls parent items:</p>
  
  <pre class="lang-cs prettyprint-override"><code>// in ReportOutlineMap
  HasMany(x =&gt; x.OutlineItems).Where("ParentOutlineItemId IS NULL");
  </code></pre>
  
  <p>Without that, I would have to remember to <em>only</em> access <code>ReportOutline</code> objects through a pre-written query that explicitly deals with the <code>OutlineItem</code> collection.</p>