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.CustomerId == 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!]