EF Navigation Property Not Loading

Question

I have the following models - Person and Address.

• A Person can exist without a Address
• A Address always belongs to a Person

Classes:

public class Person {    
        // properties

        [ForeignKey("Address")]
        public int? AddressId { get; set; }
        public virtual Address Address { get; set; }
}

public class Address {
        // properties

        [ForeignKey("Person")]
        public int PersonId { get; set; }
        public virtual Person Person { get; set; }
}

PersonConfiguration:

HasOptional(a => a.Address)
                .WithMany()
                .HasForeignKey(u => u.AddressId);

AddressConfiguration:

HasRequired(a => a.Person)
            .WithMany()
            .HasForeignKey(u => u.PersonId);

Problem

SSMS shows that all FKs and constraints are as expected. However when I do the following:

var dbPerson = db.Persons.Include(s => s.Address).ToList();

None of the Person objects (ones that have Addresses) returned have Address or AddressId populated. Everything is null.

When I do the same for db.Address, I get all properties populated as expected - valid relations in-tact. What is causing the principal end of my 1:1 optional relationship to not pull in the dependent entities?

I should note that I do need FK IDs accessible on both entities as defined above.

Answer 1

Let me tell you that the One-One/Optional relationship is not made like this. I am sharing the code how to make 1:1/0 relationship. Also, when you are using fluent API, no need to use Data Annotation attributes. Use only one of them and fluent API is better, because relationship looks very clear.

In 1:1/0 relationship, the foreign keys are not defined separately. Foreign key is defined in any one table only, and the primary key of one entity becomes primary key and foreign key of another related entity. In this example, I made Id field as primary key of Person entity(table) and made the Id as primary key and foreign key of Address entity(table). This is the proper way of 1:1/0 relationship. If we don't follow this convention, the relationship is not made properly and face problems.

Here is the code

public class Person
{
    // properties
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual Address Address { get; set; }
}

public class Address
{
    // properties
    public int Id { get; set; }
    public string Location { get; set; }

    public virtual Person Person { get; set; }
}

public class PersonConfiguration : EntityTypeConfiguration<Person>
{
    public PersonConfiguration()
    {
        ToTable("Person");
        HasKey(p => p.Id);

    }
}

public class AddressConfiguration : EntityTypeConfiguration<Address>
{
    public AddressConfiguration()
    {
        ToTable("Address");
        HasKey(p => p.Id);
        Property(a => a.Id).HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.None);

        HasRequired(p => p.Person)
            .WithOptional(a => a.Address);
    }
}

public class AppObjectContext : DbContext
{
    public AppObjectContext() : base("AppConnectionString")
    {

    }

    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Configurations.Add(new PersonConfiguration());
        modelBuilder.Configurations.Add(new AddressConfiguration());
    }
}

and here is the resultant screenshot

In screenshot you can see, we can accesss Address instance from Person instance and Person instance from Address instance due to mapped relationship.

Here is data which I put in table.

and here is tables structure

Person table SQL Script

CREATE TABLE [dbo].[Person](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Person] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Address Table SQL Script

CREATE TABLE [dbo].[Address](
    [Id] [int] NOT NULL,
    [Location] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.Address] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Address_dbo.Person_Id] FOREIGN KEY([Id])
REFERENCES [dbo].[Person] ([Id])
GO

ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_dbo.Address_dbo.Person_Id]
GO

In response to your Note:

I should note that I do need FK IDs accessible on both entities as defined above.

It is against convention of 1:1/0 relationship, but the better way is as follows.

In one to one relationship, the foreign key and primary keys values are same, it means, if you access the primary key entity of one entity, it is the foreign key and primary key of another entity as well.

For example, if the primary key of person is 20, then the foreign key and primary key of address mapped with this person is also 20. This is the proper way of accessing.