CODEWITHSUNDEEP
entity-framework-6edmxef-database-first
mangafaua
mangafaua

Reputation: 449

EF6 database first map one to one relationship

In my database I have one-to-one relationships enforced by making a unique constraint on the foreign key table. This is correctly identified by the Sql Server Management Studio as one-to-one relation:

enter image description here

My problem is that EF6 does not identifies this as a one-to-one relation and generates a Collection on the Person class:

enter image description here

I could easily modify the T4 templates to not generate the collection when there is a unique key on the table IF the itemCollection in the t4 templates would contain the unique key information. But they only contain the foreign key information. I guess this is because the edmgen tool does not extract these information from the db.

Is there a way to force the tool to include all the constraints in the database so I can use this information in the t4 templates?

Upvotes: 5

Views: 2246

Answers (3)

Kevin A. Naudé
Kevin A. Naudé

Reputation: 4070

Option 3: Place a either a unique constraint or unique index on the foreign key.

Uniqueness of the foreign key limits the cardinality of that side of the relationship to at most 1. I tested and confirmed that this is correctly scaffolded when using Sql Server and EF Core 5.0 in a database first scenario.

Why Option 3 May Be Preferable

Option 3 works for both 1-to-0..1 and 0..1-to-0..1 relationships. This means that you can use it quite readily on existing databases without having to change them.

0..1-to-0..1 relationships do occur quite naturally when the entities can exist in their own right before being linked. To extend the example above, if we allow system user accounts that are not related to any person, and we also have people for whom a user account has not yet been created, then that may best be modelled by a 0..1-to-0..1 relationship.

Option 2 remains a great option if you are certain you need a 1-to-0..1 relationship. You can migrate to option 3 later, but it will be more work.

Additional Notes

If you are implementing a [0,1]-to-[0,1] relationship, then your foreign key field will be nullable. A UNIQUE constraint on a nullable foreign key will usually allow multiple rows to contain a NULL because NULL is not a value in conventional SQL. However, Sql Server is an exception and will permit only one row to hold NULL in the foreign key.

The solution is to use a conditional UNIQUE INDEX rather than a UNIQUE CONSTRAINT on Sql Server. Your condition should apply the index only to rows that have a non-null value in the foreign key.

You might create such an index with SQL such as the following.

CREATE UNIQUE INDEX IX_User_Person
ON User (PersonId) WHERE PersonId IS NOT NULL;

Upvotes: 2

Ashish Garg
Ashish Garg

Reputation: 35

bro listen. yor are correct. I have same issue. use primary and foreign key on one column. dont seprate primary and foreign key into two column. this will definately solve your problem

Upvotes: -1

Sifford
Sifford

Reputation: 329

I think the best solution here is to rethink your tables and relationships.

Option 1: If this is a 1 to 1 relationship, can you put everything in one table?

Option 2: Take out the PersonId column. If this is truly a 1 to 1, or even a "1 to 0 or 1" relationship, the tables should share a primary key. The relationship should be between Id and Id. If you do this, EF6 will properly recognize it.

Upvotes: 1

Related Questions