linq to sql batch delete

Question

Hi. I have the following DB: Posts which have an Id, Tags also with Id, and TagsToPosts table which have TagsToPosts.PostId => Posts.Id and TagsToPosts.TagId => Tags.Id FK relations. I need to delete multiple items from TagsToPosts in following way. I'm creating IList<Tag> newTags by parsing a string. Each tag have it's name. I want to delete all TagsToPosts items pointing to single post (TagsToPosts.PostId == mypostid) and which points to Tag with name which not in my newTags.

For instance I have one post with Id = 1, three tags: 1 => "tag1", 2 => "tag2", 3 => "tag3" And ManyToMany relations table TagsToPosts: 1 => 1, 1 => 2, 1 => 3 So all three tags are linked to my post. After that I'll create a new IList<Tag> newList = new List<Tag>() by parsing a string. newList contains: 0 => "tag1", 0 => "tag2". Now I want to remove third relation from table TagsToPosts because my new list of tags doesn't contain tag with name "tag3". So I need to find a difference. I know I can find similar items using JOIN but how to find difference?

I want this to happend in one DB query without iterating over each item to delete it.

Answer 1

Have you looked at the Linq Except operator?

For example:

var toDelete = (from t in TagsToPost
                select t).Except(from nt in newList
                                 select nt, new TagComparer());

class TagComparer: IEqualityComparer<TagsToPosts>
{
    public bool Equals(TagsToPosts x, TagsToPosts y)
    {
         return x.Tag.Equals(y.Tag, CompareOptions.Ordinal);
    }
}

Answer 2

You can't do this with LINQ-to-SQL.

LINQ-to-SQL is not good for batch operations - it can't do batch inserts, it can't do batch updates, and it can't do batch deletes. Every object in your collection is treated individually. You can do all the operations in one transaction, but there will always be a query for each record.

MSDN

A better option is to write a stored procedure that will do what you want.

Answer 3

PLINQO supports batch delete operations without retrieving the entities first.

var delete = from t in TagsToPost select t).Except(from nt in newList select nt, new TagComparer())

context.Tags.Delete(delete);

http://plinqo.com

Answer 4

It is possible. Here is good post