title: Linq NOT EXISTS multiple tables
body: <p>I want to do a LINQ NOT EXISTS on query MULTIPLE TABLES.</p>
  
  <p>All examples on Google or SO are handling <strong>two tables</strong> I'm working with three so I'm struggling as a newbie on LINQ on how to reference them correctly.</p>
  
  <p>First I tried this LINQ query</p>
  
  <pre><code>  var nocertificates = (
    from x in rmdb.t_certificates
    from ce in rmdb.t_user_certificates
    from u in rmdb.t_users
    where u.id == ce.uid &amp;&amp; ce.value != x.id
    select x).AsEnumerable().Select(x =&gt; new ViewModelCheckBox()
        {
           Value = x.id.ToString(),
           Name = x.name,
           Checked = false
        });
  </code></pre>
  
  <p>I used the ugly three times from as I'm not that good with creating types for joining.
  But that gave wrong result and I realized I had to go for a <strong>NOT EXISTS</strong></p>
  
  <p>So I built a new query in T-SQL</p>
  
  <p>This is the SQL query it works!</p>
  
  <pre><code>select distinct * from t_certificates tc
  where NOT EXISTS
  (
  select distinct * from t_users tu, t_user_certificates tuc
  WHERE tu.email = 'user@email.com'
  and tu.id = tuc.[uid]
  and tuc.value = tc.id
  )
  </code></pre>
  
  <p><strong>How would I do that in LINQ?</strong> </p>
  
  <p><em><strong>This is the question, I will award my answer for that!</em></strong></p>
  
  <p><strong>BUT!</strong></p>
  
  <p>When we are at it... I'm really curious on the answer.. Is it possible to do one LINQ query that return an Ienumerable with both those that EXISTS and NOT EXISTS resulting in an object which will hold DIFFERENT VALUES on the checked property <strong><br> EXISTS -> CHECKED = true <br>NOT EXISTS -> CHECKED = false</strong></p>
  
  <p>This is how I create my object.</p>
  
  <pre><code>   .Select(x =&gt; new ViewModelCheckBox()
        {
           Value = x.id.ToString(),
           Name = x.name,
           Checked = this should be different based on exists or not
        });
  </code></pre>