locked
problem with primary and foreign key of type nvarchar RRS feed

  • Question

  • Let's say there are two tables parent and child. The parent table has a primary key (name) of type nvarchar and the child table has a corresponding foreign key (parentName).

    When a new child object is instantiated the association can be established by assigning the foreign key property (parentName) or the parent property (parent) of the instance. What I have noticed is that if I go with assigning the foreign key property ( childInstance.parentName = parentInstance.Name), SubmitChanges() of the data-context would fail because the INSERT statement generated will contain NULL for the parentName column. And the childInstance.parentName appears to have been set to NULL by SubmitChanges().  However if I assign the parent property directly (childInstance.parent = parentInstance), then the correct INSERT statement is generated and no exception is thrown.

    I have not noticed this problem if the primary key column is of other types. Am I missing something here or is it a bug?

    -Q

    Wednesday, February 1, 2006 9:24 AM

Answers

  • When it comes to foreign-key values, association references take precedence.  SubmitChanges(), assuming you've made a mistake if the reference does not match the foriegn-key values will overwrite your currently foreign-key values so that they match the reference.  A null reference is equated to a null foreign key value, other wise the foreign-key values are set using the associated instance's primary-key values.
    Sunday, February 5, 2006 10:53 PM

All replies

  • When it comes to foreign-key values, association references take precedence.  SubmitChanges(), assuming you've made a mistake if the reference does not match the foriegn-key values will overwrite your currently foreign-key values so that they match the reference.  A null reference is equated to a null foreign key value, other wise the foreign-key values are set using the associated instance's primary-key values.
    Sunday, February 5, 2006 10:53 PM
  • Thanks for the reply, Matt. However, what you described only applies to foreign-keys of type nvarchar. If I set a foreign-key of type int while leaving the association reference null, there is no problem adding the record the to table. Well, since one can work around the problem by avoiding chosing columns of type nvarchar as primary keys, it's not really a big deal.

    However, giving precedence to association reference doesn't always make sense. Sometimes one may have the foreign key value handy and having to retrieve the association would just introduce unnecessary overhead. For example, in a multithreaded app, one might not be able to share entity instances across threads, but foreign key value can be safely shared. This brings up another topic - the lack of entity detachment/attachment feature in Dlinq.

    Why not just let the most recent modification win whether it is made on the foreign key or the association reference?

     

    Thursday, February 9, 2006 5:12 AM