How to apply correctly a relationship of 1 to 1 in EF6

Question

So i wanted to apply a relation of 1 to 1 from one table to another, with navigational properties on each one and a foreign key that is accessable on at least one of the models.

Lets suppose this example

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }

    public int ContactId { get; set; }
    public virtual Contact Contact { get; set; }
}

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual User User { get; set; }
}

modelBuilder.Entity<User>().HasOptional<Contact>(u=> u.Contact)
    .WithRequired(c => c.User).Map(m => m.MapKey("ContactId")).

Similar to the same example used in this stack overflow question:

EF Code First - 1-to-1 Optional Relationship

The problem is that it gives an error saying that the Property name 'ContactId' is already defined.

But i want to have this foreign property defined both at the database and on the model, so that i can use for example linq:

this.dbContextProvider.CurrentContext.User.SingleOrDefault(src => src.ContactId == contactId);

or is this acceptable or very inneficient:

this.dbContextProvider.CurrentContext.User.SingleOrDefault(src => src.Contact.Id == contactId);

This last options will create a join between the two tables while query the database, right?

http://www.entityframeworktutorial.net/code-first/configure-one-to-one-relationship-in-code-first.aspx — Robert Harvey, Jul 29 '21 at 15:44
Don't use modelbuilder for ef 6. It's easier to use annotations: [ForeignKey("Contact")] above public int ContactId {get;set;} — GH DevOps, Jul 29 '21 at 15:50
@GHDevOps Everything that can be done with Data Annotations can also be achieved using the Fluent API / ModelBuilder. The opposite is not true. — 41686d6564 stands w. Palestine, Jul 29 '21 at 20:16

score 0 · Answer 1 · answered Jul 29 '21 at 20:08

The downside of the correct model (i.e. without explicit User.ContactId property) is that in reality it's still a 1:n relationship. The database doesn't enforce 1:1. It's just a FK. The only way to make a true, database-enforced 1:1 association in EF6 is one in which the dependent entity (here: User) has a primary key that's also a foreign key to the principal entity (Contact):

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }

    //public int ContactId { get; set; } <= removed
    public virtual Contact Contact { get; set; }
}

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual User User { get; set; }
}

And:

modelBuilder.Entity<User>()
           .HasRequired<Contact>(u => u.Contact)
           .WithOptional(c => c.User);

This generates the following database schema:

CREATE TABLE [dbo].[Users] (
    [Id] [int] NOT NULL,
    [Username] [nvarchar](max),
    CONSTRAINT [PK_dbo.Users] PRIMARY KEY ([Id])
)

CREATE TABLE [dbo].[Contacts] (
    [ID] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](max),
    CONSTRAINT [PK_dbo.Contacts] PRIMARY KEY ([ID])
)

CREATE INDEX [IX_Id] ON [dbo].[Users]([Id])

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_dbo.Users_dbo.Contacts_Id]
    FOREIGN KEY ([Id]) REFERENCES [dbo].[Contacts] ([ID])

As for querying, in a query like context.Users.Where(u => u.Contact.ID == 4), EF6 will notice that no Contact fields are requested and it will short-circuit the FK to User.Id, i.e. no join. But of course, in this setup, you may as well use context.Users.Where(u => u.Id == 4).

In EF core it would be possible to use your model, with User.ContactId, by this mapping:

modelBuilder.Entity<User>()
    .HasOne(u => u.Contact)
    .WithOne(c => c.User)
    .HasForeignKey<User>(u => u.ContactId);

EF core is smart enough to create a unique index on User.ContactId, so this is a database-enforced 1:1 association with a separate FK.

Which amounts to: if you need a separate FK field but are stuck with EF6, use the model as proposed in the answer to question you refer to. In that model EF will also be smart enough to short-circuit to `User.ContactId` whenever possible. — Gert Arnold, Jul 29 '21 at 20:24

How to apply correctly a relationship of 1 to 1 in EF6

1 Answers1