Solved

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

Posted on 2011-05-09
12
395 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 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