The EF Core child entity isn't loaded along with the parent

Question

The database

I'm working on a SQL Server database with two tables: INVOICE_HEADER and INVOICE_ROW. INVOICE_HEADER has two fields:

• INTERNAL_ID, type int and the primary key,
• INV_NUM, type varchar(13), which is unique per business logic but not per database structure.

INVOICE_ROW has three:

• INTERNAL_ID, type int and the primary key,
• INV_NUM, type varchar(13),
• INV_ROW_NUM, type int, unique per business logic as well.

Rows from INVOICE_ROW with the same value for INV_NUM as a row from INVOICE_HEADER make up the same invoice in business logic terms, so INVOICE_HEADER.INV_NUM and (INVOICE_ROW.INV_Num, INVOICE_ROW.INV_ROW_NUM) serve as de facto primary keys as far as our queries are concerned.

The values found in INVOICE_HEADER.INTERNAL_ID are different from those found in INVOICE_ROW.INTERNAL_ID, and no field in either table seems to have a value corresponding to that of INTERNAL_ID in the other.

So there is no actual foreign key linking either table to the other.

Do I need to mention the database comes from a third party, its structure is fixed, it has many more tables, and that both INVOICE_HEADER and INVOICE_ROW have many more fields? Well at any rate I just did.

Actually, INTERNAL_ID hardly even shows up in the document describing the database structure, field by field, and only for a few other tables.

Using Entity Framework Core to access the database

I can't use the primary keys defined in the database for the aforementioned reasons.

So I've created two classes:

    public class InvoiceHeader
    {
        public int InternalId { get; set; }
        public string InvoiceNum { get; set; }

        public virtual List<InvoiceRow> InvoiceRows { get; set; }
    }

    public class InvoiceRow
    {
        public int InternalId { get; set; }
        public string InvoiceNum { get; set; }
        public int? RowNum { get; set; }

        public virtual InvoiceHeader InvHeader { get; set; }
    }

And a context:

        public DbSet<InvoiceHeader> RepoInvHeader { get; set; }
        public DbSet<InvoiceRow> RepoInvRow { get; set; }

        protected override void OnModelCreating(ModelBuilder p_mbuModel)
        {
            base.OnModelCreating(p_mbuModel);

            // Table name
            p_mbuModel.Entity<InvoiceHeader>().ToTable("INVOICE_HEADER");

            // Primary key
            p_mbuModel.Entity<InvoiceHeader>().HasKey(t => new { t.InternalId }).
                                               HasName("PK_INTERNAL_ID_INVOICE_HEADER");

            // Fields
            p_mbuModel.Entity<InvoiceHeader>().Property(t => t.InternalId).IsRequired();
            p_mbuModel.Entity<InvoiceHeader>().Property(t => t.InternalId).HasColumnType("int");
            p_mbuModel.Entity<InvoiceHeader>().Property(t => t.InvoiceNum).HasColumnName("INV_NUM");
            p_mbuModel.Entity<InvoiceHeader>().Property(t => t.InvoiceNum).HasColumnType("varchar(13)");
            p_mbuModel.Entity<InvoiceHeader>().Property(t => t.InvoiceNum).HasMaxLength(13);

            // Foreign keys
            p_mbuModel.Entity<InvoiceHeader>().HasMany(h => h.InvoiceRows).
                                               WithOne(r => r.InvHeader).
                                               HasPrincipalKey(h => h.InvoiceNum).
                                               HasForeignKey(r => r.InvoiceNum);

            // Table name
            p_mbuModel.Entity<InvoiceRow>().ToTable("INVOICE_ROW");

            // Primary key
            p_mbuModel.Entity<InvoiceRow>().HasKey(t => new { t.InternalId }).
                                            HasName("PK_INTERNAL_ID_INVOICE_ROW");

            // Fields
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.InternalId).IsRequired();
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.InternalId).HasColumnType("int");
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.InvoiceNum).HasColumnName("INV_NUM");
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.InvoiceNum).HasColumnType("varchar(13)");
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.RowNum).HasColumnName("INV_ROW_NUM");
            p_mbuModel.Entity<InvoiceRow>().Property(t => t.RowNum).HasColumnType("int");

            // Foreign keys
            p_mbuModel.Entity<InvoiceRow>().HasOne(r => r.InvHeader).
                                            WithMany(h => h.InvoiceRows).
                                            HasForeignKey(r => r.InvoiceNum).
                                            HasPrincipalKey(h => h.InvoiceNum);
        }

I feel all those mentions with HasMany, WithOne are necessary to make up for the lack of a true, usable primary key.

The results

I tried the following:

            InvoiceHeader z_objHeader = z_dbcContexteSage.repoInvHeader.Include(h => h.InvoiceRows).FirstOrDefault(h => h.Piece == "XXXX");

On debugging, z_objHeader duly has the values I'd expect for the fields from INVOICE_HEADER, but its InvoiceRows property is null. (I also tried foreach (InvoiceRow z_objRow in z_objHeader.InvoiceRows): it throws a NullReferenceException.)

Now, if I add this and run it as well,

            InvoiceRow z_objRow = z_dbcContexteSage.repoInvRow.FirstOrDefault(r => r.Piece == "XXXX");

looking at z_objHeader.InvoiceRows shows that it now contains one InvoiceRow.

Even better, after running

            List<InvoiceRow> z_lisRows = z_dbcContexteSage.repoInvRow.Where(r => r.Piece == "XXXX").ToList();

z_objHeader.InvoiceRows now has all the rows I'd expect it to have.

So I can work with that, but I still feel that those commands explicitly loading from INVOICE_ROW shouldn't be needed, or even useful.

I've looked far and wide on the Web and I can't seem to find out what I've overlooked or left out.

I guess the catch might be with that official-but-unusable-primary-key/de-facto-primary-key stuff.

Any ideas?

Answer 1

It wasn't something I had forgotten to put in, it was something I (or someone else) had put in at some point.

There was a

using System.Data.Entity;

in the testing class, that was apparently messing the Update() method. Now that it's gone, the property InvoiceRows is correctly loaded.

Thanks to everyone who took the time to read the question!