Collection navigation property, some items are not loaded in the model

[GustavoArriola]

I am applying convention relationships at Entity Framework Core

. When I try to load a navigation property of type "collection", some items are loaded, others are not.
I load it in the following way:

                result = ctx.Instrumentos
                    .Include(m => m.Materia)
                    .Include(c => c.Curso)
                .Where(i => i.CreateUserId == userId && i.AnhoLectivo == anhoLectivo && i.Etapa == etapa).ToList();  

EFC generates the SQL query (with inner join) perfectly, and the SQL query returns the expected values (if I run the query directly in SQL Management Studio), however EFC does not load all the navigation properties correctly ( see attached files)

Steps to reproduce

public class Instrumento : EntityBase
    {  
        public Int64 InstrumentoId { get; set; }
        public string Seccion { get; set; }
        public string Nombre { get; set; }       
        public int AnhoLectivo { get; set; }       
        public int Etapa { get; set; }
        public Curso Curso { get; set; }
        public Materia Materia { get; set; }
    }

 public class Materia
    {
        public long MateriaId { get; set; }
        public string Nombre { get; set; }     
        [JsonIgnore]
        public List<Instrumento> Instrumentos { get; set; }
    }
public class Curso
    {
        public Int64 CursoId { get; set; }
        public String CodigoUsuario { get; set; }
        public Int64 NivelId { get; set; }
        public String Nombre { get; set; }       
        [JsonIgnore]
        public List<Instrumento> Instrumentos { get; set; }        
    }

Nothing in OnModelCreating () and nothing special (except logger and connection string) in OnConfiguring ().

I did not create any migration since the database already exists in production. I am just creating the models and adapting it to the current database simulating Code First.

I have tried configuring Microsoft.EntityFrameworkCore.Proxies without success.

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 PRO x64
IDE: Visual Studio 2019 16.5.4

[roji]

@GustavoArriola any chance you can post a small, runnable code sample that demonstrates the issue? It's bit hard to understand what's going on from a series of screenshots...

[GustavoArriola]

@roji you can go here: https://1drv.ms/u/s!Anfap1RYoo3OjvQ7mu-JDuSRlfnScA?e=FQwKCe

Best regards,
Gustavo

[roji]

@GustavoArriola that URL seems to point back here. Ideally, we'd have a minimal, runnable program - not a full project with unrelated code etc.

[GustavoArriola]

What I attached was just the Visual Studio template for asp.net Core, not the actual project UI. The complete project has 33 MB compressed.

[GustavoArriola]

@roji It seems that relationships are affected when the related entity is the same, that is, the same object.

[roji]

@GustavoArriola please try to isolate the minimal code that creates the problem, and ideally submit that as a simple console program - a 33MB compressed project would require us to go through a lot to make it work, understand what's happening etc.

[GustavoArriola]

@roji Here is the project! I included in it, the database "neoSchool_DB_MSSQL2017.zip".

COREConsoleApp.zip

[roji]

@GustavoArriola I've restored your database backup and executed your program, and everything seems to work - I can't see a problem. I get back a list of 28 instances of Instrumento, including their related entities (Curso, Materia). Can you please clarify what exactly the problem is?

[GustavoArriola]

@roji Yes, at index 0 the information is loaded correctly. The same happens with index 1, however, in index 2 it does not load the Materia property. Index 8 does not load either Curso or Materia. Can you reproduce this behavior?

[roji]

No, when I run your application with your database, all the Instrumentos instances have a non-null Materia reference:

var missingMateria = result.Where(i => i.Materia is null).ToList();

I suggest to make sure you're connecting to the right database with the right data - there may be a confusion there. If you're looking at specific indexes, it may be a good idea to add an OrderBy to make sure the order of results is determinstic as well:

result = ctx.Instrumentos
    .Include(m => m.Materia)
    .Include(c => c.Curso)
    .Where(i => i.CreateUserId == 1 && i.AnhoLectivo == 2019 && i.Etapa == 1)
    .OrderBy(i => i.InstrumentoId)
    .ToList();

[GustavoArriola]

@roji No, the object is not null, however the properties within it have the default .NET values, i.e. 0 (zero) for integers or null for string.

var missingMateria = result.Where(i => i.Materia.MateriaNombre is null).ToList();

The database is exactly the same as you have at the moment, I'm sure.

[roji]

Can you please confirm whether removing the constructor for Instrumento fixes the problem for you? In other words, leave the navigation properties as null by default, rather than instantiating them to a default Materia etc.

[ralmsdeveloper]

@roji I can reproduce!

[ralmsdeveloper]

You will be able to reproduce when using Tracking and NoTracking.

AsNoTracking works well!

[GustavoArriola]

It works!! Why Tracking conflicts with the constructor?

[roji]

As a general rule, you should not be initializing reference navigations to default instances - this interferes with the Include/change tracking logic. I'll see with the rest of the team about improving guidance here.

[GustavoArriola]

Thank you! Sorry for this incident.

[roji]

No problem at all, keeping open for now to discuss possibly improving docs.

[ajcvickers]

See #18007

[roji]

Duplicate of #18007