ER diagram

I have four tables with the following relationship. I able create the relationship between three of four. However, the table linea I can't.

-Entities-

    public class Producto
    {
        [Key]
        [Required]
        public int idProducto { get; set; }
        public bool Estatus { get; set; }
        public string Variante { get; set; }
        [Required]
        public int idSecuencia { get; set; }
        [Required]
        public int idFascia { get; set; }
        [ForeignKey("idFascia")]
        public virtual Fascia Fascia { get; set; }
        [ForeignKey("idSecuencia")]
        public virtual Secuencia Secuencia { get; set; }*/
}


    public class Fascia
    {
        [Required]
        public int idFascia { get; set; }
        [Required]
        public string ModeloM100Pos0 { get; set; }
        [Required]
        public string VersionM100Pos0 { get; set; }
        [Required]
        public DateTime FechaYHora { get; set; }
        [Required]
        public string NombreVersion { get; set; }
    }


    public class Secuencia
    {
        [Key]
        [Required]
        public int idSecuencia { get; set; }
        [Required]
        public string Flujo { get; set; }
        [Required]
        public int idLinea { get; set; }

        [ForeignKey("idLinea")]
        public virtual Linea Linea { get; set; }
    }

    public class Linea
    {
        [Key]
        [Required]
        public int idLinea { get; set; }
        [Required]

        public string Nombre { get; set; }

        public int NoPosiciones { get; set; }
    }

--DbContext--

public class DbContextSCADA_A: DbContext
    {
        public DbSet<Linea> Lineas { get; set; }
        public DbSet<Secuencia> Secuencias { get; set; }
        public DbSet<Fascia> Fascias { get; set; }
        public DbSet<Producto> Productos { get; set; }

        public DbContextSCADA_A(DbContextOptions<DbContextSCADA_A> options) : base(options)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new LineaMap());
            modelBuilder.ApplyConfiguration(new SecuenciaMap());
            modelBuilder.ApplyConfiguration(new FasciaMap());
            modelBuilder.ApplyConfiguration(new ProductoMap());
        }
    }

--ProductoViewModel--

    public class ProductoViewModel
    {
        public int idProducto { get; set; }
        public int idSecuencia { get; set; }
        public int idFascia { get; set; }
        public string Nombre { get; set; }
        public string Posicion0 { get; set; }
        public string Version { get; set; }
        public string Variante { get; set; }
        public string Linea { get; set; }
        public string Secuencia { get; set; }
        public int idLinea { get; set; }
        public bool Estatus { get; set; }
    }

--ProductosController: List --

public async Task<IEnumerable<ProductoViewModel>> Listar()
        {
            var producto = await _context.Productos
                .Include(i => i.Secuencia)
                .Include(i => i.Fascia)
                .ToListAsync();

            return producto.Select(i => new ProductoViewModel
            {
                idProducto = i.idProducto,
                idFascia = i.idFascia,
                idSecuencia = i.idSecuencia,
                Nombre = i.Fascia.NombreVersion,
                Posicion0 = i.Fascia.ModeloM100Pos0,
                Version = i.Fascia.VersionM100Pos0,
                idLinea = i.Secuencia.idLinea,
                Linea = i.Secuencia.Linea.Nombre,
                Secuencia = i.Secuencia.Flujo,
                Variante = i.Variante,
                Estatus = i.Estatus,

            });
        }

--Mapping: Configure--

        public void Configure(EntityTypeBuilder<Producto> builder)
        {
            builder.ToTable("producto")
                .HasKey(a => a.idProducto);
        }

        public void Configure(EntityTypeBuilder<Fascia> builder)
        {
            builder.ToTable("fascia")
                .HasKey(a => a.idFascia);
        }

        public void Configure(EntityTypeBuilder<Linea> builder)
        {
            builder.ToTable("linea")
                .HasKey(a => a.idLinea);
        }

        public void Configure(EntityTypeBuilder<Secuencia> builder)
        {
            builder.ToTable("secuencia")
                .HasKey(a => a.idSecuencia);
        }

When I try the execute, however I get following exception error Exception Error and don't return data.

Thanks in advance.

UPDATED:: I update the image Exception image error :P && the configurations code.

CodePudding user response：

The issue is most likely that you are eager loading some entities, but haven't eager-loaded the "Linia" underneath the Secunia.

You can fix this with the following change:

// EF Core
var producto = await _context.Productos
    .Include(i => i.Secuencia)
        .ThenInclude(s => s.Linia)
    .Include(i => i.Fascia)
    .ToListAsync();

// EF 6
var producto = await _context.Productos
    .Include(i => i.Secuencia)
    .Include(i => i.Secuencia.Linia)
    .Include(i => i.Fascia)
    .ToListAsync();

However, a better solution is to bring the projection (Select) right into the query like so:

var producto = await _context.Productos
    .Select(i => new ProductoViewModel
    {
        idProducto = i.idProducto,
        idFascia = i.idFascia,
        idSecuencia = i.idSecuencia,
        Nombre = i.Fascia.NombreVersion,
        Posicion0 = i.Fascia.ModeloM100Pos0,
        Version = i.Fascia.VersionM100Pos0,
        idLinea = i.Secuencia.idLinea,
        Linea = i.Secuencia.Linea.Nombre,
        Secuencia = i.Secuencia.Flujo,
        Variante = i.Variante,
        Estatus = i.Estatus
    }).ToListAsync();

Notice how we don't have to mess with eager loading Include statements when projecting? This has the added benefit that it will generate a query that only returns the columns from the various related entities that we need to build the view model. This means less data over the wire, less memory usage on the server, and it can potentially be optimized better via indexes.