ASP.NET Core MVC : complex data model reading related data

Question

I am trying to display a drop-down on my DEVICES create.cshtml view of the names from CONTACT via the lookup table DeviceAssignments, below is an ERD. I am already able to display the drops downs for DeviceCategories and DeviceLocations, but I am not having to go through an intermediary table.

I am just getting started learning ASP.NET and any help is appreciated.

I have tried the following in my DBContext:

entity.ToTable("DeviceAssignments", "Assets");
entity.Property(e => e.AssignDate).HasColumnType("date");
entity.Property(e => e.Created)
            .HasDefaultValueSql("(getdate())")
            .HasColumnType("datetime");
entity.Property(e => e.Creator)
            .IsRequired()
            .HasMaxLength(150)
            .IsUnicode(false)
            .HasDefaultValueSql("(suser_sname())");
entity.Property(e => e.Modified)
            .HasDefaultValueSql("(getdate())")
            .HasColumnType("datetime");
entity.Property(e => e.Modifier)
            .IsRequired()
            .HasMaxLength(150)
            .IsUnicode(false)
            .HasDefaultValueSql("(suser_sname())");
entity.Property(e => e.ReturnDate).HasColumnType("date");
entity.HasOne(d => d.Contact).WithMany(p => p.DeviceAssignments)
            .HasForeignKey(d => d.ContactID)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_DeviceAssignments_Contact");
