2

So I'm creating a database model using Entity Framework's Code First paradigm and I'm trying to create two tables (Players and Teams) that must share a uniqueness constraint regarding their primary key.

For example, I have 3 Players with Ids "1", "2" and "3" and when I try to create a Team with Id "2", the system should validate uniqueness and fail because there already exists a Player with Id "2".

Is this possible with data annotations? Both these entities share a common Interface called IParticipant if that helps!

Txs in advance lads!

1
  • The use of a GUID is usually for archiving purposes but in your case I don't quite understand why you would need to have a unique ID for 2 specific tables. Could you please fill us in some details on how this unique Id would be used? Otherwise I think Claies is kind of right. If you truly need to do that, it should be doable manually in your constructors but I don't think such a thing is supported in data annotations. Commented Mar 23, 2015 at 18:37

1 Answer 1

2

The scenario you are describing here isn't really ideal. This isn't really a restriction on Entity Framework; it's more a restriction on the database stack. By default, the Id primary key is an Identity column, and SQL itself isn't really supportive of the idea of "shared" Identity columns. You can disable Identity and manage the Id properties yourself, but then Entity Framework cannot automatically build navigation properties for your entities.

The best option here is to use one single participant table, in a technique called "Table Per Hierarchy", or TPH. Entity Framework can manage the single table using an internal discriminator column. Shared properties can be put into the base class, and non-shared properties can be put on the individual classes, which Entity Framework will composite into a single large table in the DB. The main drawback to this strategy is that columns for non-shared properties will automatically be nullable in the database. This article describes this scenario very well.

The more I try to come up with a solution, I realize that this is an example of the XY Problem. There is not really a good solution to this question, because this question is already a proposed solution. There is a problem here that has led you to create an Interface which you suggest requires the entities which are using the interface to have a unique Id. This really sounds like an issue with the design of the Interface itself, as Interfaces should be agnostic to the entity they are applied to. Perhaps providing some code and showing what your problem actually is would be helpful, since the proposed solution you are asking how to implement here isn't really practical.

Sign up to request clarification or add additional context in comments.

1 Comment

Txs for the quick reply! I'll look into TPH and if it happens to solve my problem I'll mark this as an answer :) The question was that I have a system where I have participants that can be both teams and players and I want to be able to query for points per participant, provide a unique id and if there is a match with a player I can be confident that there won't be a match with a team. This can only be done if IDs are unique with both types. As such, your solution of TPH seems to match this and I'll look into it ASAP :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.