?
Solved

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

Posted on 2011-05-09
12
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

 
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
 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

764 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