EF Core loading an empty related property

Question

I have an EmailMessage class, as follows...

c#
  public class EmailMessage {
    public int Id { get; set; }
    public User User { get; set; } = new();
    public DateTime Date { get; set; }
    public int FromId { get; set; }
    public EmailMessageRecipient From { get; set; } = new();
    public List<EmailMessageRecipient> Tos { get; set; } = new();
    public List<EmailMessageRecipient> Ccs { get; set; } = new();
    public EmailMessageRecipient IncomingBcc { get; set; } = new();
    public List<EmailMessageRecipient> OutgoingBccs { get; set; } = new();
    public string Subject { get; set; }
    public string Body { get; set; }
  }

...where EmailMessageRecipient looks like this...

c#
  public class EmailMessageRecipient {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public int? EmailMessageToId { get; set; }
    public virtual EmailMessage EmailMessageTo { get; set; }
    public int? EmailMessageCcId { get; set; }
    public virtual EmailMessage EmailMessageCc { get; set; }
  }

The reason for all the links between the two is that an email will have a From address, and a number (possibly zero) of CC and To addresses. It may also have a BCC address (only one, as the point of BCC is that you don't see the other people BCCed).

The DbContext class contains the following two lines...

c#
    public virtual DbSet<EmailMessage> EmailMessages { get; set; }
    public virtual DbSet<EmailMessageRecipient> EmailMessageRecipients { get; set; }

...and OnModelCreating includes the following...

c#
      builder.Entity<EmailMessage>(entity => {
        entity.HasMany(m => m.Tos)
          .WithOne(r => r.EmailMessageTo)
          .OnDelete(DeleteBehavior.ClientSetNull);
        entity.HasMany(m => m.Ccs)
          .WithOne(r => r.EmailMessageCc)
          .OnDelete(DeleteBehavior.ClientSetNull);
      });

There's nothing else in there at all related to these two classes.

The data for a single message is loaded with the following code...

c#
      _msg = await AppDbContext.EmailMessages
        .Include(em => em.From)
        // Other .Include statements here
        .FirstOrDefaultAsync(m => m.Id == Id);

However, when the message loads, the From navigation property is always a default instance, meaning it is not null, but has an Id of zero, and the two string properties are null.

I ran SQL Server profiler, and looked at the SQL that was used. Running that in a Query Analyser window showed the From Id, Name and Address included correctly in the returned data, so it looks like the data is being sent correctly to the code, but that EF creating the From entity, but is not populating it with the data.

When I did the original migration for these classes, I didn't include a FromId property in the EmailMessage class. When I updated the database, EF created a column in the table, and added a foreign key reference as well. If I query the database from LinqPad, I can see the related data correctly.

As an experiment, I added a FromId property to the EmailMessage class. The migration code that EF generated showed that it was dropping the existing foreign key and recreating exactly the same thing. Nevertheless, just to be sure, I updated the database and tried running again. I could see the correct FromId value on the EmailMessage entity, but again, the From entity had a zero id and null string properties.

Anyone any idea what's going on here? I've done this sort of thing countless times before, and never seen this happen.

Answer 1

If you can write SQL statements with proper JOIN clauses (done by setting relations in SSMS) that produce expected results then consider creating a temp project and reverse engineer your database with EF Power Tools (free Visual Studio extension) followed by writing a lambda query with .Include to see if the expected results are returned.

The nice thing about EF Power Tools you can rerun the reverse engineering process and not lose code so long as you extend custom configurations and such like Interceptors in a partial class. Also you can separate DbSet configurations as shown here.

Answer 2

@Karen Payne MVP Sorry for the delay in replying. I did post a reply, but it seems to have vanished!

Thanks for the suggestion, however I mentioned in my first post that the SQL isn't the issue. If I copy the SQL that EF generates, I can see the data. The problem seems to be that EF recognises that there is a related entity, and so creates an empty object, but then doesn't populate it with the data that was returned from the database.

Any other ideas? Thanks