LINQ inner join betwenn Enumerable and DB Table

Question

I'm trying to determine which records to delete from a database when a user submits a form. The page has two CheckBoxList one representing the records before modification and one after.

I can easily get the selected values that need to be deleted like this...

//get the items not selected that were selected before
var oldSelectedItems = from oItem in oldChklSpecialNeeds.Items.Cast<ListItem>()
                       where !(from nItem in newChklSpecialNeeds.Items.Cast<ListItem>()
                               where nItem.Selected
                               select nItem.Value).Contains(oItem.Value)
                           && oItem.Selected
                       select oItem.Value;

now I am trying to do something like this but it isn't allowing it...

var itemsToDelete = from specialNeed in db.SpecialNeeds
                           join oldSelectedItem in oldSelectedItems on specialNeed.SpecialNeedsTypeCd equals oldSelectedItem.Value
                           where specialNeed.CustomerId == customerId

I can easily just use a foreach loop and a .DeleteOnSubmit() for each item but I'm thinking there is a way use functionality of LINQ and pass the whole query result of an inner join to .DeleteAllOnSubmit()

//like so
db.SpecialNeeds.DeleteAllOnSubmit(itemsToDelete);

Any ideas?

Answer 1

What is the error you are getting? Is it a type mismatch between SpecialNeedsTypeCd and oldSelectedItem.Value? Have you just omitted the select in the second Linq statement in this post or is that the problem?

Answer 2

Local collections can be used in LINQ to SQL with the Contains() method. You can try changing the join clause into a where with Contains():

var itemsToDelete = from specialNeed in db.SpecialNeeds
                    where oldSelectedItems.Contains(specialNeed.SpecialNeedsTypeCd)
                       && specialNeed.CustomerId == customerId
                    select ...;