Linq, how to delete items in the middle of a query return?

Question

I got a list of objects in the following query. How to delete items in the middle, saying I want to delete item in 2, 5, and 7 position? thanks

            var archivedPwds = from archivedPwd in db.PasswordArchive
                               where archivedPwd.UserId == userId
                               orderby archivedPwd.DateChanged
                               select archivedPwd;

EDIT:

I am using L2E and want to delete those items from database.

Answer 1

You cannot "delete" items from an IEnumerable or IQueryable. What you can do is filter them out, or build another collection out of the filtering results.

Filtering by index is still done by Where:

var indexesToFilterOut = new[] { 2, 5, 7 };
var filtered = archivedPwds.Where((pwd, i) => !indexesToFilterOut.Contains(i));

Answer 2

Unfortunately, in this case your only option is to iterate through the archivedPwds in a loop and delete them "by hand" (i.e. by calling db.PasswordArchive.DeleteOnSubmit).

What you may try is using the overload of the Select method to get indexes of the items, i. e. by rewriting it to

var archivedPwds = db.PasswordArchive
                     .Where(x => x.UserId == userId)
                     .OrderBy(x => x.DateChanged)
                     .Select((idx, item) => new { Index = idx, Item = itm })

and then use the Index field to filter items you want to delete. Though, I've not tried it in real world and I'm not completely sure if linq2sql will be able to process it.

Answer 3

You can use Jon's method for filtering only the entities to delete:

var pwdsToDelete = archivedPwds.Where((_, i) => (i == 3 || i == 5 || i == 7));

And then delete them all from the entity set:

foreach (var pwd in pwdsToDelete)
{
    db.PasswordArchive.DeleteObject(pwd);
}

Don't forget to save your changes, too:

db.SaveChanges();