title: Which LINQ query to select rows from 1 table that are not in another table
body: <p>I'm developing an application in which I have 2 different entities, Products, and ShoppingCarts. Each product is unique and has a unique identifier. I want to add a product that is not already in another cart and that is not sold to a ShoppingCart.  </p>
  
  <p>Product entity simplified:</p>
  
  <pre><code>public class Products
  {
      public int Id { get; set; }
      public string Name{ get; set; }
      public bool Sold { get; set; }
  }
  </code></pre>
  
  <p>Shopping Cart entity simplified:</p>
  
  <pre><code>public class ShoppingCarts
  {
      public int Guid Guid { get; set; }
      public int ProductId { get; set; }
  }
  </code></pre>
  
  <p>So first I retrieve all the Product.Id and then I add them to my cart. My method looks like this: </p>
  
  <pre><code>private IQueryable&lt;Products&gt; GetAvailableProductId(int quantity)
  {
      var query = (from p in _context.Set&lt;Products&gt;()
                  join sc in _context.Set&lt;ShoppingCarts&gt;() on p.Id equals sc.ProductId into subset
                  from sc in subset.DefaultIfEmpty()
                  where !p.Sold &amp;&amp; sc == null
                  select p).Take(quantity);
      return query;
  }
  </code></pre>
  
  <p>For some reason, every once in a while, 2 entities with the same ProductId are being added to different carts. This was enabling the application to sell 2 of the same products. I ended up fixing this by performing another check in the application before I make the transaction.</p>
  
  <p>I revisited the code recently and came across these posts  <a href="https://stackoverflow.com/questions/4845604/linq-query-determining-if-object-in-one-list-exists-in-another-based-on-key">LINQ Query: Determining if object in one list exists in another based on key</a>
  <a href="https://stackoverflow.com/questions/2587359/linq-to-entity-joining-on-not-in-tables">LINQ to Entity, joining on NOT IN tables</a></p>
  
  <p>My question is if changing my query to something like this will prevent the double addition.</p>
  
  <pre><code>private IQueryable&lt;Products&gt; NewGetAvailableProductId(int quantity)
  {
      var query = (from p in _context.Set&lt;Products&gt;()
                  where !_context.Set&lt;ShoppingCarts&gt;().Any(x =&gt; x.ProductId == p.Id) &amp;&amp;  !p.Sold
                  select p).Take(quantity);
      return query;
  }
  </code></pre>
  
  <p>If there are any doubts, please let me know so I can explain this better. </p>
  
  <p>Thanks,</p>