Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

LINQ to SQL: how to remove an associated child object

Posted on 2010-08-17
13
Medium Priority
?
1,694 Views
Last Modified: 2013-11-11
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;
db.SubmitChanges();

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])
GO
ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_Building]
GO

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.

Thanks,
Jeff
0
Comment
Question by:jblindberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33460529
Hi jblindberg;

If you have a samll test database can you zip it up and upload to here?

Fernando
0
 
LVL 27

Expert Comment

by:nmarun
ID: 33461103
FernandoSoto... do you ever sleep?Arun
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33464932
Humm sleep, humm what is that?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:jblindberg
ID: 33469092
Fernando, here's the database.  Does this format work for you?

Jeff
hs.zip
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33470022
Hi Jeff;

Unable to restore what I think is a backup of your database with an error stating that it hast two families but only one was provided. The best way is just to zip the database file, .mdf, and the log file into one zip file.

Fernando
0
 

Author Comment

by:jblindberg
ID: 33481077
OK, here's a zip of the .mdf file.  Hope this will be what you need to look at this problem.

Jeff

HealthSetDB.zip
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33481372
Hi Jeff;

I downloaded and was able to attach the DB to the server.

I created a new project, added a Entity Framework Model, and used the code snippet shown below and had no problem deleting the BuildingId from a Client record and save it.

Did you modify the model once you created it or did you just used all the default values?

Fernando
HealthSetEntities db = new HealthSetEntities();

Client client = (from c in db.Clients
                 where c.BuildingId == 4
                 select c).FirstOrDefault();

client.BuildingId = null;
db.SaveChanges();

Open in new window

0
 

Author Comment

by:jblindberg
ID: 33481597
I'm not using Entity Framework Model, I'm using LINQ to SQL.  So I don't have a HealthSetEntities class.

I generated the dbml and associated designer.cs files using the tools in Visual Studio 2010.

I get the DataContext (System.Data.Linq.DataContext) this way:
HealthSetDataContext context = new HealthSetDataContext(Settings.HSetConnStringHelper());
(uses a helper method to return either the test or production connection string)

In the Client class, the Building property is defined like this:
this._Building = default(EntityRef<Building>);

I haven't made any changes to the designer.cs file, it's just as it was auto-generated.

Jeff
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33481932
Hi Jeff;

I just also tried it using Linq to SQL with the code in the snippet below with no issues as well. Seeming you are still having issues please post the actual code you are using.

Fernando
DataClasses1DataContext db = new DataClasses1DataContext();

Client client = (from c in db.Clients
                 where c.BuildingId == 4
                 select c).FirstOrDefault();

client.BuildingId = null;
db.SubmitChanges();

Open in new window

0
 

Author Comment

by:jblindberg
ID: 33489432
OK, I figured out what was happening.  
First, even though I thought I was setting the BuildingId to null, actually I was setting it to zero.  
But when I tried actually set BuildingId to null (like your example above), I got the following exception when trying to set the Client.BuildingId property:

System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException
Operation is not valid due to the current state of the object.
at HSet.HealthSet.Client.set_BuildingId(Nullable`1 value) in HealthSet.designer.cs:line 1250

Apparently LINQ to SQL wasn't happy about changing the BuildingId property to null when it already had a Building object loaded.  

So next I tried setting the Building property itself to null, like the code below.
This made LINQ happy, and everything is now working.  

Thanks for your help.
Jeff
if (buildingId == 0)
{
  if (c.Building != null)
  {
    c.Building = null; // NOTE nullify the Building object property, not the BuildingId
  }
}
else
{
  c.BuildingId = buildingId;
}
db.SubmitChanges();

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33489751
Hi jblindberg;

I believe you will find that is going to cause an issue. Because you are removing the reference to the Building table. For example if you do the following two lines of code after getting the Client with BuildingId of 4 :

client.Building = null;
db.SubmitChanges();

If you attempt to do this just after the above two line you most likely come accross an error.

client.BuildingId = 4;
db.SubmitChanges();

You should be nulling the Building and not the reference.

Fernando
0
 

Accepted Solution

by:
jblindberg earned 0 total points
ID: 33490069
Actually it works correctly, with the scenario you describe above as well as several sequences of operations I tested.  

Note that I want to remove the reference, I don't want to affect the actual Building object, I just want to change the Client so that it doesn't have an associated Building anymore.  But other clients will still have the same building.

I think the automatically generated LINQ to SQL code in the designer.cs file takes care of correctly removing the reference on both sides.  See the set code for the Client.Building property below, from the generated designer.cs file.

Also when I set the Client.BuildingId property, it correctly attaches the Building object to the Client.  See the set code for Client.BuildingId property in the 2nd part of the code snippet below.

However, in looking at the code in the BuildingID set operation (below), I am concerned by the check of this._Building.HasLoadedOrAssignedValue.  Perhaps that is the possible error you were warning about. So I'm thinking that it is safer to set the Client.Building property with another Building object instead of just changing the BuildingId.  So I now use code like this:

if (buildingId == 0)
  c.Building = null;
else
  c.Building = c.GetBuildingById(buildingId);

(The c.GetBuildingById() method uses the same DataContext that was used to load the Client.)

Does that look right to you?

Jeff

// set for Client.Building property
Building previousValue = this._Building.Entity;
if (((previousValue != value) 
  || (this._Building.HasLoadedOrAssignedValue == false)))
{
  this.SendPropertyChanging();
  if ((previousValue != null))
  {
    this._Building.Entity = null;
    previousValue.Clients.Remove(this);
  }
  this._Building.Entity = value;
  if ((value != null))
  {
    value.Clients.Add(this);
    this._BuildingId = value.BuildingId;
  }
  else
  {
    this._BuildingId = default(Nullable<int>);
  }
  this.SendPropertyChanged("Building");
}

// set for Client.BuildingId property
if ((this._BuildingId != value))
{
  if (this._Building.HasLoadedOrAssignedValue)
  {
    throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
  }
  this.OnBuildingIdChanging(value);
  this.SendPropertyChanging();
  this._BuildingId = value;
  this.SendPropertyChanged("BuildingId");
  this.OnBuildingIdChanged();
}

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 33490109
OK Jeff
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question