3

I need to have an entity that has two separate candidate keys where one of them is the primary key, which is a surrogate identity key, and another one that is an alternate key representing the real unique attribute. I need to have them both separately in my entity. Please note that I am not referring to composite or multi-column keys. Anyhow I need to have this on one of my entities in ADO.NET data model. Is it possible to do that? If yes please guide me.

The reason to have the second key is that I need another entity to have an association (and foreign key) relationship with first entity on the that key.

Update: I found a very similar situation to mine in the following question: http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/a248632a-d305-4c15-8e57-6742457cca94 It seems that EF v1 does not support this feature. Does anybody know if V4(literally the second version) has this feature or not? I have found the following but it does not seem to show any clues that this feature has been added to the current version: http://blogs.msdn.com/b/adonet/archive/2009/05/11/update-on-the-entity-framework-in-net-4-and-visual-studio-2010.aspx

amirmonshi
  • 649
  • 5
  • 19
  • 2
    Your question is very unclear. You seem to have two keys that are unique over the space of this table, one of them (an auto-gen integer sequence key) and another one a unique ID. You want both of them to show up as primary keys of the table? In this case no, you can only pick one as your primary key. There is exactly one primary key per table. But you can build an index on the other column. – Stephen Chung Mar 15 '11 at 03:21
  • 1
    @Stephen OK maybe that's because I have expressed the problem in relational data model language. I know you can only have one primary key of course. That's why I have referred to the other one as alternate key as they do in relational databases language. And it is really a unique attribute. You can easily have this uniqueness constraint over any non-primary-key attribute and make it an "alternate key". I just want to know if it is possible to inflict this constraint from ADO.NET EDM/EDM designer or not? or does it recognize this constraint if I directly add it to the underlying table? – amirmonshi Mar 15 '11 at 03:32
  • You refer to EDM. Are you using the Entity Framework? – Stephen Chung Mar 15 '11 at 06:12
  • 1
    Been a while since I touched the Entity Framework, but I think you can define a UNIQUE constraint in the EDM designer... Let me come back later. – Stephen Chung Mar 15 '11 at 06:22
  • 1
    @Stephen Thank you very much. But please note that I not only need it to be unique but also it should be a key. Because you can only define associations/foreign keys on candidate keys. And I need to define an association based on that alternate key. – amirmonshi Mar 15 '11 at 06:27
  • 1
    I am not sure you can do that in EF, at least in the current version. EF associations can only be defined on primary keys, asfaik... – Stephen Chung Mar 15 '11 at 07:55
  • @Stephen Thank you so much for your time Stephen. It's really appreciated. – amirmonshi Mar 15 '11 at 08:19
  • Possible duplicate of [Enitity Framework 6.1.3 Mapping Foreign key to non primary key](http://stackoverflow.com/questions/30037798/enitity-framework-6-1-3-mapping-foreign-key-to-non-primary-key) – ErikE Mar 09 '17 at 22:05

2 Answers2

4

Entity framework doesn't support unique keys. In entity model your second key will be field as any other. You will not be able to create relations on that key and EF will not check that value of the key is unique. But if your model will be based on existing database with unique keys you will have this check on database layer.

Support for unique keys has been hinted at by the EF team but remains unavailable as of .NET 4.5.

Technobabble
  • 996
  • 9
  • 25
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • +1 Thanks for your answer. I also suspected that should be the case reading the links that I have posted. BTW, do you have any idea why people have downvoted my question? is it that bad?!:)) – amirmonshi Mar 15 '11 at 06:45
  • Upvoted question. EF support for both primary and surrogate keys is a relevant topic. Databases support foreign key constraints on alternate unique indexes, so it would make sense for EF to eventually recognize those relationships. – Technobabble Mar 29 '13 at 18:45
0

If you are using Code First and the fluent API, creating two seperate DbContexts might be a good solution for you. You can write separate EntityTypeConfiguration classes for each of the keys that you want to use in navigation properties. We ran into this issue on our project as well and I wrote a blog post describing our solution: http://mmilleruva.blogspot.com/2013/10/working-with-legacy-database-schemas.html

mmilleruva
  • 2,110
  • 18
  • 20