How can I write a LINQ to SQL query to update tags?

I have an image site where users can开发者_JAVA技巧 tag photos much like you can tag a question on Stackoverflow.

I have the following tables:

Images [ID, URL, etc]   
Tags  [ID, TagName]    
ImageTag  [TagID, ImageID]

I want to write a method with the signature:

public void UpdateImageTags(int imageId, IEnumerable<string> currentTags)

This method will do the following:

• Create any new Tags in currentTags that don't already exist in the Tags table.
• Get the old ImageTag's for an image.
  • Delete any ImageTag's that no longer exist in the currentTags
  • Add any ImageTag's that are new between the currentTags and oldTags.

Here is my attempt at that method:

public void UpdateImageTags(int imageId, IEnumerable<string> currentTags)
{
    using (var db = new ImagesDataContext())
    {
        var oldTags = db.ImageTags.Where(it => it.ImageId == imageId).Select(it => it.Tag.TagName);
        var added = currentTags.Except(oldTags);
        var removed = oldTags.Except(currentTags);

        // Add any new tags that need created
        foreach (var tag in added)
        {
            if (!db.Tags.Any(t => t.TagName == tag))
            {
                db.Tags.InsertOnSubmit(new Tag { TagName = tag });
            }
        }               
        db.SubmitChanges();

        // Delete any ImageTags that need deleted.
        var deletedImageTags = db.ImageTags.Where(it => removed.Contains(it.Tag.TagName));
        db.ImageTags.DeleteAllOnSubmit(deletedImageTags);

        // Add any ImageTags that need added.
        var addedImageTags = db.Tags.Where(t => added.Contains(t.TagName)).Select(t => new ImageTag { ImageId = imageId, TagId = t.TagId });
        db.ImageTags.InsertAllOnSubmit(addedImageTags);
        db.SubmitChanges();
    }
}

However, this fails on the line:

db.ImageTags.DeleteAllOnSubmit(deletedImageTags);

With the error:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

Is there an easier way I can handle the operation of adding new tags, deleting old ImageTags, adding new ImageTags in LINQ to SQL?

Seems like this would be easiest

public void UpdateImageTags(int imageId, IEnumerable<string> currentTags) 
{ 
    using (var db = new ImagesDataContext()) 
    { 
        var image = db.Images.Where(it => it.ImageId == imageId).First()
        image.Tags.Clear();
        foreach(string s in currentTags)
        {
            image.Tags.Add(new Tag() { TagName = s});
        } 
        db.SubmitChanges();  
    }
 }

This might have to be modified slightly for LinqtoSQL. EF is what i have been using most recently. Also this is dependent on Lazy loading being enabled. If it is not, you will have to force the include of the image tags.

Here is a helper method to deal with many-to-many relationships:

public static void UpdateReferences<FK, FKV>(
    this EntitySet<FK> refs,
    Expression<Func<FK, FKV>> fkexpr,
    IEnumerable<FKV> values)
  where FK : class
  where FKV : class
{
  Func<FK, FKV> fkvalue = fkexpr.Compile();
  var fkmaker = MakeMaker(fkexpr);
  var fkdelete = MakeDeleter(fkexpr);

  var fks = refs.Select(fkvalue).ToList();
  var added = values.Except(fks);
  var removed = fks.Except(values);

  foreach (var add in added)
  {
    refs.Add(fkmaker(add));
  }

  foreach (var r in removed)
  {
    var res = refs.Single(x => fkvalue(x) == r);
    refs.Remove(res);
    fkdelete(res);
  }
}

static Func<FKV, FK> MakeMaker<FKV, FK>(Expression<Func<FK, FKV>> fkexpr)
{
  var me = fkexpr.Body as MemberExpression;

  var par = Expression.Parameter(typeof(FKV), "fkv");
  var maker = Expression.Lambda(
      Expression.MemberInit(Expression.New(typeof(FK)),
        Expression.Bind(me.Member, par)), par);

  var cmaker = maker.Compile() as Func<FKV, FK>;
  return cmaker;
}

static Action<FK> MakeDeleter<FK, FKV>(Expression<Func<FK, FKV>> fkexpr)
{
  var me = fkexpr.Body as MemberExpression;
  var pi = me.Member as PropertyInfo;

  var assoc = Attribute.GetCustomAttribute(pi, typeof(AssociationAttribute))
    as AssociationAttribute;

  if (assoc == null || !assoc.DeleteOnNull)
  {
    throw new ArgumentException("DeleteOnNull must be set to true");
  }

  var par = Expression.Parameter(typeof(FK), "fk");
  var maker = Expression.Lambda(
      Expression.Call(par, pi.GetSetMethod(),
        Expression.Convert(Expression.Constant(null), typeof(FKV))), par);

  var cmaker = maker.Compile() as Action<FK>;
  return cmaker;
}

Usage:

IEnumerable<Tag> values = ...;
Image e = ...;
e.ImageTags.UpdateReferences(x => x.Tag, tags);