entity.HasOne(d => d.Device).WithMany(p => p.DeviceAssignments)
            .HasForeignKey(d => d.DeviceId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_DeviceAssignments_Devices");
entity.ToTable("Devices", "Assets");
entity.Property(e => e.Comment)
            .HasMaxLength(150)
            .IsUnicode(false);
entity.Property(e => e.Cost).HasColumnType("decimal(9, 4)");
entity.Property(e => e.Created)
            .HasDefaultValueSql("(getdate())")
            .HasColumnType("datetime");
entity.Property(e => e.Creator)
            .IsRequired()
            .HasMaxLength(150)
            .IsUnicode(false)
            .HasDefaultValueSql("(suser_sname())");
entity.Property(e => e.Description)
            .HasMaxLength(150)
            .IsUnicode(false);
entity.Property(e => e.InvoiceNumber)
            .HasMaxLength(50)
            .IsUnicode(false);
entity.Property(e => e.MAC)
            .HasMaxLength(50)
            .IsUnicode(false);
entity.Property(e => e.Model)
            .HasMaxLength(150)
            .IsUnicode(false);
entity.Property(e => e.Modified)
            .HasDefaultValueSql("(getdate())")
            .HasColumnType("datetime");
entity.Property(e => e.Modifier)
            .IsRequired()
            .HasMaxLength(150)
            .IsUnicode(false)
            .HasDefaultValueSql("(suser_sname())");
entity.Property(e => e.PurchaseDate).HasColumnType("date");
entity.Property(e => e.SerialNumber)
            .HasMaxLength(50)
            .IsUnicode(false);
entity.Property(e => e.Status)
            .HasMaxLength(50)
            .IsUnicode(false);
entity.HasOne(d => d.DeviceCategory).WithMany(p => p.Devices)
            .HasForeignKey(d => d.DeviceCategoryId)
            .OnDelete(DeleteBehavior.ClientSetNull)
            .HasConstraintName("FK_Devices_DeviceCategories");
entity.HasOne(d => d.DeviceLocation).WithMany(p => p.Devices)
            .HasForeignKey(d => d.DeviceLocationId)
            .HasConstraintName("FK_Devices_DeviceLocations");

DevicesController:

// GET: Devices/Edit/5
public async Task<IActionResult> Edit(int? id)
{
    if (id == null || _context.Devices == null)
    {
        return NotFound();
    }

    var devices = await _context.Devices.FindAsync(id);

    if (devices == null)
    {
        return NotFound();
    }

    ViewData["DeviceCategoryId"] = new SelectList(_context.DeviceCategories, "DeviceCategoryId", "DeviceCategoryName", devices.DeviceCategoryId);
    ViewData["DeviceLocationId"] = new SelectList(_context.DeviceLocations, "DeviceLocationId", "DeviceLocationName", devices.DeviceLocationId);            

    return View(devices);
}

Edit.cshtml

@model QsccDevAlpha.Models.Devices

@{
    ViewData["Title"] = "Edit";
}

<h1>Edit</h1>

<h4>Devices</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Edit">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <input type="hidden" asp-for="DeviceId" />
            <div class="form-group">
                <label asp-for="DeviceCategoryId" class="control-label"></label>
                <select asp-for="DeviceCategoryId" class="form-control" asp-items="ViewBag.DeviceCategoryId"></select>
                <span asp-validation-for="DeviceCategoryId" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="DeviceLocationId" class="control-label"></label>
                <select asp-for="DeviceLocationId" class="form-control" asp-items="ViewBag.DeviceLocationId"></select>
                <span asp-validation-for="DeviceLocationId" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Description" class="control-label"></label>
                <input asp-for="Description" class="form-control" />
                <span asp-validation-for="Description" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="SerialNumber" class="control-label"></label>
                <input asp-for="SerialNumber" class="form-control" />
                <span asp-validation-for="SerialNumber" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="PurchaseDate" class="control-label"></label>
                <input asp-for="PurchaseDate" asp-format="{0:MM/dd/yyyy}" class="form-control" />
                <span asp-validation-for="PurchaseDate" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="InvoiceNumber" class="control-label"></label>
                <input asp-for="InvoiceNumber" class="form-control" />
                <span asp-validation-for="InvoiceNumber" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="MAC" class="control-label"></label>
                <input asp-for="MAC" class="form-control" />
                <span asp-validation-for="MAC" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Model" class="control-label"></label>
                <input asp-for="Model" class="form-control" />
                <span asp-validation-for="Model" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Status" class="control-label"></label>
                <input asp-for="Status" class="form-control" />
                <span asp-validation-for="Status" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Cost" class="control-label"></label>
                <input asp-for="Cost" class="form-control" />
                <span asp-validation-for="Cost" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Comment" class="control-label"></label>
                <input asp-for="Comment" class="form-control" />
                <span asp-validation-for="Comment" class="text-danger"></span>
            </div>            
            <div class="form-group">
                <input type="submit" value="Save" class="btn btn-primary" />
            </div>
        </form>
    </div>
</div>

Answer 1

I still have the same questions, how do I add a drop down box for Contacts.FullName on the Create and Edit views for the DevicesContoller? I am able to add the drop downs and save the related data for DeviceCategories and DeviceLocations but not Contacts.

Follow the many-to-many pattern in my previous links. Following the pattern make the LINQ and loading related data a lot easier.

More importantly, you have a general design issue because you are using Entity Framework entities, like Devices, as view models (the models used to populate views and post data). This type of approach only works for the most simplest of MVC applications. The model used in Views should be separate from the EB entities. Again, the tutorials in this site cover this concept.

Unfortunately, I was unable to commit my updates to your repo. Probably related to GitHub security.

    public class Devices
    {
        [Key]
        public int DeviceId { get; set; }

        public int DeviceCategoryId { get; set; }

        public int? DeviceLocationId { get; set; }

        public string Description { get; set; }

        public string SerialNumber { get; set; }
        
        public virtual DeviceCategories DeviceCategory { get; set; }

        public virtual DeviceLocations DeviceLocation { get; set; }

        public int? ContactsId { get; set; }
        public ICollection<Contacts> Contacts { get; set; }
        public List<DeviceAssignments> DeviceAssignments { get; set; }

    }

    public class Contacts
    {
        [Key]
        public int ContactId { get; set; }       

        public string FullName { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public ICollection<Devices> Devices { get; set; }
        public List<DeviceAssignments> DeviceAssignments { get; set; }
    }

    public class DeviceAssignments
    {
        [Key]
        public int DeviceAssignmentId { get; set; }

        public int DeviceId { get; set; }
        public Devices Devices { get; set; }

        public int ContactId { get; set; }
        public Contacts Contacts { get; set; }

    }

    public class AssetsContext : DbContext
    {
        public AssetsContext(DbContextOptions<AssetsContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
            .Entity<Devices>()
            .HasMany(p => p.Contacts)
            .WithMany(p => p.Devices)
            .UsingEntity<DeviceAssignments>(
                j => j
                    .HasOne(da => da.Contacts)
                    .WithMany(c => c.DeviceAssignments)
                    .HasForeignKey(pt => pt.ContactId),
                j => j
                    .HasOne(da => da.Devices)
                    .WithMany(d => d.DeviceAssignments)
                    .HasForeignKey(da => da.DeviceId),
                j =>
                {
                    j.HasKey(t => new { t.DeviceId, t.ContactId });
                });
        }

        public virtual DbSet<Contacts> Contacts { get; set; }

        public virtual DbSet<DeviceAssignments> DeviceAssignments { get; set; }

        public virtual DbSet<DeviceCategories> DeviceCategories { get; set; }

        public virtual DbSet<DeviceLocations> DeviceLocations { get; set; }

        public virtual DbSet<Devices> Devices { get; set; }

    }

        // GET: Devices/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null || _context.Devices == null)
            {
                return NotFound();
            }

            var devices = await _context.Devices.Include(c => c.Contacts).FirstOrDefaultAsync(d => d.DeviceId == id);
            ViewData["ContactsOptions"] = new SelectList(devices.Contacts, "ContactId", "FullName");

            //var devices = await _context.Devices.FindAsync(id);
            if (devices == null)
            {
                return NotFound();
            }
            ViewData["DeviceCategoryId"] = new SelectList(_context.DeviceCategories, "DeviceCategoryId", "DeviceCategoryName", devices.DeviceCategoryId);
            ViewData["DeviceLocationId"] = new SelectList(_context.DeviceLocations, "DeviceLocationId", "DeviceLocationName", devices.DeviceLocationId);
            return View(devices);
        }