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