Sql – Linq2SQL: DeleteAllOnSubmit discriminates entities being re-added

linq-to-sql

Encountering some weird behaviour with Linq:

Situation:

I have a many to many relationship between two entities: UserProfile and BusinessUnit (joined in UserProfile_BusinessUnit table).
Using a ListBox, a user can multi-select BusinessUnits a UserProfile belongs to.
When UPDATING the record, I call:

dataContext.UserProfile_BusinessUnit.DeleteAllOnSubmit(userProfile.BusinessUnits);
dataContext.SubmitChanges();
This ends up deleting the records from UserProfile_BusinessUnit
I then add the entries back in from the multi-select using a foreach

foreach (var b in model.BusinessUnitsSelected)
{
userProfile.BusinessUnits.Add(GetBusinessUnitAsEntity(b.ToString()));
}
dataContext.SubmitChanges();

The Issue:

Only NET NEW BusinessUnits are added back to the relationship. If a BusinessUnit remains selected and is addressed as part of the DeleteAllOnSubmit, it does not get re-added… it's like the dataContext continues to see the record as marked for deletion (even though I've already called SubmitChanges())…

EDIT: I tried removing the SubmitChanges() between calls to see if it would resolve but it only ends up deleting all entries and does not re-add…

EDIT: I added the following to make it work… seems like a hack to me:

 dataContext = null;
 dataContext = new UserProfileDataContext();

Shouldn't there be a way to cause the datacontext to "commit" and move on?

Any ideas on how I can proceed? I thought about dumping the DeleteAllOnSubmit, and explicitly comparing what is going in vs. out… but this seems like a lot more work than I should need to do…

Thanks!

The Code:

dataContext.UserProfile_BusinessUnits.DeleteAllOnSubmit(userProfile.UserProfile_BusinessUnits);
dataContext.SubmitChanges(); // all records deleted

foreach (var b in model.BusinessUnitsSelected)
   {
     userProfile.BusinessUnits.Add(GetBusinessUnitAsEntity(b.ToString()));
   }
cx.SubmitChanges(); // only net new being added

Best Answer

Try this:

foreach (var b in model.BusinessUnitsSelected) 
   { 
     var unit = GetBusinessUnitAsEntity(b.ToString());
     userProfile.BusinessUnits.Add(unit); 
     db.BusinessUnits.InsertOnSubmit(unit);
   }

Where you add to the local collectionand to the main business units table too.

Related Solutions

Sql – Linq2Sql Insert Records To Related Tables

You have to write it this way I think (assuming you have classes Participant and Challenge):

Participant applicant = new Participant();
Participant respondant = new Participant();
//update your participants here

Challenge insertedChallenge = new Challenge();
//update your challenge values here

applicant.Challenges.add(insertedChallenge);
respondant.Challenges1.add(insertedChallenge);

submitChanges();

Linq-to-SQL should automatically assign these properties (Challenges and Challenges) so it can set the key values for you.

Hope it helps.

Sql – LINQ to SQL : Delete a new added child entity before submitchanges – fails

I discovered what the problem is and solved it. I want to share the info here if someone else is having the same issue

First i want to mention an important fact: If Cascade Delete is enabled in the DB , then

DeleteOnNull:=True , DeleteRule:="CASCADE"

will be auto inserted in the code behind the .dbml file without the need to manually add DeleteOnNull=True for a specific association.

The problem is, there was another FKey in the child entity for the OrderType, which is OrderTypeID. I set this value like this:

Dim NormalOrderType = (From ot in db.OrderTypes Where ot.OrderTypeID=1 Select ot).Single
NewOrder.OrderType = NormalOrderType          
OrdersBindingSource.Add(NewOrder)

When a user deletes an order from the grid for a specific customer, LINQ to SQL sets the CustomerID FKey to NOTHING, breaking the association to the parent entity. BUT, it does not set OrderTypeID to NOTHING, keeping the other association active to OrderTypes entity. With an OrderType entity already loaded in memory, LINQ to SQL will think that i still want to insert the child entity but with CustomerID=Nothing, and since this is not allowed (as from the dbml file, Nullable=False), it will raise an exception before it tries to save to DB in the first place.

This problem is solved by doing this:

NewOrder.OrderType = NOTHING

This manually removes the association for the other FKey. Then, simply removing the child entity will cause LINQ to SQL to ignore it when submitChanges is called.

Thank you
Bassam Muhammad.

Best Answer

Related Solutions

Sql – Linq2Sql Insert Records To Related Tables

Sql – LINQ to SQL : Delete a new added child entity before submitchanges – fails

Related Topic