LINQ to SQL have 1:1 relation with each object having a property containing the other's id

1
votes

I am wondering if there is any way in LINQ to SQL to have two tables point to one another's ids, such that there is a 1:1 relationship between them, and to create both of the objects with a single SubmitChanges.

For example, if I have one class Car, with properties CarExtension and CarExtensionID that point to a CarExtension object, and the CarExtension class in turn has Car and mandatory CarID properties that point back to the Car table, is there a way to set this up so that I can create a Car object, create a new CarExtension and assign it to the Car.CarExtension property, and then successfully submit changes?

Currently the SubmitChanges fails because LINQ to SQL doesn't know to assign the id of the newly created car to the .CarID property of the CarExtension, so then when it tries to create the CarExtension the creation fails since the required CarID field is blank.

linq-to-sqlnavigation-properties
CarExtension's primary key should also be the foreign key to Car. It shouldn't have a separate CarID as FK. - Gert Arnold
That doesn't work for me because CarExtension is optional, and I need Linq to Sql to use a left join when it links Car to CarExtension. CarID is a mandatory field in Car, and I can't make it optional there. When the navigation key is mandatory, Linq to Sql generates an inner join between the two tables, which is not what I want. That is why I'm using a different key in CarExtension from in Car, and why I can't make a Linq to SQL navigation property from Car to CarExtension that depends on CarID. - user756366