Understanding ForeignKey attribute in entity framework code first

c# code-first entity-framework foreign-keys shared-primary-key

Question

See the following post for some background:

Entity framework one to zero or one relationship without navigation property

I had always thought that ForeignKey was used to show which property in a class held the ForeignKey that determined the navigation property e.g.

public class MemberDataSet
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? DeferredDataId { get; set; }
    [ForeignKey("DeferredDataId")]
    public virtual DeferredData DeferredData { get; set; }
}

However, I discovered on the linked post that this is not right and that as DeferredData's primary key was called Id I actually needed:

public class MemberDataSet
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? DeferredDataId { get; set; }
    [ForeignKey("Id")]
    public virtual DeferredData DeferredData { get; set; }
}

i.e. ForeignKey is used to point to the other class.

I then proceeded to change some of the other references:

public class MemberDataSet
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? DeferredDataId { get; set; }
    [ForeignKey("Id")]
    public virtual DeferredData DeferredData { get; set; }

    public int? SignedOffById { get; set; }
    [ForeignKey("UserId")]
    public virtual UserProfile SignedOffBy { get; set; }
}

However, this failed. Turned out on this one the ForeignKey needed to point to the Id on MemberDataSet class.

public class MemberDataSet
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? DeferredDataId { get; set; }
    [ForeignKey("Id")]
    public virtual DeferredData DeferredData { get; set; }

    public int? SignedOffById { get; set; }
    [ForeignKey("SignedOffById")]
    public virtual UserProfile SignedOffBy { get; set; }
}

I presume this is because this second relationship is one to many whereas the first was one to zero or one, and that effectively the principal end of the relationship differs, but I would appreciate some clarity on this/references to good articles, so I can understand what is happening and exactly what ForeignKey is doing.

I was also looking for clarity in the example above of how public int? DeferredDataId { get; set; } fits into the equation given it is not explicitly linked to DeferredData. I am happy this will match up by convention but how would I explicitly tell it this e.g. if it had a different name? Al the examples I have seen on this talk about using the ForeignKey attribute but this can't be the answer in all cases per above!

All help greatly appreciated - looking to understand the issue rather than fix a specific problem as I have lots of references in my model so need to establish what approach to take with each.

Thanks.

Edit

Added other classes to help:

public class DeferredData
{

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    //other properties
}

public class UserProfile
{

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    //other properties
}
1
27
9/26/2017 2:17:25 PM

Accepted Answer

The required side of the 1..0 relationship MemberDataSet should not have a FK to DeferredData. Instead, DeferredData's PK should also be a FK to MemberDataSet (known as shared primary key)

public class MemberDataSet
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual DeferredData DeferredData { get; set; }
}

public class DeferredData
{
    // DeferredData.Id is both the PK and a FK to MemberDataSet
    [Key]
    [DatabaseGenerated( DatabaseGeneratedOption.None )]
    [ForeignKey( "MemberDataSet" )]
    public int Id { get; set; }

    [Required]
    public virtual MemberDataSet MemberDataSet { get; set; }
}

Fluent API:

modelBuilder.Entity<MemberDataSet>()
    .HasOptional( mds => mds.DeferredData )
    .WithRequired()
    .WillCascadeOnDelete();
27
2/18/2014 2:58:47 PM

Popular Answer

I think that your original idea was correct, with one slight exception. By putting the foreign key on the MemberDataSet you are implying that you would like a zero-or-one-to-many relationship.

In your example, MemberDataSet.DeferredData is optional, and DeferredData can be referred to by many MemberDataSet instances.

In fluent syntax this would be expressed by:

modelBuilder.Entity<MemberDataSet>()
    .HasOptional(dataSet => dataSet.DeferredData)
    .WithMany()
    .HasForeignKey(deferredData => deferredData.DeferredDataId);

In order to make this a one-to-zero-or-one property you can put a unique (where not null) key constraint on MemberDataSet's DeferredDataId column. This would mean that a DeferredData entity could only be referred to by a single MemberDataSet entity.

CREATE UNIQUE INDEX unique_MemberDataSet_DeferredDataId ON MemberDataSet(DeferredDataId) WHERE DeferredDataId IS NOT NULL

Note: This type of filtered key is only available in SQL Server 2008 and up.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow