Solved

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

Posted on 2011-05-09
12
380 Views
Last Modified: 2013-12-16
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.
0
Comment
Question by:wint100
  • 7
  • 5
12 Comments
 
LVL 11

Expert Comment

by:saragani
ID: 35721093
Are you working with Linq to SQL, Entity framework or what?
0
 
LVL 1

Author Comment

by:wint100
ID: 35721127
Sorry it's LINQ to SQL in my Windows Service.
0
 
LVL 11

Expert Comment

by:saragani
ID: 35721437
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
 
LVL 1

Author Comment

by:wint100
ID: 35721577
That looks good, thanks
0
 
LVL 1

Author Comment

by:wint100
ID: 35721735
I get an error:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.
0
 
LVL 11

Expert Comment

by:saragani
ID: 35721753
Please try it and tell me if it worked for you.

Thanks :-)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Expert Comment

by:saragani
ID: 35721792
Oops, I just saw your reply.... I'll try to find a solution.
0
 
LVL 11

Expert Comment

by:saragani
ID: 35721946
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
 
LVL 1

Author Comment

by:wint100
ID: 35722317
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
 
LVL 11

Accepted Solution

by:
saragani earned 500 total points
ID: 35722345
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
 
LVL 11

Expert Comment

by:saragani
ID: 35722366
Oops, missing an s where !BuildingNos.Contains(val.BuildingNo)
0
 
LVL 1

Author Comment

by:wint100
ID: 35722429
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now