LINQ-to-SQL + One-to-Many + DataBinding deleting

Question

I use LINQ-to-SQL to load data from a database that has two tables in a one-to-many relationship (one Recipe has many Ingredients).

I load a Recipe and LINQ retrieves Ingredient objects into an EntitySet that is binded into a ListBox.

If I want to delete some Ingredients off a Recipe, I get a "An attempt was made to remove a relationship between a Recipe and a Ingredient. However, one of the relationship's foreign keys (Ingredient.RecipeID) cannot be set to null.

I SOLVED this problem using the well known solution by adding 'DeleteOnNull="true"' to the DBML file. But adding this setting only removes the problem when we are deleting Ingredient objects that were retrieved from the DB.

The problem is with the Ingredient objects that were created in code (added to a Recipe) and added to the EntitySet collection of Ingredients and then deleted BEFORE SubmitUpdates is called. Then, the same exception happens again. This usually happens on a new, unsaved recipe when user is adding ingredients to it, makes a mistake and erases an ingredient off a recipe. I added the DeleteOnNull to both 'Association Name="Recipe_Ingredient"' lines in DBML.

How am I supposed to remove such objects? The only solution I see at the moment is that I would load the ingredients into a collection not under the DataContext and then when saving, delete all ingredients off a recipe and add then again from that cache..

Answer 1

        try
        {
            // Needed for existing records, but will fail for new records
            yourLINQDataContext.Ingredients.DeleteOnSubmit(ingredient);
        }
        catch (Exception)
        {
            // Swallow
        }

        yourRecipeObject.Ingredients.Remove(ingredient);