LINQ to SQL : Why can't I have a Zero to Many Cardinality

Question

I created a foreign key relationship in my database so that I could access another table as a property in this case House.Area

Now if I create a House object and have Area as null I get the following exception on SubmitChanges():

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

Ok i made the example above a little simpler, but a comment below make me think i should give a better example

"House" Table has a column called CityID mapping to a "City" Table connecting on CityID as FK and CityID can't be null

"House" Table also has a column called AreaID mapping to a "Area" Table connecting on CityID as well as AreaID, but AreaID can be null.

House needs to have 1 city always. 1 city can have many houses. House can be in zero or 1 area. 1 area might have zero or many houses.

I update the House.City = new City(....); House.CityID does get a value <- checked

Error Update

An attempt was made to remove a relationship between a Area and a House. However, one of the relationship's foreign keys (House.CityID, House.AreaID) cannot be set to null.

Unless updating the Area is overriding the value of CityID which can explain the error. Comments please. Any way around?

Answer 1

It looks like your database does not allow the field to be null. Load up the House table in SQL server enterprise manager (or whatever database tool you're using) and check that the AreaID field allows nulls

Answer 2

       / \_
      /   \|
     /     \
    /       \
    ---------
    | house |
    ---------

There fore house must contain area

Answer 3

I agree with Orion's response. This error message is not a LINQ error but a database provider error. Remember LINQ is simply a query language layer on top of a backend provider.