One to one optional relationship using Entity Framework Fluent API

Question

We want to use one to one optional relationship using Entity Framework Code First. We have two entities.

public class PIIUser
{
    public int Id { get; set; }

    public int? LoyaltyUserDetailId { get; set; }
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }

    public int PIIUserId { get; set; }
    public PIIUser PIIUser { get; set; }
}

PIIUser may have a LoyaltyUserDetail but LoyaltyUserDetail must have a PIIUser. We tried these fluent approach techniques.

modelBuilder.Entity<PIIUser>()
            .HasOptional(t => t.LoyaltyUserDetail)
            .WithOptionalPrincipal(t => t.PIIUser)
            .WillCascadeOnDelete(true);

This approach didn't create LoyaltyUserDetailId foreign key in PIIUsers table.

After that we tried the following code.

modelBuilder.Entity<LoyaltyUserDetail>()
            .HasRequired(t => t.PIIUser)
            .WithRequiredDependent(t => t.LoyaltyUserDetail);

But this time EF didn't create any foreign keys in these 2 tables.

Do you have any ideas for this issue? How can we create one to one optional relationship using entity framework fluent api?

Answer 1

EF Code First supports 1:1 and 1:0..1 relationships. The latter is what you are looking for ("one to zero-or-one").

Your attempts at fluent are saying required on both ends in one case and optional on both ends in the other.

What you need is optional on one end and required on the other.

Here's an example from the Programming E.F. Code First book

modelBuilder.Entity<PersonPhoto>()
.HasRequired(p => p.PhotoOf)
.WithOptional(p => p.Photo);

The PersonPhoto entity has a navigation property called PhotoOf that points to a Person type. The Person type has a navigation property called Photo that points to the PersonPhoto type.

In the two related classes, you use each type's primary key, not foreign keys. i.e., you won't use the LoyaltyUserDetailId or PIIUserId properties. Instead, the relationship depends on the Id fields of both types.

If you are using the fluent API as above, you do not need to specify LoyaltyUser.Id as a foreign key, EF will figure it out.

So without having your code to test myself (I hate doing this from my head)... I would translate this into your code as

public class PIIUser
{
    public int Id { get; set; }    
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }    
    public PIIUser PIIUser { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<LoyaltyUserDetail>()
  .HasRequired(lu => lu.PIIUser )
  .WithOptional(pi => pi.LoyaltyUserDetail );
}

That's saying LoyaltyUserDetails PIIUser property is required and PIIUser's LoyaltyUserDetail property is optional.

You could start from the other end:

modelBuilder.Entity<PIIUser>()
.HasOptional(pi => pi.LoyaltyUserDetail)
.WithRequired(lu => lu.PIIUser);

which now says PIIUser's LoyaltyUserDetail property is optional and LoyaltyUser's PIIUser property is required.

You always have to use the pattern HAS/WITH.

HTH and FWIW, one to one (or one to zero/one) relationships are one of the most confusing relationships to configure in code first so you are not alone! :)

Answer 2

Just do like if you have one-to-many relationship between LoyaltyUserDetail and PIIUser so you mapping should be

modelBuilder.Entity<LoyaltyUserDetail>()
       .HasRequired(m => m.PIIUser )
       .WithMany()
       .HasForeignKey(c => c.LoyaltyUserDetailId);

EF should create all foreign key you need and just don't care about WithMany !

Answer 3

There are several things wrong with your code.

A 1:1 relationship is either: PK<-PK, where one PK side is also an FK, or PK<-FK+UC, where the FK side is a non-PK and has a UC. Your code shows you have FK<-FK, as you define both sides to have an FK but that's wrong. I recon PIIUser is the PK side and LoyaltyUserDetail is the FK side. This means PIIUser doesn't have an FK field, but LoyaltyUserDetail does.

If the 1:1 relationship is optional, the FK side has to have at least 1 nullable field.

