title: Select all tags that are user in questions asp.net c# EF
body: <p>There are four tables:</p>
  
  <p>Questions(questionId, question)<br>
  QuestionTags(questionTagId, questionId, tagId)<br>
  CodingKeys(codingKeyId, codingTypeId ..)<br>
  Codings(codingId, codingKeyId, coding ..)</p>
  
  <p>I want to select all question tagIds and their codings (codingKeyId is foreign key of tagId) that are represented in Questions... So if I have 10 different codings in Codings table but only two of them are represented in Questions I want to select only these two.</p>
  
  <p>I tried with join like this:</p>
  
  <pre><code>var query = from qt in context.QuestionTags
      join c in context.Codings on qt.tagId equals c.codingKeyId
      select new
      {
          tagId = qt.tagId,
          coding = c.coding
      };
  </code></pre>
  
  <p>But the above solution gave me double results. For example, if one tag is included in more than one question, I get the same tag twice (I tried distinct, but that didn't work).</p>
  
  <p>I also tried using Any:</p>
  
  <pre><code>var query= context.QuestionTags
      .Where(qt =&gt; qt.Questions.QuestionTags.Any(q =&gt; q.tagId == qt.tagId))
      .Select(qt =&gt; new
      {
          codingKeyId = qt.questionId,
          coding = context.Codings.FirstOrDefault(c =&gt; c.CodingKeys.codingKeyId == qt.tagId).coding
      });
  </code></pre>
  
  <p>The same thing happened here, I got duplicate results, but <code>Distinct</code> didn't work (don't know why).</p>
  
  <p>However, if I use this SQL statement:</p>
  
  <pre><code>SELECT distinct tagId, coding 
  FROM QuestionTags
  LEFT OUTER JOIN Codings ON codingKeyId LIKE QuestionTags.tagId
  WHERE Codings.languageId = 1
  </code></pre>
  
  <p>I get the right result, but I don't want to write and store a procedure for this. I really wan't to know if I can solve this with EF (linq), and I am also not sure if distinct is the right solution.</p>
  
  <p>Thanks for your help.</p>