Can't save multiple selections in select list - asp.net core web app razor pages

Question

Here I am able to bind the multi-select select list on the create page.

<select id="multiple" asp-for="SecurityLog.Officer" multiple="multiple" class="selectpicker form-control" asp-items="ViewBag.Officer">
</select>

public IActionResult OnGetAsync()
{                    
     ViewData["Officer"] = new SelectList(_context.Officer.Where(a => a.Active == "Y"), "ID", "FullName");

     return Page();
}

The biggest problem I'm having is that I am trying to edit/post values and there's no direct field to bind to in my model.

I have found a good example here, but it only shows you how to save 1 selection. https://www.learnrazorpages.com/razor-pages/forms/select-lists

My SecurityLog Model has this

public virtual Officer Officer { get; set; }

My Officer Model has this

namespace SecurityCore.Models
{
    public class Officer
    {
        [Required]
        public int ID { get; set; }

        [Required]
        [Display(Name = "Officer's First Name")]
        public string FirstName { get; set; }

        [Required]
        [Display(Name = "Officer's Last Name")]
        public string LastName { get; set; }

        [Display(Name = "Officer's Name")]
        public string FullName { get; set; }

        [Required]
        public string Active { get; set; }        
    }
}

I do not have a SecurityLogOfficer Model. Do I need one?

And here is my database schema and sample results

Any assistance would be appreciated!

* UPDATE *

Thank you for your suggestion to bind to a list and change the asp-for to that list. Here is my updated code. SelectedOfficerIds does store the selected id's.

<select id="multiple" asp-for="SelectedOfficerIds" multiple="multiple" class="selectpicker form-control" asp-items="ViewBag.Officer">    
</select>

I am posting to two different databases (1 record should go to SecurityLog, and multiple to SecurityLogOfficer), but since I am calling savechanes() twice it is actually posting multiple records to the SecurityLog db and on the second for loop save I receive an error

Cannot insert explicit value for identity column in table 'SecurityLogOfficer' when IDENTITY_INSERT is set to OFF

_context.SecurityLog.Add(SecurityLog);
//We need to ensure we have the ID of the newly posted event
_context.SaveChanges();

int tempSecurityLogID = SecurityLog.ID;

for (int i = 0; i < SelectedOfficerIds.Length; i++)
{                
     SecurityLogOfficer.SecurityLogID = tempSecurityLogID;
     SecurityLog = null;

     SecurityLogOfficer.OfficerID = SelectedOfficerIds[i];
     _context.SecurityLogOfficer.Add(SecurityLogOfficer);                
     await _context.SaveChangesAsync();
}            

Message = "Entry added successfully!";

Is there a way to clear out _context for SecurityLog so I only post once and how can I address the identity_insert issue? The db, SecurityLogOfficer, already has identity insert on.

Update 1/9/2020

@XingZou - Thank you for the quick reply. I attempted to implement your steps and everything worked out perfectly!!!

I do have one additional question if you don't mind? I am attempting to get a list of the selected values on the Edit Page and cannot set the values. This is what I tried so far...

Here is my Edit Page Model...

[BindProperty] 
public MultiSelectList GetExistingSelectedOfficerIds { get; set; }

public async Task<IActionResult> OnGetAsync(int? id)
{
    var i = _context.SecurityLogOfficer.Where(a => a.SecurityLogID == SecurityLog.ID);

        SecurityLogOfficers.AddRange(i);

        int[] GetExistingSelectedOfficerIds = new int[SecurityLogOfficers.Count];

        int itemCount = 0;
        foreach (var item in SecurityLogOfficers)
        {                
            GetExistingSelectedOfficerIds[itemCount] = item.OfficerID;
            itemCount++;
        }

        return Page();
}

Here is my Edit razor page...

<select id="multiple" asp-for= "GetExistingSelectedOfficerIds" multiple="multiple" class="selectpicker form-control" asp-items="ViewBag.Officer">
</select>
<span asp-validation-for="GetExistingSelectedOfficerIds" class="text-danger"></span>

Answer 1

You need to bind the dropdown list to a list of int instead of object.

