Looking for LINQ code to delete entries when not contained in a List

I have a complex List<custom> with a column for ID. What I need to do is use a LINQ statement to delete all entries in a table, if the rows in the table contain an ID that is NOT contained in the List.

So if the SQL table has rows with ID 2,3,5 and the List<custom> doesn't contain any entries with a matching ID, the rows should be removed from the table.
LVL 1
wint100Asked:
Who is Participating?
 
saraganiConnect With a Mentor Commented:
Doesn't the following code work?


var BuildingNos = from val in list
                    select val.BuildingNo;


            var q = from val in db.ODBCDumps
                    where !BuildingNo.Contains(val.BuildingNo)
                    select val;
0
 
saraganiCommented:
Are you working with Linq to SQL, Entity framework or what?
0
 
wint100Author Commented:
Sorry it's LINQ to SQL in my Windows Service.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
saraganiCommented:
Try the following:

var recordsToDelete =Db.Table.Where(a => !YourList.Any(b => b.ID == a.ID));



I would have wanted to have it like this:

With regular Linq (Not Lambda) you can do:

            var q = from val in db.Table
                    join val2 in YourList on val.ID equals val2.ID
                    select val



However, this will result all the all the items with the existing parallel item on the list. (cause there is no "not equal").
0
 
wint100Author Commented:
That looks good, thanks
0
 
wint100Author Commented:
I get an error:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.
0
 
saraganiCommented:
Please try it and tell me if it worked for you.

Thanks :-)
0
 
saraganiCommented:
Oops, I just saw your reply.... I'll try to find a solution.
0
 
saraganiCommented:
Those are some of the limitations of Linq to SQL (not being able to work with both DB and ram objects together).

Try the following:

            var Ids = from val in YourList
                    select val.ID;


            var q = from val in db.Table
                    where !Ids.Contains(val.ID)
                    select val;


And now you just need to delete all the results of q from the database.
Tell me if it works.

If it doesn't (and with the same error) then I'm out of ideas.
0
 
wint100Author Commented:
For some reason that wouldn't compile, I tried a few different approaches and settled with this:

var recordsToDelete = new List<ODBCDump>();
                        foreach (var c in db.ODBCDumps)
                        {
                            bool keep=false;
                            foreach (var v in list)
                            {
                                if (c.BuildingNo==v.BuildingNo)
                                {
                                    keep = true;
                                }
                            }
                            if (!keep)
                            {
                                recordsToDelete.Add(c);
                            }

                        }
                        //var recordsToDelete = db.ODBCDumps.Where(a => !list.Any(b => b.BuildingNo == a.BuildingNo));

                        

                        
                        foreach (var c in recordsToDelete)
                        {
                            db.ODBCDumps.DeleteOnSubmit(c);
                        }

Open in new window

0
 
saraganiCommented:
Oops, missing an s where !BuildingNos.Contains(val.BuildingNo)
0
 
wint100Author Commented:
It does now, I had to add a cast:

var BuildingNos = (from val in list
                                          select val.BuildingNo).ToList();


                        var recordsToDelete = from val in db.ODBCDumps
                                where !BuildingNos.Contains((int)val.BuildingNo)
                                select val;

Open in new window


Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.