Linq to sql batch delete

Question

I have the following DB: Poststhat have Id, Tagsalso with a table Idand TagsToPosts, which has a relationship TagsToPosts.PostId => Posts.Idand TagsToPosts.TagId => Tags.IdFK. I need to remove multiple items from TagsToPostsas follows. I create IList<Tag> newTagsby parsing a string. Each tag has a name. I want to remove all elements TagsToPostspointing to one post ( TagsToPosts.PostId == mypostid) and which points to Tagwith a name that is not in mine newTags.

For example, I have one message with Id = 1three tags: 1 => "tag1", 2 => "tag2", 3 => "tag3"And the ManyToMany relationship table TagsToPosts: 1 => 1, 1 => 2, 1 => 3 Thus, all three tags are associated with my message. After that I will create a new IList<Tag> newList = new List<Tag>()one by parsing the string. newListIt contains: 0 => "tag1", 0 => "tag2". Now I want to remove the third relation from the TagsToPosts table because my new tag list does not contain a tag named "tag3". So I need to find the difference. I know that I can find similar elements using JOIN, but how to find the difference?

I want this to happen in one DB query without repeating each element in order to remove it.

Answer 1

You cannot do this with LINQ-to-SQL.

LINQ-to-SQL - , . . , .

MSDN

, , .

Answer 2

Linq Except?

:

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 3

PLINQO .

var delete = from t TagsToPost                t). ( nt newList                                 nt, TagComparer())

context.Tags.Delete();

http://plinqo.com

Answer 4

, , :

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

:

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

It works with only one field, but it can be extended enough for compound fields.