1

So I have 2 IQueryable<Array> queries with a common string type; I want to use the second query to remove all records using the common type in the first, leaving only the records not found as the result of the first query. Not quite sure how to do it.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
JeffreyJ
  • 99
  • 2
  • 12
  • Show us the code you have? What you tried that didn't work? – George Stocker Nov 25 '12 at 01:57
  • I'm just not sure how to do it: IQueryable a = db.ListA.Where(x => x.Active); IQueryable b = db.ListB.Where(x => x.HadReview); a = a.Concat(x => x.LogginID == b.Any.LogginID); – JeffreyJ Nov 25 '12 at 02:01

1 Answers1

2

The most understandable way to do it is to filter out the items in ListA using Where():

IQueryable<LISTA> as = db.ListA.Where(x => x.Active);
IQueryable<LISTB> bs = db.ListB.Where(x => x.HadReview);
as = as.Where(a => bs.Any(b => a.LogginID == b.LogginID) == false);

A way that might be more efficient is to use a Group Join:

var as = from a in db.ListA.Where(x => x.Active)
         join b in db.ListB.Where(x => x.HadReview) 
             on a.LogginID equals b.LogginID into bs
         where bs.Any() == false
         select a;

Edit: If you have two different DataContexts the above will not work. This might:

IQueryable<LISTA> as = db1.ListA.Where(x => x.Active);
IQueryable<LISTB> bs = db2.ListB.Where(x => x.HadReview);
as = as.Where(a => bs.Select(b => b.LogginID).Contains(a.LogginID) == false);

See also: Linq to SQL - How to inner join tables from different Data Context?

Community
  • 1
  • 1
Risky Martin
  • 2,491
  • 2
  • 15
  • 16
  • Great solution thank you, however I have a further issue. Each of the queries are on a different datacontext. Can you assist me which way I can get that to work? – JeffreyJ Nov 25 '12 at 06:07
  • To get around my issue above I have done the following, the Any() method could not be used as it caused another issue. var as = prdb.LISTA.Where(x => x.Active).Select(x => x.StaffUsername).ToArray(); var bs = syndb.LISTB.Where(x => x.ActiveFlag); bs = bs.Where(x => !r.Contains(x.Community.NetworkLogin)); This solution fixed the 'different datacontext' issue and the issue with .Any() which I changed to .Contains() that followed. – JeffreyJ Nov 25 '12 at 08:45
  • @JeffreyJ: There might be a way to perform the query without storing anything in local memory. See my edit. – Risky Martin Nov 25 '12 at 15:46