Update mapping table in Linq

I have a table Customers with a CustomerId field, and a table of Publications with a PublicationId field. Finally, I have a mapping table CustomersPublications that records which publications a customer can access - it has two fields: CustomerId field PublicationId.

For a given customer, I want to update the CustomersPublications table based on a list of publication ids. I want to remove records in CustomersPublications where the PublicationId is not in the list, and add new records where the PublicationId is in the list but not already in the table.

This would be easy in SQL, but I can't figure out how to do it in Linq.

For the delete part, I tried:

var recordsToDelete = dataContext.CustomersPublications.Where
                      (
                         cp => (cp.CustomerId == customerId)
                               && ! publicationIds.Contains(cp.PublicationId)
                      );

dataContext.CustomersPublications.DeleteAllOnSubmit(recordsToDelete);

... but that didn't work. I got an error:

System.NotSupportedException: Method 'Boolean Contains(Int32)' has no supported translation to SQL

So, I tried using Any(), as follows:

var recordsToDelete = dataContext.CustomersPublications.Where
                      (
                         cp => (cp.Cus开发者_开发技巧tomerId == customerId)
                               && ! publicationIds.Any(p => p == cp.PublicationId)
                      );

... and this just gives me another error:

System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

Any pointers?

[I have to say, I find Linq baffling (and frustrating) for all but the simplest queries. Better error messages would help!]

Wow. Almost by chance, I discovered that the reason I couldn't use Contains in my first example was that my publicationIds was an IList<int> rather than a an int[]. I changed it, and it worked.

Thanks, compiler message author! :-|