none
how to handle multiple foreign keys to the same table? RRS feed

  • Question

  • Hi,
    I have a big legacy database that I'm trying to access via LINQ.  Some tables have more than one foreign key relationships to the same table.  For example, the Agency table has two references to the Person table, one is for the agency contact and the other is for agency head.  When VS generates the entities, Agency gets a Person1 property (for the contact) and a Person2 property for the head.  This can get confusing as there are other tables that have more.  Currently I'm handling this by creating property wrappers like this:

        public partial class Agency
        {
            public Person AgencyContact { get { return this.Person1; } set { value = this.Person1; } }
    
            public Person AgencyHead { get { return this.Person2; } set { value = this.Person2; } }
    
        }
    

    I'm not so sure if this is the best way of handling this complexity.  Any insight from you LINQ to SQL gurus?  Thanks.
    Thursday, April 16, 2009 6:08 PM

Answers

All replies

  • You can change the names of the relationship properties it generates in the visual designer.

    [)amien
    Thursday, April 16, 2009 10:40 PM
    Moderator
  • Hi Damien,
    I used to do what you suggested.  But each time I regenerate the dbml. my changes are overwritten and I would to redo it over again.  That's when I started doing what I'm doing now which I described in my original message.  But I suspect that that is fragile too.  I'm not sure if it is guaranteed that Person1 will always remain as AgencyContact, and Person2 as AgencyHead every regeneration.  Can anyone comment on that?
    Friday, April 17, 2009 11:34 AM
  • Hi Damien,
    I used to do what you suggested.  But each time I regenerate the dbml. my changes are overwritten and I would to redo it over again.  That's when I started doing what I'm doing now which I described in my original message.  But I suspect that that is fragile too.  I'm not sure if it is guaranteed that Person1 will always remain as AgencyContact, and Person2 as AgencyHead every regeneration.  Can anyone comment on that?

    If you regenerate the dbml then you have to apply any customizations (names and what not) again. Better avoid regenerating if you have done any customizations. Sync just the changes instead of regenerating and your customizations will stay and you don't need any extra (non-queryable) properties.

    Besides, the extra properties in your example rely on "Person1" always being AgencyContact and "Person2" always being AgencyHead. What if the order of the foreign keys get swapped and the Person1 and Person2 properties are swapped? That can lead to some very interesting bugs.


    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com
    Saturday, April 18, 2009 4:54 AM
    Answerer