CODEWITHSUNDEEP
c#linqlinq-to-sql
Smithy
Smithy

Reputation: 2190

LINQ return non-matching items query

I have 2 arrays of different types one is from file and one is from a SQL Database - LINQ to SQL. I'm trying to delete non matching items from my database against items I receive via the file. (I mention this in case there is a more efficient way to do what I'm trying to achieve).

I've drafted up a couple of anonymous arrays to show what I am trying to do:

var a = new[] { 
    new { code = "A", subid = 1, test = "dunno" }, new { code = "A", subid = 2, test = "dunno" }, new { code = "A", subid = 3, test = "dunno" },
    new { code = "B", subid = 1, test = "dunno" }, new { code = "B", subid = 2, test = "dunno" }, new { code = "B", subid = 3, test = "dunno" }
};
var c = new[] { 
    new { code = "A", subid = 1 }, new { code = "A", subid = 2 },
    new { code = "B", subid = 1 }, new { code = "B", subid = 2 }
};

I need it to return the items that do not match e.g. new { code = "A", subid = 3 } and new { code = "B", subid = 3 }

var b = (from items in a
         where c.Any(d => d.code == items.code && d.subid != items.subid)
         select items);

and 

var b = (from items in a
        where c.Where(d=> d.code == items.code).Any(d => d.subid != items.subid)
        select items);

I've tried these but they just seem to return all the items. How do I do this?

Upvotes: 0

Views: 2492

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236268

Use Enumerable.Except

var nonMatchingItems = a.Except(c);

Full sample:

var a = new[] { 
    new { code = "A", subid = 1 }, 
    new { code = "A", subid = 2 }, 
    new { code = "A", subid = 3 },
    new { code = "B", subid = 1 }, 
    new { code = "B", subid = 2 }, 
    new { code = "B", subid = 3 }
};

var c = new[] { 
    new { code = "A", subid = 1 }, 
    new { code = "A", subid = 2 },
    new { code = "B", subid = 1 }, 
    new { code = "B", subid = 2 }
};

foreach(vat item in a.Except(c))
    Console.WriteLine(item);

// { code = A, subid = 3 }
// { code = B, subid = 3 }

UPDATE (if you have different types, and want to find matches of some set of fields, then use join of sequences, and remove all items from a which matched some items in c:

var matchingItems = from aa in a
                    join cc in c
                        on new { aa.code, aa.subid } equals // properties subset
                           new { cc.code, cc.subid } // same subset 
                    select aa; // select inner item from join       

var nonMatchingItems = a.Except(matchingItems);

Upvotes: 2

Related Questions