How to identify set of records to be deleted using LinqToSql where not in another set?

Question

I have a daily xml feed which contains all records each day. These need to be processed and loaded to a database i.e. those not included in the feed for day2 which were in day1 feed should be deleted and any differences should cause an update on the database. My current approach is to update each included record with a processed date and then delete all records not processed that day.

This however needs each record to be updated each day, and I wondered if there was a better way?

I currently load the XML file and find within my dbcontext each record which currently exists for each record within the xml as follows:

return (from venue in db.Venues 
where venue.id = xmlVenueId
select venue).FirstOrDefault();

if one exists, then I update all of the fields with the current xml file information and if one doesn't exist I create a new one. If all of the fields are the same, then the database is not updated.

When I have processed all records I then need to delete any records which had not been effected.

How should I form a list of all of the venues found (should I extract the primary keys and then add to a List or take the whole Venue) and what would linq2Sql query look like to find all records not in this list?

Answer 1

Not sure that it is the best way, but it seems to work:

I create a List<int> venuePrimaryKeys then perform the following query:

from venue in db.Venues
where ! venuePrimaryKeys.Contains(venue.id)
select venue

I can then traverse the resultant IQueryable and DeleteOnSubmit each venue in the resultant list.