Solved

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

Posted on 2011-05-09
12
406 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

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…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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