Problem loading data with Entity Framework using Include

Question

I’m using the Entity Framework with the Mysql on my system and I need to make a specific query that includes a list of a type of Model (ApplicationUser), in this list each returned object has in its properties another Model. That is, Each Applicationuser has a list of the type 'Vehicle' (one-to-Many) and every vehicle has its own Applicationuser (Driver), but when will I use direct on view, I notice that the driver of each vehicle is always null, even if I give the include in this way:

context.Veiculos.Include(veiculo => veiculo.Motorista).ToList();

Veiculo Model:

 [DisplayColumn("Nome")]
    [Table("Veiculos")]
    public class Veiculo
    {
        [Key]
        public Guid VeiculoID { get; set; }

        [Required]
        public Guid MotoristaID { get; set; }
        //[Required]
        //public Guid EnderecoID { get; set; }

        [Required]
        public Guid EmpresaID { get; set; }

        //[Display(Name = "Address", ResourceType = typeof(Resources.Language))]
        //public virtual Endereco Endereco { get; set; }

        [Display(Name = "Company", ResourceType = typeof(Resources.Language))]
        public virtual ApplicationUser Empresa { get; set; }

        [Display(Name = "Driver", ResourceType = typeof(Resources.Language))]
        public virtual ApplicationUser Motorista { get; set; }

        [Required]
        [Display(Name = "Mark", ResourceType = typeof(Resources.Language))]
        public string Marca { get; set; }

        [Required]
        [Display(Name = "Model", ResourceType = typeof(Resources.Language))]
        public string Modelo { get; set; }

        [Required]
        [Display(Name = "Year", ResourceType = typeof(Resources.Language))]
        public int Ano { get; set; }

        [Required]
        [Display(Name = "LicensePlate", ResourceType = typeof(Resources.Language))]
        public string Placa { get; set; }

        [Required]
        [Display(Name = "Color", ResourceType = typeof(Resources.Language))]
        public string Cor { get; set; }

        [Required]
        [Display(Name = "RENAVANCode", ResourceType = typeof(Resources.Language))]
        public long Renavan { get; set; }

        [Required]
        [Display(Name = "NumberOfPassangers", ResourceType = typeof(Resources.Language))]
        public int NumeroPassageiros { get; set; }

        [Display(Name = "ExtraBaggage", ResourceType = typeof(Resources.Language))]
        public double PesoExtra { get; set; }

        [Required]
        [Display(Name = "Trailer", ResourceType = typeof(Resources.Language))]
        public bool Carretinha { get; set; }

        [Display(Name = "Excluded", ResourceType = typeof(Resources.Language))]
        public bool Excluido { get; set; }
    }

Applicationuser model:

