Sql – linq-to-sql How to get a few rows that don’t match the existing rows

.net-3.5linq-to-sql

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.

Best Answer

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 =(

Related Topic