Accepted answer

It's just another predicate, "where a corresponding silver vendor doesn't exist":

var goldWoSilver = _repository.Query<Books>()
    .Where(x => x.VendorId.Contains("gold"))
    .Where(x => !_repository.Query<Books>()
        .Any(s => s.ASIN == x.ASIN
               && s.VendorId.Contains("sil"))
    .OrderBy(x => x.Id).Skip(0).Take(500).ToList();

In many cases this is a successful recipe: start the query with the entity you want to return and only add predicates. In general, joins shouldn't be used for filtering, only to collect related data, although in that case navigation properties should be used which implicitly translate to SQL joins.


See if it helps -

var goldWithoutCorrespondingSilver = from b1 in books
                                  join b2 in books on b1.ASIN equals b2.ASIN
                                  where b1.VendorId.Contains("gold")
                                  group b2 by b1.VendorId into g
                                  where !g.Any(x => x.VendorId.Contains("sil"))
                                  select g.FirstOrDefault();

What I have done is -

  • Selected records with matching ASIN
  • Grouped them by VendorID
  • Selected ones which do not have sil

Related Articles