linq-to-sql How can I get a few rows that don't match my existing rows?

Question

I have a few rows of data pulled into business objects via linq-to-sql from large tables.

Now I want to get a few rows that don't match to test my comparison functions.

Using what I thought would work I get a NotSupportedException:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

Here's the code:

//This table has a 2 field primary key, the other has a single
var AllNonMatches = from c in dc.Acaps
   where !Matches.Rows.Any((row) => row.Key.Key == c.AppId & row.Key.Value == c.SeqNbr)
   select c;

foreach (var item in AllNonMatches.Take(100)) //Exception here
{}

The table has a compound primary key: AppId and SeqNbr.

The Matches.Rows is defined as a dictionary of keyvaluepair(appid,seqnbr).

and the local sequence it is referring to appears to be the local dictionary.

Answer 1

Could you provide more information on the structure and the name(s) of the table(s) plz?

Not sure what you're trying to do...

edit:

Ok.. I think I get it now...

It appears you can't merge/join local tables (dictionary) with a SQL table. If you can, I'm afraid I don't know how to do it.

The simplest solution I can think of is to put those results in a table ("Match" for instance) with foreign keys related to your table "Acaps" and then use linq-to-sql, like:

var AllNonMatches = dc.Acaps.Where(p=>p.Matchs==null).Take(100).ToList();

Sorry I couldn't come up with any better =(

Answer 2

What about this:

var AllNonMatches = from c in dc.Acaps
   where !(Matches.Rows.ContainsKey(c.AppId) && Matches.Rows.ContainsValue(c.SeqNbr))
   select c;

That will work fine. I have also used a bitwise AND operator (&&) - I think thats the right term to help improve performance over the standard AND operator.