public class ApplicationUser : IdentityUser
    {
        public ApplicationUser()
        {
            Clients = new Collection<Client>();
        }

        [Index, MaxLength(60)]
        public override string UserName { get; set; }

        public Guid? EmpresaPaiID { get; set; }
        public Guid? EnderecoID { get; set; }
        public Guid? ServicoID { get; set; }
        public bool Ativo { get; set; }
        public bool CadastroCompleto { get; set; }

        [Display(Name = "Address", ResourceType = typeof(Resources.Language))]
        public virtual Endereco Endereco { get; set; }
        [Display(Name = "Vehicles", ResourceType = typeof(Resources.Language))]
        public virtual ICollection<Veiculo> Veiculos { get; set; }
        [Display(Name = "ServicesPerformed", ResourceType = typeof(Resources.Language))]
        public virtual ICollection<Servico> ServicosVans { get; set; }

        [Display(Name = "Available", ResourceType = typeof(Resources.Language))]
        public bool Disponivel { get; set; }        

        [Required]
        [Display(Name = "Name", ResourceType = typeof(Resources.Language))]
        public string Nome { get; set; }

        [Display(Name = "CompanyName", ResourceType = typeof(Resources.Language))]
        public string NomeDaEmpresa { get; set; }

        [Required]
        [Display(Name = "UserType", ResourceType = typeof(Resources.Language))]
        public TipoUsuario TipoUsuario { get; set; }

        [Required]
        [Display(Name = "CelPhone", ResourceType = typeof(Resources.Language))]
        public override string PhoneNumber { get; set; }

        [Display(Name = "Phone", ResourceType = typeof(Resources.Language))]
        public string Telefone { get; set; }

        [Required]
        [Display(Name = "RG", ResourceType = typeof(Resources.Language))]
        public string RG { get; set; }

        [Cpf]
        [Required]
        [Display(Name = "CPF", ResourceType = typeof(Resources.Language))]
        public string CPF { get; set; }

        [Cnpj]
        [Display(Name = "CNPJ", ResourceType = typeof(Resources.Language))]
        public string CNPJEmpresa { get; set; } //Caso for empresa o tipo de usuário

        //[Cnpj]
        //[Display(Name = "RentalCNPJ", ResourceType = typeof(Resources.Language))]
        //public string CNPJLocadora { get; set; }

        [Required]
        [Display(Name = "Gender", ResourceType = typeof(Resources.Language))]
        public string Sexo { get; set; }        

        public double Latitude { get; set; }

        public double Longitude { get; set; }

        [Display(Name = "PassangerSecurity", ResourceType = typeof(Resources.Language))]
        public bool SeguroPassageiro { get; set; }

        [Display(Name = "RemuneratedActivity", ResourceType = typeof(Resources.Language))]
        public bool AtividadeRemunerada { get; set; }

        [Display(Name = "DriverLicense", ResourceType = typeof(Resources.Language))]
        public long CNH { get; set; }

        [NotMapped]
        [Display(Name = "Photo", ResourceType = typeof(Resources.Language))]
        public HttpPostedFileBase Foto { get; set; }

        [NotMapped]
        [Display(Name = "DocumentOfTheCarRentalAgency", ResourceType = typeof(Resources.Language))]
        public HttpPostedFileBase DocumentoLocadoraVeiculos { get; set; }

        [NotMapped]
        [Display(Name = "CertificateOfCriminalRecord", ResourceType = typeof(Resources.Language))]
        public HttpPostedFileBase AtestadoAntecedentesCriminais { get; set; }

        [NotMapped]
        [Display(Name = "TicketDPVAT", ResourceType = typeof(Resources.Language))]
        public HttpPostedFileBase BilheteDPVAT { get; set; }

        [NotMapped]
        [Display(Name = "VehicleLicensingRegistration", ResourceType = typeof(Resources.Language))]
        public HttpPostedFileBase RegistroLicenciamentoVeiculo { get; set; }

        public string FotoPath { get; set; }
        public string DocumentoLocadoraVeiculosPath { get; set; }
        public string AtestadoAntecedentesCriminaisPath { get; set; }
        public string BilheteDPVATPath { get; set; }
        public string RegistroLicenciamentoVeiculoPath { get; set; }

        public virtual ICollection<Client> Clients { get; set; }

In the view, I notice that the Motorista is not loaded by this query. This generates a null reference error, of course. It would have something to do with Lazy Loading?

How to solve?

If you need to paste here the diagram of the database.

Answer 1

As we can see, the Vehicle class has two FK for Applicationuser. They are Company and Driver.

public class Veiculo
{
    [Key]
    public Guid VeiculoID { get; set; }

    [Required]
    public Guid MotoristaID { get; set; }

    [Required]
    public Guid EmpresaID { get; set; }

    public virtual ApplicationUser Empresa { get; set; }

    public virtual ApplicationUser Motorista { get; set; }
}

EF cannot have a single collection for two FK. As in the class below:

public class ApplicationUser : IdentityUser
{
    public virtual ICollection<Veiculo> Veiculos { get; set; }
}

It is necessary to have two collections (Vehiclescompany and Vehiclesdriver), so that each represents the reverse part of the relationship Vehicle -> Applicationuser. If a collection is needed that brings all vehicles, it can be done through another property (Vehicles).

public class ApplicationUser : IdentityUser
{
    public virtual ICollection<Veiculo> VeiculosEmpresa { get; set; }
    public virtual ICollection<Veiculo> VeiculosMotorista { get; set; }

    public virtual IEnumerable<Veiculo> Veiculos
    {
        get { return VeiculosEmpresa.Concat(VeiculosMotorista); }
    }
}

For this to work, it is necessary to include an Fluent mapping in the Onmodelcreating method. In this way, we are informing the Model Builder which property is reflected by which collection, and its respective FK.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
     modelBuilder.Entity<Veiculo>().HasRequired(m => m.Empresa).WithMany(m => m.VeiculosEmpresa).HasForeignKey(p => p.EmpresaID);
     modelBuilder.Entity<Veiculo>().HasRequired(m => m.Motorista).WithMany(m => m.VeiculosMotorista).HasForeignKey(p => p.MotoristaID);
}