[topic from a discussion; addressing a colleague]
Imagine the very common situation where you have a parent-child relationship like the following, and you want to delete the child (Currency; or better just remove the relation by setting the parent’s child navigation property to null):
Having code (show in an intergration test) like this
[TestMethod]
public void RemoveChildEntityNavigationFromParentEntityProperty_WithManyToOneConstraint_Throws()
{
// save parent and child with a 1:* relationship constraint
int dbGeneratedCityId = -1;
Currency chf = new Currency { Symbol = "CHF", Name = "Schweizer Franken" };
City city = new City { Code = "ZRH", Name = "Zürich", Currency = chf };
this.target.Add(city);
this.target.Save();
dbGeneratedCityId = city.Id;
// get the parent again from the database and get rid of the child
City reloadedCity = this.target.GetById<City>(new City { Id = dbGeneratedCityId });
Assert.IsNotNull(reloadedCity.Currency);
reloadedCity.Currency = null;
this.target.Save();
// assert that the child was properly deleted
City reloadedUpdatedCity = this.target.GetById<City>(new City { Id = dbGeneratedCityId });
Assert.IsNull(reloadedUpdatedCity.Currency);
}
Using SQLProfiler shows that the following queries are emitted for the first code block
Calling Save() in the second block throws the following exception:
Aha, sure, this is due to the “1” in the “1:*” relationship – no cities without a default local currency (not even south european EU contries … sorry, no, not funny).
Let’s change this to “0..1”
Running the same test again shows “green”
emitting the Update statement.
Please note:
- depending on the constraint, there may be no Delete(chf) necessary
- relationship constraints violations throw exceptions
- simply set the parent’s child navigation property to null
- loosening the constraint to “0..1” leaves the child entity in the database
In comparison we can do the following (with “0..1” relationship):
[TestMethod]
public void DeleteChildEntityFromParent_WithManyToOneConstraint_Throws()
{
// save parent and child with a 0..1:* relationship constraint
int dbGeneratedCityId = -1;
Currency euro = new Currency { Symbol = "EUR", Name = "Euro" };
City city = new City { Code = "ATH", Name = "Athens", Currency = euro };
this.target.Add(city);
this.target.Save();
dbGeneratedCityId = city.Id;
// get the parent again from the database and get rid of the child
City reloadedCity = this.target.GetById<City>(new City { Id = dbGeneratedCityId });
Assert.IsNotNull(reloadedCity.Currency);
this.target.Delete(reloadedCity.Currency); // Athens and no more Euro
this.target.Save();
// assert that the child was properly deleted
City reloadedUpdatedCity = this.target.GetById<City>(new City { Id = dbGeneratedCityId });
Assert.IsNull(reloadedUpdatedCity.Currency);
}
and get
Setting the constraint back to “1:*” throws too:
DeleteChildEntityFromParent_WithManyToOneConstraint_Throws threw exception:
System.Data.UpdateException: A relationship from the 'CityCurrency' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'City' must also in the 'Deleted' state.
Guessing the same behavior for NHibernate. Can anybody confirm this?