In PageModel, add a new property which stores all selected Ids:

[BindProperty]
public int[] SelectedOfficerIds { get; set; }

On Razor Pages，bind the multiple select to SelectedOfficerIds

<form method="post">
    <div asp-validation-summary="ModelOnly" class="text-danger"></div>
    <div class="form-group">
        <label asp-for="SecurityLog.EventDate" class="control-label"></label>
        <input asp-for="SecurityLog.EventDate" class="form-control" />
        <span asp-validation-for="SecurityLog.EventDate" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label class="control-label">Officers</label>
        <select id="multiple" asp-for="SelectedOfficerIds" multiple="multiple" class="selectpicker form-control" asp-items="ViewBag.Officer">
        </select>
    </div>

    <div class="form-group">
        <input type="submit" value="Create" class="btn btn-primary" />
    </div>
</form>

When you submit form, you will get new Officer ID list of the SecurityLog in you post handler.

Update 1/9/2020

There is no direct relationship between SecurityLog and Officer. There is a one-to-many relationship between SecurityLog and SecurityLogOfficer and a many-to-one relationship between SecurityLogOfficer and Officer.

From you description, it is many-to-many relationships between SecurityLog and Officer. 1.Remove the ID of SecurityLogOfficer,it uses composite key now

public class SecurityLogOfficer
{
    public int SecurityLogID { get; set; }
    public SecurityLog SecurityLog { get; set; }

    public int OfficerID { get; set; }
    public Officer Officer { get; set; }
}
public class Officer
{
    public int ID { get; set; }

    //other properties

    public List<SecurityLogOfficer> SecurityLogOfficers { get; set; }
}

public class SecurityLog
{
    public int ID { get; set; }

    //other properties

    public virtual List<SecurityLogOfficer> SecurityLogOfficers { get; set; }
}

2.Configure many-to-many relationship like below.

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


    public DbSet<Officer> Officer { get; set; }
    public DbSet<SecurityLog> SecurityLog { get; set; }
    public DbSet<SecurityLogOfficer> SecurityLogOfficer { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SecurityLogOfficer>()
            .HasKey(t => new { t.SecurityLogID, t.OfficerID });

        modelBuilder.Entity<SecurityLogOfficer>()
            .HasOne(pt => pt.SecurityLog)
            .WithMany(p => p.SecurityLogOfficers)
            .HasForeignKey(pt => pt.SecurityLogID);

        modelBuilder.Entity<SecurityLogOfficer>()
            .HasOne(pt => pt.Officer)
            .WithMany(t => t.SecurityLogOfficers)
            .HasForeignKey(pt => pt.OfficerID);
    }
}

3.Add migrations and Update database.

Is there a way to clear out _context for SecurityLog so I only post once

4.In your PageModel, save data like below

[BindProperty]
public SecurityLog SecurityLog { get; set; }

public List<SecurityLogOfficer> SecurityLogOfficers { get; set; } = new List<SecurityLogOfficer>();

[BindProperty]
public int[] SelectedOfficerIds { get; set; }

public async Task<IActionResult> OnPostAsync()
    {
        _context.SecurityLog.Add(SecurityLog);
        foreach (var id in SelectedOfficerIds)
        {
            var item = new SecurityLogOfficer()
            {
                SecurityLog = SecurityLog,
                Officer = await _context.Officer.FirstOrDefaultAsync(s => s.ID == id),
            };
            SecurityLogOfficers.Add(item);
        }
        _context.SecurityLogOfficer.AddRange(SecurityLogOfficers);
        await _context.SaveChangesAsync();

        return RedirectToPage("./Index");
    }

Update 1/10/2020

To get multiple selected values in Edit Page, use below code before return Page() in get handler

ViewData["Officer"] = new MultiSelectList(_context.Officers.Where(a => a.Active == "Y"), "ID", "FullName", GetExistingSelectedOfficerIds);

Answer 2

You did not share your complete Create pagemodel. In it, do you have something such as the following:

public MultiSelectList Officer { get; set; }

I believe you will be able to populate that with the Officer's information from the Officer model.