title: Self JOIN query using EF-Core
body: <p>I have an old stored procedure selecting data from a self-referencing table.</p>
  
  <pre><code>exists (select * 
          from MyTable t1
          join MyTable t2 on t1.model = t2.model
          where t1.name = 'Name1'
            and t1.value = 'Value1'
            and t2.name = 'Name2'
            and t2.value = 'Value2'
            and t1.model = @model)
  </code></pre>
  
  <p>We move the code to EF Core. Is there a more efficient way of building the above query?</p>
  
  <p>I can imagine something like this</p>
  
  <pre><code>SELECT COUNT(*)
  FROM MyTable 
  WHERE 
      (model= @model) AND (
      (name = 'Name1' AND value = 'Value1') OR
      (name = 'Name2' AND value = 'Value2'))
  </code></pre>
  
  <p>and checking the returned value, so in EF</p>
  
  <pre><code>var v = await repository
      .AsNoTracking()
      .Where(t =&gt; t.model == model &amp;&amp; 
                 (t.name == 'Name1' &amp;&amp; t.value='Value1' || 
                  t.name == 'Name2' &amp;&amp; t.value == 'Value2'))
      .CountAsync();
  </code></pre>
  
  <p>Is this a more efficient approach comparing to the JOIN? I can see there is a difference, however, the result should be the same based on the nature of our data.</p>