1

I have 2 tables with a master-detail relationship. We use a DataGridView to handle the detail records. The detail records have fields with lookup values (foreign keys). Instead of setting the ID value of the lookup tables we set the object value. This all works well, instead of one situation. If we add a detail row and set at least one of the lookup fields and remove this row before saving (submitchanges), we receive this error:

An attempt was made to remove a relationship between a TableX and a TableY. However, one of the relationship's foreign keys (TableY.TableXID) cannot be set to null.

This problem was discussed in this question (scroll down to answer of Neil Barnwell), but it doesn't have an answer for this particular problem. The solution "Call Datacontext.GetChanges" doesn't help as Datacontext doesnt expose a method GetChanges and GetChangedset fails with same error.

Community
  • 1
  • 1
Roland Deschain
  • 659
  • 11
  • 21

2 Answers2

1

I assume there is some kind of mistake when removing the row. When you add the detail row to the master row, it is automatically attached to the context. So if you add with:

myMasterRow.DetailsRows.Add(myDetails);

You have to use DeleteOnSubmit to reverse the adding:

context.DetailsRows.DeleteOnSubmit(myDetails);

The data context is smart enough to treat the deletion as "undo add", so the entry will never hit the database.

Anders Abel
  • 67,989
  • 17
  • 150
  • 217
0

It seems that if you set an object of the entity and remove the new record before any submit Linq still thinks you want to add the new record. In this case you have to set all objects that have FK references to null.

See this post which describes the problem and solution in detail: http://social.msdn.microsoft.com/Forums/en/linqtosql/thread/4170c7bb-f727-4e6a-9190-fc268ae4ce4b

Roland Deschain
  • 659
  • 11
  • 21