EF6: optional foreign key always null

Question

I have a C# (C# 7.5) application that is using EF6.

I have a table that contains two foreign keys. Both are one:one relationships and both are required.

When I load a record from the database, the required relationship is populated with the target record.

The issue is that the second relationship is always null, even though the field marked as a foreign key contains a valid value.

[Table("Bob")]
public class bob 
{
    [Key]
    public Guid Id { get; set; }
    public String Name { get; set; }
 
    public Guid WorkingId { get; set; }
    [ForeignKey("WorkingId")]  
    public virtual WorkingTable WorkingTable { get; set; }

    public Guid NotWorkingId { get; set; }
    //[ForeignKey("NotWorkingId")  // commented out as causes exception
    public virtual NotWorkingTable NotWorkingTable { get; set; }
}

[Table("WorkingTable")]
public class WorkingTable 
{
    [Key]
    public Guid Id { get; set; }
    public String AValue { get; set; }
}

[Table("NotWorkingTable")]
public class NotWorkingTable 
{
    [Key]
    public Guid Id { get; set; }
    public String AValue { get; set; }
}

Both relationships are defined in SQL Server as:

ForeignKeyBaseTable:       Bob  
ForeignKeyColumns:         WorkingId  (and NotWorkingId)  
Primary/Unique Key Base:   WorkingTable  (and NotWorkingTable)  
Primary/Unique Key Column: Id  (for both)

The code that I am using inside of the DbContext is

 var results = BobDbSet.AsNoTracking().ToList();

If I examine results[0] as an example, I can see that workingId and WorkingTable are populated, but while NotWorkingId has a valid value (resolves to the target in T-SQL), NotWorkingTable is null.

Note: I am using ASP.NET running on .NET 4.8 (not ASP.NET Core or EF Core).

Does anyone have an idea on how to diagnose this issue? As far as I am aware, adding the foreign key property onto the virtual class definition causes EF to try and match it with the first key defined in that class (in this case Id).

When I examine things with the debugger, I can see the EF RelationshipManager only has an entry for the first relationship and not the second.

What exception are you getting that caused commenting out the FK link?? //[ForeignKey("NotWorkingId") // commented out as causes exception (besides missing the trailing ]) It will help to see the actual entities with the problem, trimmed down to just the navigation properties and FK definitions. Attempts to simplify an example often hides the actual problem. — Steve Py
– Steve Py, Commented Jul 3 at 3:30
Both your WorkingTable and NotWorkingTable had a data annotation [key] (with a lower-case "k") - this is incorrect and I fixed it in your code - it must be [Key] with an upper-case "K" - could that be the problem? — marc_s
– marc_s, Commented Jul 3 at 3:37
As said, it's essential to have code that causes the same (presently unknown) exception when the annotation is active. — Gert Arnold
– Gert Arnold, Commented Jul 3 at 7:06
Apologies for the ] and Key typos. The application just crashes to desktop (no exception block). — AndyW
– AndyW, Commented Jul 4 at 2:29

AndyW · Accepted Answer · 2025-07-09 21:34:02Z

-1

Eventually worked it out.

The attribute declaration needed to have both the virtual keyword (even though I am not using lazy loading) and the Required attribute.

[Table("Bob")]
public class bob 
{
    [Key]
    public Guid Id { get; set; }
    public String Name { get; set; }
 
    public Guid WorkingId { get; set; }
    [Required, ForeignKey("WorkingId")]  
    public virtual WorkingTable WorkingTable { get; set; }

    public Guid NotWorkingId { get; set; }
    [Required, ForeignKey("NotWorkingId")  
    public virtual NotWorkingTable NotWorkingTable { get; set; }
}

The required attribute basically says this is one:one as there is no collection definition following it.

answered Jul 9 at 21:34

AndyW

5231 gold badge5 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gert Arnold Jul 12 at 8:55

Since you never posted a minimal reproducible example it's impossible to tell why these things would make a difference. At face value it looks very unlikely.

AndyW Jul 14 at 23:19

That is the code that works - the documentation explains what the additional keywords do. Not sure why the need to produce a 'minimal reproducible example' when the original example given is the same thing. I could have course just closed the question without providing an answer which would have been inconvenient for future readers - let me know if you prefer that - happy to delete my answer.

Collectives™ on Stack Overflow

EF6: optional foreign key always null

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related