Search code examples
c#sql-serverlinq-to-sql

Linq to SQL parent-child relation direction

I have a parent-child relation among two tables named: Relation and Resource, so that Relation.RESOURCE_ID == Resource.ID

If I write my association into the dbml file, so that Relation --> Resource on RESOURCE_ID = ID then I am unable to add a Resource to a Relation entity. The cardinality of association is one to one.

tbl_res_Resource resoure = new tbl_res_Resource()
{
    //ID is auto calculated
    RESOURCE_KEY = "some key",
    RESOURCE_TYPE = 20,
};

tbl_res_Relation relation = new tbl_res_Relation()
{
    ENTITY_ID = 10,
    ENTITY_TYPE_ID = 10,
    //ID is auto calculated
    //RESOURCE_ID should be inferred
};


relation.Resource = resoure;

context.tbl_res_Relations.InsertOnSubmit(relation);
context.SubmitChanges();

Submit changes will throw

System.Data.Linq.ForeignKeyReferenceAlreadyHasValueExceptio with the message 'Invalid operation due to the current state of the object'.

But if I go to the model (dbml) and reverse the association ( so Resource --> Relation on ID == RESOURCE_ID) then everything work fine.. without changing a line of code.

Keep in mind that all the association are defined only at dbml level, there is no foreign key into database.

What I am missing here?

Solution

  • What I am missing here?

    Nothing, that is correct. It doesn't matter that the cardinality is one-to-one. An association in LINQ2SQL is an abstraction in the application layer that is the equivalent of defining a foreign key in the database layer. And LINQ2SQL expects you to follow the constraints of that association just the same as you would have to follow the constraints of an equivalent foreign key in the database layer.

    what i don't understand is why reversing the 1-1 association (in teh dbml model) will break the association. It's a one to one, it does not have a preferred direction nor constraints at DB level.

    Again this has nothing to do with cardinality (one-to-one) and associations always have a direction (parent to child(ren)) depending on how you set them up. If you look in the Properties window of the association, you'll see it says which object is the Parent and which is the Child.

    In your code you're setting the relation.Resource field to a resource object, ergo saying that the resource is the parent and relation is the child:

    relation.Resource = resoure;

    The above is the equivalent of in the database layer making relation the child table with a foreign key defined on the Resource field relating back to the parent resource table.

    If your association is defined such that relation is the parent, then your above code fails to the constraint because you have a resource child object that you're not assigning a parent to. You would need something like the following to adhere to the constraint:

    resource.Relation = relation;