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
}
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();
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.