Entity Framework Core : DbContext not linking entities

Question

I'm trying to use ASP.NET Core 2.2.0 with Entity Framework Core and I'm facing a bit of troubles.

I want to display a list of pictures of different celestial objects I took (nebula, galaxies, etc...).

The data should look like this:

I created my entities, made the migration, and everything seemed to work great. I inserted a few data manually so I could check if the insert was ok.

CelestialObjectType nebuleuse = new CelestialObjectType { Name = "Nébuleuse", Definition = "Nuage interstellaire de gaz et de poussière" };
CelestialObjectType galaxie = new CelestialObjectType { Name = "Galaxie", Definition = "Vaste ensemble d'étoiles et de matière interstellaire dont la cohésion est assurée par la gravitation" };

context.CelestialObjectTypes.Add(nebuleuse);
context.CelestialObjectTypes.Add(galaxie);
 
CelestialCatalog messier = new CelestialCatalog { Name = "Messier" };
CelestialCatalog ngc = new CelestialCatalog { Name = "NGC" };
CelestialCatalog ugc = new CelestialCatalog { Name = "UGC" };

context.CelestialCatalogs.Add(messier);
context.CelestialCatalogs.Add(ngc);
context.CelestialCatalogs.Add(ugc);

CelestialObject m42 = new CelestialObject
        {
            Name = "M42 - Grande nébuleuse d'orion",
            CelestialObjectType = nebuleuse,
            DiscoveryDate = new DateTime(1610, 11, 26)
        };
CelestialObject m81 = new CelestialObject
        {
            Name = "M81 - Galaxie de Bode",
            CelestialObjectType = galaxie,
            DiscoveryDate = new DateTime(1774, 12, 31),
        };
CelestialObject m82 = new CelestialObject
        {
            Name = "M82 - Galaxie du cigare",
            CelestialObjectType = galaxie,
            DiscoveryDate = new DateTime(1774, 12, 31),
        };

context.CelestialObjects.Add(m42);
context.CelestialObjects.Add(m81);
context.CelestialObjects.Add(m82);

CelestialObjectCatalog messier42 = new CelestialObjectCatalog { CelestialCatalog = messier, CelestialObject = m42, Rank=42 };
CelestialObjectCatalog ngc1976 = new CelestialObjectCatalog { CelestialCatalog = ngc, CelestialObject = m42, Rank=1976 };

CelestialObjectCatalog messier81 = new CelestialObjectCatalog { CelestialCatalog = messier, CelestialObject = m81, Rank = 81 };
CelestialObjectCatalog ngc3031 = new CelestialObjectCatalog { CelestialCatalog = ngc, CelestialObject = m81, Rank = 3031 };
CelestialObjectCatalog ugc5318 = new CelestialObjectCatalog { CelestialCatalog = ugc, CelestialObject = m81, Rank = 5318 };

CelestialObjectCatalog messier82 = new CelestialObjectCatalog { CelestialCatalog = messier, CelestialObject = m82, Rank = 82 };
CelestialObjectCatalog ngc3024 = new CelestialObjectCatalog { CelestialCatalog = ngc, CelestialObject = m82, Rank = 3034 };
CelestialObjectCatalog ugc5322 = new CelestialObjectCatalog { CelestialCatalog = ugc, CelestialObject = m82, Rank = 5322 };

context.Add<CelestialObjectCatalog>(messier42);
context.Add<CelestialObjectCatalog>(ngc1976);

context.Add<CelestialObjectCatalog>(messier81);
context.Add<CelestialObjectCatalog>(ngc3031);
context.Add<CelestialObjectCatalog>(ugc5318);

context.Add<CelestialObjectCatalog>(messier82);
context.Add<CelestialObjectCatalog>(ngc3024);
context.Add<CelestialObjectCatalog>(ugc5322);

Picture m42Picture = new Picture
        {
            Date = new DateTime(2019, 12, 26),
            Path = "~/images/Ciel profond/M42 - Nébuleuse d'Orion.jpg",
            SingleExposureTime = new TimeSpan(0, 0, 20),
            RawNumber = 14,
            DarksNumber = 7,
            OffsetsNumber = 8
        };

Picture m8182Picture = new Picture
        {
            Date = new DateTime(2020, 02, 24),
            Path = "~/images/Ciel profond/M81-M82-Galaxies.jpeg",
            SingleExposureTime = new TimeSpan(0, 0, 30),
            RawNumber = 46,
            DarksNumber = 12,
            OffsetsNumber = 15
        };

context.Pictures.Add(m42Picture);
context.Pictures.Add(m8182Picture);

CelestialObjectPicture m42ObjectPicture = new CelestialObjectPicture { CelestialObject = m42, Picture = m42Picture };
CelestialObjectPicture m81ObjectPicture = new CelestialObjectPicture { CelestialObject = m81, Picture = m8182Picture };
CelestialObjectPicture m82ObjectPicture = new CelestialObjectPicture { CelestialObject = m82, Picture = m8182Picture };

context.Add<CelestialObjectPicture>(m42ObjectPicture);
context.Add<CelestialObjectPicture>(m81ObjectPicture);
context.Add<CelestialObjectPicture>(m82ObjectPicture);

context.SaveChanges();

Problem starts here: when I load the context from the database, entities seem not to be linked. For instance, Pictures have no references to the link entity CelestialObjectPictures:

