title: Entity Framework: Get parent record with filtered child records
body: <p>I don't really know much about EF but have to do some bugfixing. One of the bugs occurs as follows:</p>
  
  <ul>
  <li>Get parent record (EF LINQ query) using <code>ObjectContext</code></li>
  <li>Change some property</li>
  <li>Save changes</li>
  </ul>
  
  <p>The problem is the <code>Get parent record</code> and more specifically <strong>getting only the active children of the parent</strong>.</p>
  
  <p>This is the current <em>hack</em>:</p>
  
  <pre><code>var data =
      (from q in Context.Questions.Include("Status").Include("Category")
       where q.Id == id
       select q).SingleOrDefault();
  
  var atts = Context.Answers.Where(a =&gt; a.qId == id &amp;&amp; a.active); // 1
  data.Answers.Clear(); // 2
  foreach (var att in atts)
  {
      data.Answers.Add(att); // 3
  }
  </code></pre>
  
  <p>What happens:</p>
  
  <ol>
  <li>Fetch the active child records from the database</li>
  <li>Because of lazy loading, all attachments are fetched from the database. The collection is then immidiately cleared. (EF tracking marks these records as "to delete"?)</li>
  <li>Loop over all active records and add them again to the collection (EF tracking marks these records as "to insert"?)</li>
  </ol>
  
  <p>I'm getting the following exception when updating: <code>The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.</code></p>
  
  <p>A pretty generic error which, I assume, occurs because EF is confused about clearing the collection and then having some of the same records added again.</p>
  
  <p><strong>My question</strong>: How do I get only the active child records and get them in an "Unchanged" state so that at no point EF can decide to delete and reinsert my records. And obviously I also don't want to lose the non-active records when updating.</p>
  
  <p><strong>Not the answer</strong> (or is it?): Often found on SO and Google but can't get this to work: </p>
  
  <pre><code>Context.ContextOptions.LazyLoadingEnabled = false;
  
  var data =
      (from q in Context.Questions.Include("Status").Include("Category")
       where q.qId == id
       select new
       {
           Question = q,
           Answers = q.Answer.Where(a =&gt; a.active)
       }).ToArray().Select(q =&gt; q.Question).FirstOrDefault();
  
  return data; // Does not contain the answers... :(
  </code></pre>