5

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?

Copil tembel
  • 399
  • 4
  • 22

3 Answers3

2

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;
Kevin A. Naudé
  • 3,992
  • 19
  • 20
1

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.

Sifford
  • 185
  • 2
  • 9
  • Thank you for this. Option 2 correct for 1 to 0..1, with the primary key on the 0..1 end also a foreign key. Unfortunately, it doesn't work for 0..1 to 0..1 relationship, i.e. any situation where the entities come into existence independently and are subsequently linked, as the entities would not usually have equal primary keys. I'm not sure that we have a good solution for this case. – Kevin A. Naudé Feb 17 '21 at 22:51
  • Having thought about this, I think there may be a general solution for all kinds of 1 to 1 relationships. I'll try it out today and post my findings. – Kevin A. Naudé Feb 17 '21 at 23:35
-1

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

Ashish Garg
  • 35
  • 5