LINQ to SQL: how to remove an associated child object
Posted on 2010-08-17
I"m using LINQ to SQL in Visual Studio 2010. I have an object 'Client" which has an optional related object "Building". The Building objects are defined in a separate Building table. Each Client may live in one of the bulidings, or they might not. The Client table has a BuildingId column to identify the related Building. There is a foreign key relationship defined (see below).
There is no problem making the original assignment of a building. I just set the BuildingId and call SubmitChanges. But when a client already has a BulidingId, and I want to remove the building from the client, that's when I run into the error. I try code like this:
client.Building = null;
This causes an error: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Client_Building". The conflict occurred in database "MyDB", table "dbo.Building", column 'BuildingId'.
The same thing happens if I use a statement like:
client.BuildingId = null;
The foreign key relationship is defined as:
ALTER TABLE [dbo].[Client] WITH CHECK ADD CONSTRAINT [FK_Client_Building] FOREIGN KEY([BuildingId])
REFERENCES [dbo].[Building] ([BuildingId])
ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Building]
If I make the change manually in the database (from SQL Server Mgmt Studio), I can change the BuildingId column to NULL.
In LINQ to SQL, how am I supposed to remove the building from the client? I just want to change the BuildingId column to NULL.