But if I decide to check the CelestialObjectPictures entity, it does contains the values:

And if I then go back and check the Picture entity (no code run, I'm still in the same breakpoint), the CelestialObjectPictures appeared.

The same pattern appears in all linked entities, which mean, before I manually check:

The CelestialObject entities have no link to CelestialObjectType (null reference), nor CelestialObjectPictures and CelestialObjectCatalogs (empty lists)
The CelestialCatalog entities have no link to CelestialCatalogEntities (empty list)
etc...

Here is my DbContext and entities classes, please note that I tried having a DbContext with:

Just a DbSet<Pictures> (the top object I do use in the razor page)
Both a DbSet<Pictures> and a DbSet<CelestialObject>
A DbSet for each entity (the one I currently use)

Code:

public class AstroWebSiteDbContext : DbContext
{
        public DbSet<CelestialObjectPicture> CelestialObjectPictures { get; set; }
        public DbSet<CelestialObjectCatalog> CelestialObjectCatalogs { get; set; }
        public DbSet<CelestialObjectType> CelestialObjectTypes { get; set; }

        public DbSet<CelestialCatalog> CelestialCatalogs { get; set; }
        public DbSet<CelestialObject> CelestialObjects { get; set; } 
        public DbSet<Picture> Pictures { get; set; }

        public AstroWebSiteDbContext(DbContextOptions<AstroWebSiteDbContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CelestialObjectPicture>().
                HasKey(x => new { x.CelestialObjectId, x.PictureId });

            modelBuilder.Entity<CelestialObjectPicture>().
                HasOne(cop => cop.Picture).
                WithMany(pic => pic.CelestialObjectPictures).
                HasForeignKey(cop => cop.PictureId);

            modelBuilder.Entity<CelestialObjectPicture>().
                HasOne(cop => cop.CelestialObject).
                WithMany(co => co.CelestialObjectPictures).
                HasForeignKey(cop => cop.CelestialObjectId);

            modelBuilder.Entity<CelestialObjectCatalog>().
                HasKey(x => new { x.CelestialObjectId, x.CelestialCatalogId });

            modelBuilder.Entity<CelestialObjectCatalog>().
                HasOne(coc => coc.CelestialCatalog).
                WithMany(cc => cc.CelestialObjectCatalogs).
                HasForeignKey(coc => coc.CelestialCatalogId);

            modelBuilder.Entity<CelestialObjectCatalog>().
                HasOne(coc => coc.CelestialObject).
                WithMany(co => co.CelestialObjectCatalogs).
                HasForeignKey(coc => coc.CelestialObjectId);

            base.OnModelCreating(modelBuilder);
        }
}

public class CelestialObjectPicture
{
    [Key]
    public int CelestialObjectId { get; set; }
    [Key]
    public int PictureId { get; set; }

    public CelestialObject CelestialObject { get; set; }
    public Picture Picture { get; set; }
}

public class CelestialObjectCatalog
{
    [Key]
    public int CelestialObjectId { get; set; }
    public CelestialObject CelestialObject { get; set; }

    [Key]
    public int CelestialCatalogId { get; set; }
    public CelestialCatalog CelestialCatalog { get; set; }

    public int Rank { get; set; }
}

public class CelestialObject
{
    [Key]
    public int CelestialObjectId { get; set; }

    public string Name { get; set; }

    public CelestialObjectType CelestialObjectType { get; set; }
    public DateTime DiscoveryDate { get; set; }

    public List<CelestialObjectPicture> CelestialObjectPictures { get; set; } = new List<CelestialObjectPicture>();

    public List<CelestialObjectCatalog> CelestialObjectCatalogs { get; set; } = new List<CelestialObjectCatalog>();
}

public class CelestialCatalog
{
    [Key]
    public int CelestialCatalogId { get; set; }

    public string Name { get; set; }

    public List<CelestialObjectCatalog> CelestialObjectCatalogs { get; set; } = new List<CelestialObjectCatalog>();
}

I'm pretty sure I'm missing something obvious, but sadly, I couldn't find anything related to this problem. Any help would be appreciated

Hello. In EF, you might need to trigger loading of the inner object list during queries by "include" function. You can check this post for example https://stackoverflow.com/questions/3356541/entity-framework-linq-query-include-multiple-children-entities — AntiqTech, Nov 03 '20 at 00:33

Jeremy Lakeman · Accepted Answer · 2020-11-03T00:42:25.387

You've effectively executed the following from within the debugger;

context.Pictures.ToList(); // => select * from Pictures;
context.CelestialObjectPictures.ToList(); // => select * from CelestialObjectPictures;
context.Pictures.ToList(); // => select * from Pictures;

Each enumeration of each DbSet will load that list of objects into the change tracker. Any other known objects already in the change tracker will be linked together. Databases can be huge, you rarely want to load everything.

If you want to load everything at once, you should run your own query to materialise the data objects you are interested in. Including any additional foreign keys that you would also like to load at the same time;

var objects = await context.CelestialObjects
    .Include(o => o.CelestialObjectsPictures)
        .ThenInclude(c => c.Pictures)
    .ToListAsync();

If you don't include it in your query, it won't be loaded into memory.

That was it, thank you! I'll accept this answer after the mandatory 24h. — F.Carette, Nov 03 '20 at 01:00

Entity Framework Core : DbContext not linking entities

1 Answers1