title: How to specify the column to join on when using Include in EF with a view on table with true foreign key
body: <p>In the MS SQL DB behind a Blazor app, I've made a view for a contest entry, adding in data from a few different tables for use in a list of entries for a voting page; I made a model class for it so I can retrieve it using EF (5.0.3).  I've also added a list of Vote objects to that class, specifically for holding the vote the current user has made on the entry in question so we can show if they have voted for the entry or not.</p>
  <p>The main model classes in question, with the not-relevant-to-the-issue fields removed:</p>
  <pre><code>[Table(&quot;JudgingEntry&quot;)]
  public class JudgingEntry
  {
      [Key]
      public int EntryId { get; set; }
      &lt;snip&gt;
      public List&lt;Vote&gt; Votes { get; set; }
  }
  
  [Table(&quot;Entry&quot;)]
  public class Entry
  {
      [Key]
      public int EntryId { get; set; }
      &lt;snip&gt;
  }
  
  [Table(&quot;Vote&quot;)]
  public class Vote
  {       
      public int EntryId { get; set; }      
      public int UserId { get; set; }
      &lt;snip&gt;
  }
  </code></pre>
  <p>Entry ID on Vote is a foreign key to the Entry ID on Entry, which the EntryID in JudgingEntry is populated from; UserId on vote is a foreign key to a User table and JudgingEntry does not use that.</p>
  <p>What I'm looking to do is: given some filtering, get the list of relevant judging entries, each populating its Votes field with a list of all Vote rows that match the entry ID of the JudgingEntry and the current user's ID.  What I've been trying is:</p>
  <pre><code>public async Task&lt;IEnumerable&lt;JudgingEntry&gt;&gt; GetJudgingEntriesFiltered(&lt;filtering parameters&gt;, int? userID)
  {
      IEnumerable&lt;JudgingEntry&gt; jentries = null;
      jentries = await _appDbContext.JudgingEntries.Where(&lt;snip for long filtering logic&gt;)
                                                   .Include(e =&gt; e.Votes.Where(v =&gt; userID != null &amp;&amp; v.UserId == userID))
                                                   .AsNoTracking().ToListAsync();
      &lt;further filtering logic on jentries&gt;
  }
  </code></pre>
  <p>We should never get in a null user ID, the userID != null clause in the filter on the include is just in case my &quot;can't happen&quot; isn't as solid as I thought.  The filtering is split up because there's some state-dependent stuff that would make doing this in one chained set of calls be, at best, extremely unreadable.</p>
  <p>Looking at the SQL generated this does seem close to what I need, the issue is that that EF is creating a new column when pulling from the Vote table, 'JudgingEntryEntryID', and selecting it/matching the EntryID from JudgingEntry against it:</p>
  <pre><code>SELECT &lt;snip&gt;
  FROM [JudgingEntry] AS [j]
  LEFT JOIN (
      SELECT [v].[EntryId], [v].[UserId], [v].[JudgingEntryEntryId], &lt;snip&gt;
      FROM [Vote] AS [v]
      WHERE [v].[DEPUserId] = @__userID_1
  ) AS [t] ON [j].[EntryId] = [t].[JudgingEntryEntryId]
  </code></pre>
  <p>and since that column does not exist, the call throws an exception.  Is there a way to specify that, for this include, JudgingEntry.EntryID should be matched to Vote.EntryID?  Or am I looking at having to redo this, using the Entry table/class and pulling in the various extra tables I need via multiple Includes?</p>
  <p>Any help or clarification, or the right way to do what I'm trying to do if my approach is bone-headed, would be appreciated. Thanks!</p>