CODEWITHSUNDEEP
linqlinq-to-sql
Dmytro Leonenko
Dmytro Leonenko

Reputation: 1511

linq to sql batch delete

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.

Upvotes: 5

Views: 1800

Answers (4)

user2242092
user2242092

Reputation: 11

My solution which lets you make deletions determined by a class field:

public static void DeleteByPropertyList<T, R>(List<T> listToDelete, Expression<Func<T, R>> getField, DataContext context) where T : class {
    List<List<string>> partitionedDeletes = listToDelete.Select(d => string.Format("'{0}'", getField.Compile()(d).ToString())).ToList().Partition<string>(2000).ToList();
    Func<Expression<Func<T, R>>, string> GetFieldName = propertyLambda => ((MemberExpression)propertyLambda.Body).Member.Name;
    MetaTable metaTable = context.Mapping.GetTable(typeof(T));
    string tableName = string.Format("{0}.{1}", metaTable.Model.DatabaseName, metaTable.TableName);
    foreach (List<string> partitionDelete in partitionedDeletes) {
        string statement = "delete from {0} where {1} in ({2})";
        statement = string.Format(statement, tableName, GetFieldName(getField), string.Join(",", partitionDelete));
        context.ExecuteCommand(statement);
    }
}

public static IEnumerable<List<T>> Partition<T>(this IList<T> source, int size) {
    for (int i = 0; i < Math.Ceiling(source.Count / (double)size); i++)
    yield return new List<T>(source.Skip(size * i).Take(size));
}

Usage:

    List<OrderItem> deletions = new List<OrderItem>();
    // populate deletions
    LinqToSqlHelper.DeleteByPropertyList<OrderItem, long>(deletions, oi => oi.OrderItemId, context);

It only works with a single field, but it could be extended to composite fields easily enough.

Upvotes: 0

Shannon Davidson
Shannon Davidson

Reputation: 216

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

Upvotes: 0

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28738

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.

Upvotes: 3

Davy Landman
Davy Landman

Reputation: 15438

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);
    }
}

Upvotes: 0

Related Questions