p.s.w.g. above did answer your question but made a mistake that s/he also defined an FK in PIIUser, which is of course wrong as I described above. So define the nullable FK field in LoyaltyUserDetail, define the attribute in LoyaltyUserDetail to mark it the FK field, but don't specify an FK field in PIIUser.

You get the exception you describe above below p.s.w.g.'s post, because no side is the PK side (principle end).

EF isn't very good at 1:1's as it's not able to handle unique constraints. I'm no expert on Code first, so I don't know whether it is able to create a UC or not.

(edit) btw: A 1:1 B (FK) means there's just 1 FK constraint created, on B's target pointing to A's PK, not 2.

Answer 4

public class User
{
    public int Id { get; set; }
    public int? LoyaltyUserId { get; set; }
    public virtual LoyaltyUser LoyaltyUser { get; set; }
}

public class LoyaltyUser
{
    public int Id { get; set; }
    public virtual User MainUser { get; set; }
}

        modelBuilder.Entity<User>()
            .HasOptional(x => x.LoyaltyUser)
            .WithOptionalDependent(c => c.MainUser)
            .WillCascadeOnDelete(false);

this will solve the problem on REFERENCE and FOREIGN KEYS

when UPDATING or DELETING a record

Answer 5

Try adding the ForeignKey attribute to the LoyaltyUserDetail property:

public class PIIUser
{
    ...
    public int? LoyaltyUserDetailId { get; set; }
    [ForeignKey("LoyaltyUserDetailId")]
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
    ...
}

And the PIIUser property:

public class LoyaltyUserDetail
{
    ...
    public int PIIUserId { get; set; }
    [ForeignKey("PIIUserId")]
    public PIIUser PIIUser { get; set; }
    ...
}

Answer 6

This is of no use to the original poster, but for anyone still on EF6 who needs the foreign key to be different from the primary key, here's how to do it:

public class PIIUser
{
    public int Id { get; set; }

    //public int? LoyaltyUserDetailId { get; set; }
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    public int Id { get; set; }
    public double? AvailablePoints { get; set; }

    public int PIIUserId { get; set; }
    public PIIUser PIIUser { get; set; }
}

modelBuilder.Entity<PIIUser>()
    .HasRequired(t => t.LoyaltyUserDetail)
    .WithOptional(t => t.PIIUser)
    .Map(m => m.MapKey("LoyaltyUserDetailId"));

Note that you can't use the LoyaltyUserDetailId field because, as far as I can tell, it can only be specified using the fluent API. (I've tried three ways of doing it using the ForeignKey attribute and none of them worked).

Answer 7

The one thing that is confusing with above solutions is that the Primary Key is defined as "Id" in both tables and if you have primary key based on the table name it wouldn't work, I have modified the classes to illustrate the same, i.e. the optional table shouldn't define it's own primary key instead should use the same key name from main table.

public class PIIUser
{
    // For illustration purpose I have named the PK as PIIUserId instead of Id
    // public int Id { get; set; }
    public int PIIUserId { get; set; }

    public int? LoyaltyUserDetailId { get; set; }
    public LoyaltyUserDetail LoyaltyUserDetail { get; set; }
}

public class LoyaltyUserDetail
{
    // Note: You cannot define a new Primary key separately as it would create one to many relationship
    // public int LoyaltyUserDetailId { get; set; }

    // Instead you would reuse the PIIUserId from the primary table, and you can mark this as Primary Key as well as foreign key to PIIUser table
    public int PIIUserId { get; set; } 
    public double? AvailablePoints { get; set; }

    public int PIIUserId { get; set; }
    public PIIUser PIIUser { get; set; }
}

And then followed by

modelBuilder.Entity<PIIUser>()
.HasOptional(pi => pi.LoyaltyUserDetail)
.WithRequired(lu => lu.PIIUser);

Would do the trick, the accepted solution fails to clearly explain this, and it threw me off for few hours to find the cause