locked
Create association that reference alternate keys RRS feed

  • Question

  • Hi,

    Is it possible to create associations that reference alternate keys (non-primary but unique columns). I hope the question would be clear by the following example:

    Table T1
    (
    PrimaryKey : primary key
    AlternateKey : (its also unique)
    ...
    )

    Table T2
    (
    T2Key : primary key
    ForeignKey: (it references T1.AlternateKey)
    ..
    )

    Notice that T2 references T1 using the alternate key, not primary key. So, is it possible to create such an association in EF.


    Thanks for your time.
    Syed Mehroz Alam
    My Blog | My Articles
    Wednesday, August 12, 2009 11:49 AM

Answers

  • As for the first part, yes, that is what I mean. You'll need to change your model, so the foreign id column will be mapped to a scalar property rather than a navigation property (you can just remove the relation and map the property to the foreign key column).

    As for the code, you'll need to use a join query as you wrote above (join is intended to relate tables that EF doesn't recognize their foreign keys).

    As for the second part - indeed the link I posted is for EF4, but I wanted you to get the general idea on how to do this. We do this in our project in EF3.5 with no problem.


    Please mark posts as answers/helpful if it answers your question
    Thursday, August 13, 2009 7:08 AM

All replies

  • You can do this by not telling EF that it's a key value, just use it as a property of an integer type, and write your own code to navigate from that key to the matching instance of T1.

    By the way, that is also the approach for using foreign keys in EF, rather than navigation properties:
    http://blogs.msdn.com/efdesign/archive/2009/03/16/foreign-keys-in-the-entity-framework.aspx
    Please mark posts as answers/helpful if it answers your question
    Wednesday, August 12, 2009 5:28 PM
  • You can do this by not telling EF that it's a key value, just use it as a property of an integer type, and write your own code to navigate from that key to the matching instance of T1.

    I did not get you here. Do you mean to manually do all the stuff using joins?

    var query = from T1 in Table1
                      join T2 in Table2
                          on T1.AlternateKey = T2.ForeignKey
                     .........





    By the way, that is also the approach for using foreign keys in EF, rather than navigation properties:
    http://blogs.msdn.com/efdesign/archive/2009/03/16/foreign-keys-in-the-entity-framework.aspx
    Yes, that's a nice and flexible feature but not available in EF1.



    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, August 13, 2009 6:42 AM
  • As for the first part, yes, that is what I mean. You'll need to change your model, so the foreign id column will be mapped to a scalar property rather than a navigation property (you can just remove the relation and map the property to the foreign key column).

    As for the code, you'll need to use a join query as you wrote above (join is intended to relate tables that EF doesn't recognize their foreign keys).

    As for the second part - indeed the link I posted is for EF4, but I wanted you to get the general idea on how to do this. We do this in our project in EF3.5 with no problem.


    Please mark posts as answers/helpful if it answers your question
    Thursday, August 13, 2009 7:08 AM
  • Actually I wanted to setup an association/navigation to enjoy the powers of entity models. Anyway, thanks for you assistance.



    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, August 13, 2009 7:20 AM