EF not recognising Key for Relationship

Question

In My EF6 Code First model, i have the following classes:

class User : IdentityUser // so it has public string Id!
{
    public virtual long ItemId { get; set; }
    public virtual Item Item { get; set; }
}

class Item
{
    public virtual long Id { get; set;
    public virtual string UserId { get; set; }
    public virtual User User { get; set; }
}

and in my Context, i do the following:

OnModelCreating(DbModelBuilder mB)
{
    mB.Entity<User>().HasOptional(x => x.Item).WithOptionalPrincipal(x => x.User).WillCascadeOnDelete(false);
}

BUT, my Migration produces the following:

CreateTable(
            "dbo.Items",
            c => new
                {
                    Id = c.Long(nullable: false, identity: true),
                    UserId = c.String(),
                    User_Id = c.String(maxLength: 128),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.Users", t => t.User_Id)
            .Index(t => t.User_Id);

What did i forget to do?

Update: I also tried to add [ForeignKey("User")] to Item.UserId

Update2: I found a workaround, but its not something that is pretty...

OnModelCreating(DbModelBuilder mB)
{
    mB.Entity<User>().HasOptional(x => x.Item).WithMany().HasForeignKey(x => x.ItemId);
    mB.Entity<Item>().HasOptional(x => x.User).WithMany().HasForeignKey(x => x.UserId);
}

Is just generated by the Class-Diagram like that, will change it later — Nefarion, Nov 15 '13 at 10:23
Are you trying to define a one-to-zero-or-one, or a one-to-one, or do you actually want a one-to-many? http://msdn.microsoft.com/en-us/data/jj591620.aspx — Colin, Nov 15 '13 at 10:44
what i want to do is a `0..1 <-> 0..1` relationship :) if that helps — Nefarion, Nov 15 '13 at 10:59

score 0 · Answer 1 · edited May 23 '17 at 11:56

As I understand it, Entity Framework enforces one-to-one relationships by making the 2 entities share the same primary key. That means that there is no need for a separate foreign key column, because the primary key property also acts as the foreign key.

So, remove the surplus foreign keys and change the type of the Item's key to string so that it can refer to the User's Id:

class User : IdentityUser // so it has public string Id!
{
    public virtual Item Item { get; set; }
}

class Item
{
    public string Id { get; set;}
    public virtual User User { get; set; }
}

Now, when both ends of the relationship are required or both sides are optional the Entity Framework cannot identify the dependent and principal. The dependent is the one that gets the foreign key referencing the principal's key.

So try this:

modelBuilder.Entity<User>()
            .HasOptional(f => f.Item). //Item is dependent and 
                                       //gets the foreign key
            .WithOptionalPrincipal(s => s.User);

Proviso

I haven't tried this or done this and the bit I don't get is this. If both ends are optional, then how can the Item have a foreign key to the User that is also a primary key? A primary key is required but an optional foreign key is not. So they cannot be one and the same.

So, if EF has now added An optional foreign key, I would add a unique index to it to ensure it is 1 to 1. As it is nullable, you will need to do it using sql in a migration

Sql("CREATE INDEX ON Item(User_Id) UNIQUE WHERE User_Id IS NOT NULL")

References:

http://msdn.microsoft.com/en-us/data/jj713564

http://msdn.microsoft.com/en-us/data/jj591620.aspx

It says on that page:

When both ends of the relationship are optional, use WithOptionalPrincipal or WithOptionalDependent after the HasOptional method.

Do I define a relationship between two entities on the dependent or the principal?

EF not recognising Key for Relationship

1 Answers1