c# - How do I display a concatenated list from a model in another model's page model?

Question

In my main index page, I would like to display a list of Officers in the Officer's Name column. i.e. Tommy Bahamas, Clinton K, etc...

I am having difficulty because the Officer and SecurityLogOfficer table are not a part of my SecurityLog model on the index page.

SecurityLog Index Page

@foreach (var item in Model.SecurityLog)
    {
    <tr>
        <td style="width:4% !important">
            @Html.DisplayFor(modelItem => item.ID)
        </td>
        <td style="width:5% !important">
            @Html.DisplayFor(modelItem => item.EventDate)
        </td>


        <td style="width:5% !important">
            @Html.DisplayFor(modelItem => item.OfficerList)
        </td>
}

I created a class, OfficerList, to return a list of comma separated Officers but I am having trouble displaying this on the SecurityLog index page.

public class OfficerList : SecurityLog
{
    private readonly SecurityCore.Models.SecurityCoreContext _context;

    public OfficerList(SecurityCore.Models.SecurityCoreContext context)
    {
        _context = context;
    }

    public List<string> GetOfficerList()
    {
        List<string> OfficerIDs = new List<string>();

        //use the syntax .ToList() to convert object read from db to list to avoid being re-read again
        var SecLog = _context.SecurityLog.ToList();
        var SecLogOfficer = _context.SecurityLogOfficer.ToList();
        var Officer = _context.Officer.ToList();


        int rowID;

        //string[] OfficerIDs = new string[100];
        rowID = 0;

        foreach (SecurityLog sl in SecLog)
        {
            foreach (SecurityLogOfficer slo in SecLogOfficer.Where(slo => slo.SecurityLogID == sl.ID))
            {
                if (OfficerIDs[rowID] == null)
                {
                    OfficerIDs[rowID] = slo.Officer.FullName + ", ";
                }
                else
                {
                    OfficerIDs[rowID] = OfficerIDs[rowID] + slo.Officer.FullName + ", ";
                }
            }

            rowID++;
        }

        return OfficerIDs;

    }

} 

This is where I would like to display the concatenated list...

And here is my database schema and sample results

Any assistance would be appreciated. Thanks!

UPDATE

I am having an issue retrieving a list of concatenated names in my SecurityLog Model.

public class SecurityLog
{
    private readonly SecurityCore.Models.SecurityCoreContext _context;

    public SecurityLog(SecurityCore.Models.SecurityCoreContext context)
    {
        _context = context;
    }

   ......

    public List<string> OfficerList
    {            
        get
        {                                
            var officerList = new OfficerList(_context);                

            return officerList.GetOfficerList();
        }
    }


 }

Modified OfficerList class

public class OfficerList : SecurityLog
{
private readonly SecurityCore.Models.SecurityCoreContext _context;

public OfficerList(SecurityCoreContext context) : base(context)
{
    _context = context;
}

public List<string> GetOfficerList()
{
    List<string> OfficerIDs = new List<string>();

    //use the syntax .ToList() to convert object read from db to list to avoid being re-read again
    var SecLog = _context.SecurityLog.ToList();
    var SecLogOfficer = _context.SecurityLogOfficer.ToList();
    var Officer = _context.Officer.ToList();


    int rowID;

    //string[] OfficerIDs = new string[100];
    rowID = 0;

    foreach (SecurityLog sl in SecLog)
    {
        foreach (SecurityLogOfficer slo in SecLogOfficer.Where(slo => slo.SecurityLogID == sl.ID))
        {
            if (OfficerIDs[rowID] == null)
            {
                OfficerIDs[rowID] = slo.Officer.FullName + ", ";
            }
            else
            {
                OfficerIDs[rowID] = OfficerIDs[rowID] + slo.Officer.FullName + ", ";
            }
        }

        rowID++;
    }

    return OfficerIDs;

}

}

My _context for SecurityLog is saying that it is null.

SqlNullValueException: Data is Null. This method or property cannot be called on Null values. Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull() Microsoft.Data.SqlClient.SqlBuffer.get_DateTime() Microsoft.Data.SqlClient.SqlDataReader.GetDateTime(int i) lambda_method(Closure , QueryContext , DbDataReader , ResultContext , int[] , ResultCoordinator ) Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.MoveNext() System.Collections.Generic.List..ctor(IEnumerable collection) System.Linq.Enumerable.ToList(IEnumerable source) SecurityCore.Models.OfficerList.GetOfficerList() in OfficerList.cs + var SecLog = _context.SecurityLog.ToList(); SecurityCore.Models.SecurityLog.get_OfficerList() in SecurityLog.cs + return officerList.GetOfficerList(); Microsoft.AspNetCore.Mvc.ViewFeatures.ExpressionMetadataProvider+<>c__DisplayClass0_0.g__modelAccessor|0(object container) Microsoft.AspNetCore.Mvc.ViewFeatures.ModelExplorer.get_Model() Microsoft.AspNetCore.Mvc.ViewFeatures.TemplateBuilder..ctor(IViewEngine viewEngine, IViewBufferScope bufferScope, ViewContext viewContext, ViewDataDictionary viewData, ModelExplorer modelExplorer, string htmlFieldName, string templateName, bool readOnly, object additionalViewData) Microsoft.AspNetCore.Mvc.ViewFeatures.HtmlHelper.GenerateDisplay(ModelExplorer modelExplorer, string htmlFieldName, string templateName, object additionalViewData) Microsoft.AspNetCore.Mvc.ViewFeatures.HtmlHelper.DisplayFor(Expression> expression, string templateName, string htmlFieldName, object additionalViewData) Microsoft.AspNetCore.Mvc.Rendering.HtmlHelperDisplayExtensions.DisplayFor(IHtmlHelper htmlHelper, Expression> expression) SecurityCore.Pages.SecurityLogs.Pages_SecurityLogs_Index.ExecuteAsync() in Index.cshtml + @Html.DisplayFor(modelItem => item.OfficerList) Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context) Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, bool invokeViewStarts) Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context) Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable statusCode) Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable statusCode) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|29_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext(ref State next, ref Scope scope, ref object state, ref bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters() Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted) Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

UPDATE - ADDING RELATIONSHIPS

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

    public DbSet<SecurityCore.Models.SecurityLog> SecurityLog { get; set; }

    public DbSet<SecurityCore.Models.Entity> Entity { get; set; }

    public DbSet<SecurityCore.Models.Location> Location { get; set; }

    public DbSet<SecurityCore.Models.ShiftRange> ShiftRange { get; set; }

    public DbSet<SecurityCore.Models.EventType> EventType { get; set; }

    public DbSet<SecurityCore.Models.SecurityLogOfficer> SecurityLogOfficer { get; set; }

    public DbSet<SecurityCore.Models.Officer> Officer { 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);
    }

}

UPDATE 1/23/2020

I attempted to update the code based on the below answer and I think this is really close to being solved, however; inside of the OfficerList class there is not currently a value for SecLog b/c there is no data on page load until the search button is clicked.

 OfficerList officerList = new OfficerList();
 OfficerLists = officerList.GetOfficerList(_context);

 SecurityLog = await PaginatedList<SecurityLog>.CreateAsync(sort
    .Include(a => a.Entity)
    .Include(b => b.EventType)
    .Include(c => c.Location)
    .Include(d => d.ShiftRange)
    .Include(e => e.Officer)
    .AsNoTracking(), pageIndex ?? 1, pageSize);

This is null when going to OfficerList class

var SecLog = _context.SecurityLog.ToList();

SqlNullValueException: Data is Null. This method or property cannot be called on Null values. Microsoft.Data.SqlClient.SqlBuffer.ThrowIfNull() Microsoft.Data.SqlClient.SqlBuffer.get_DateTime() Microsoft.Data.SqlClient.SqlDataReader.GetDateTime(int i) lambda_method(Closure , QueryContext , DbDataReader , ResultContext , int[] , ResultCoordinator ) Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.MoveNext() System.Collections.Generic.List..ctor(IEnumerable collection) System.Linq.Enumerable.ToList(IEnumerable source) SecurityCore.Models.OfficerList.GetOfficerList(SecurityCoreContext _context) in OfficerList.cs + var SecLog = _context.SecurityLog.ToList(); SecurityCore.Pages.SecurityLogs.IndexModel.OnGetAsync(string sortOrder, string currentFilter, string searchString, Nullable pageIndex, string entitySelect, string entityFilter, DateTime dateBegin, DateTime dateBeginSelect, DateTime dateEnd, DateTime dateEndSelect) in Index.cshtml.cs + OfficerLists = officerList.GetOfficerList(_context);

Is there a way in the OfficerList class to not loop through SecurityLog in the foreach and somehow pass in the ID of the SecurityLog as a second parameter to OfficerList( along with _context)?

Answer 1

I found that you have used dependency injection multiple times in the OfficeList and SecurityLog classes. This should be the main cause of the error

I suggest that you only need to use dependency injection in the PageModel, pass _context as a parameter to the GetOfficerList method, and I don't think you need to let the OfficeList inherit the SecurityLog class.

After you return the OfficeIDs collection, use OfficerLists Field in the PageModel to store the collection, and store the SecurityLog data collection to the SecurityLog Field.

At the same time I found some errors in your GetOfficerList method. You can directly refer to the following code for testing.

PageModel:

public class HomeModel : PageModel
{ 
    private readonly SecurityCoreContext _context;
    public HomeModel(SecurityCoreContext context)
    {
        _context = context;
    }

    public List<string> OfficerLists { get; set; }
    public IList<SecurityLog> SecurityLog { get; set; }

    public async Task<IActionResult> OnGetAsync()
     {
        OfficerList officerList = new OfficerList();
        OfficerLists = officerList.GetOfficerList(_context);
        SecurityLog = await _context.SecurityLog.AsNoTracking().ToListAsync();
        return Page();
    }
}

OfficerList.cs

public class OfficerList
{
    public List<string> GetOfficerList(SecurityCoreContext _context)
    {
        List<string> OfficerIDs = new List<string>();

        //use the syntax .ToList() to convert object read from db to list to avoid being re-read again
        var SecLog = _context.SecurityLog.ToList();
        var SecLogOfficer = _context.SecurityLogOfficer.ToList();
        var Officer = _context.Officer.ToList();

        int rowID;
        rowID = 0;
        foreach (SecurityLog sl in SecLog)
        {
            int count = SecLogOfficer.Where(slo => slo.SecurityLogID == sl.ID).Count();
            if (count > 0)
            {
                OfficerIDs.Add("");
            }
            foreach (SecurityLogOfficer slo in SecLogOfficer.Where(slo => slo.SecurityLogID == sl.ID))
            {
                OfficerIDs[rowID] = OfficerIDs[rowID] + slo.Officer.FirstName + ", ";
            }
            if (count > 0)
            {
                OfficerIDs[rowID] = OfficerIDs[rowID].Substring(0, OfficerIDs[rowID].Length - 2);
            }
            rowID++;
        } 
        return OfficerIDs;

    }
}

Page:

@page
@model WebApplication_core_razorpage.Pages.HomeModel
@{
    ViewData["Title"] = "Home";
    Layout = "~/Pages/Shared/_Layout.cshtml";
    var i = 0;
}

<h1>Home</h1>

<table>
    @foreach (var item in Model.SecurityLog)
    {
        <tr>
            <td style="width:4% !important">
                @Html.DisplayFor(modelItem => item.ID)
            </td>
            <td style="width:5% !important">
                @Html.DisplayFor(modelItem => item.EventDate)
            </td>

            <td style="width:5% !important">
                @Model.OfficerLists[i]
            </td>
        </tr>
        i++;
    }

</table>

Here is the result of this demo:

https://i.stack.imgur.com/sz2LA.png

Answer 2

I was able to modify's @Yongqing Yu suggested code to get the officer list to display correctly. I decided to pass the id to the OfficerList class and the sorting is setup so that I get the correct officer list for each row.

I did not post this logic originally but I have sort, search and paging that I was able to implement based on Microsoft's Contoso University's demo.
https://docs.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-3.1

With the newly implemented code, the sorting and filtering no longer works for officer list. Everything else works as desired.

How can I address the sorting and searching in my current code below? and more specifically... how can I read (iterate) through the list of OfficerIDs and search for the string value of each list item (concatenated officer list row)?

foreach (SecurityLog secLog in SecurityLog)
        {
            secLogCopy = secLog;

            OfficerLists = officerList.GetOfficerList(_context, secLog, rowID, OfficerIDs);
            if (!String.IsNullOrEmpty(searchString))
            {
                sort = sort.Where(s => OfficerIDs.ToString().Contains(searchString));
            }
            rowID++;
        }

PageModel:

namespace SecurityCore.Pages.SecurityLogs
{
    public class IndexModel : PageModel
    {
        private readonly SecurityCore.Models.SecurityCoreContext _context;

        public IndexModel(SecurityCore.Models.SecurityCoreContext context)
        {
            _context = context;
        }

        public string EventDateSort { get; set; }
        public string EventStartSort { get; set; }
        public string EventEndSort { get; set; }
        public string ContactNameSort { get; set; }
        public string EventTypeSort { get; set; }
        public string ShiftRangeSort { get; set; }

        public string CurrentSort { get; set; }
        public string IDSort { get; set; }

        [DataType(DataType.Date)]
        public Nullable<DateTime> DateEnd { get; set; }
        [DataType(DataType.Date)]
        public Nullable<DateTime> DateBegin { get; set; }
        public Entity Entity { get; set; }


        public PaginatedList<SecurityLog> SecurityLog { get; set; }
        public List<secLog> SecurityLogOfficers { get; set; } = new List<secLog>();
        public List<string> OfficerLists { get; set; }

        [BindProperty]
        public OfficerList officerList { get; set; } = new OfficerList();
        [BindProperty]
        public List<string> OfficerIDs { get; set; } = new List<string>();







    public async Task OnGetAsync(string sortOrder, string currentFilter, string searchString, int? pageIndex,
                                 string entitySelect, string entityFilter, DateTime dateBegin, DateTime dateBeginSelect, DateTime dateEnd, DateTime dateEndSelect)
    {
        selectedEntity = new SelectList(_context.Entity.Where(a => a.Active == "Y"), "Name", "Name");

        CurrentSort = sortOrder;
        IDSort = sortOrder == "ID" ? "ID_Desc" : "ID";
        EventDateSort = sortOrder == "EventDate" ? "EventDate_Desc" : "EventDate";
        ContactNameSort = sortOrder == "ContactName" ? "ContactName_Desc" : "ContactName";
        EventTypeSort = sortOrder == "EventType" ? "EventType_Desc" : "EventType";
        ShiftRangeSort = sortOrder == "ShiftRange" ? "ShiftRange_Desc" : "ShiftRange";            
        OfficerNameSort = sortOrder == "OfficerName" ? "OfficerName_Desc" : "OfficerName";


        IQueryable<SecurityLog> sort = from s in _context.SecurityLog select s;


        switch (sortOrder)
        {
            case "ID_Desc":
                sort = sort.OrderByDescending(s => s.ID);
                break;
            case "ID":
                sort = sort.OrderBy(s => s.ID);
                break;
            case "EventDate":
                sort = sort.OrderBy(s => s.EventDate);
                break;                
            case "ShiftRange":
                sort = sort.OrderBy(s => s.ShiftRange.Name).ThenBy(s => s.EventDate);
                break;
            case "ShiftRange_Desc":
                sort = sort.OrderByDescending(s => s.ShiftRange.Name).ThenBy(s => s.EventDate);
                break;
            case "EventType":
                sort = sort.OrderBy(s => s.EventType.Name).ThenBy(s => s.EventDate);
                break;
            case "EventType_Desc":
                sort = sort.OrderByDescending(s => s.EventType.Name).ThenBy(s => s.EventDate);
                break;                
            case "OfficerName":                    
                sort = sort.OrderBy(s => officerList.ToString()).ThenBy(s => s.EventDate);
                break;
            case "OfficerName_Desc":                    
                sort = sort.OrderByDescending(s => officerList.ToString()).ThenBy(s => s.EventDate);
                break;
            default:
                sort = sort.OrderByDescending(s => s.EventDate);
                break;
        }

        int pageSize = 5;





        SecurityLog = await PaginatedList<SecurityLog>.CreateAsync(sort
        .Include(a => a.Entity)
        .Include(b => b.EventType)
        .Include(c => c.Location)
        .Include(d => d.ShiftRange)
        .Include(e => e.Officer)                                    
        .AsNoTracking(), pageIndex ?? 1, pageSize);



        int rowID;
        rowID = 0;


        foreach (SecurityLog secLog in SecurityLog)
        {
            secLogCopy = secLog;
            OfficerLists = officerList.GetOfficerList(_context, secLog, rowID, OfficerIDs);
            if (!String.IsNullOrEmpty(searchString))
            {
                sort = sort.Where(s => OfficerIDs.ToString().Contains(searchString));
            }
            rowID++;
        }



        if (!String.IsNullOrEmpty(searchString))
        {                                                

            sort = sort.Where(s => s.Narrative.Contains(searchString)
                                || s.RecordLocked.Contains(searchString)
                                || s.ContactName.Contains(searchString)
                                || s.Location.Name.Contains(searchString)
                                || s.EventType.Name.Contains(searchString)
                                || s.ShiftRange.Name.Contains(searchString)
                                || s.ID.ToString().Contains(searchString)
                                || s.SubjectFirst.Contains(searchString)
                                || s.SubjectLast.Contains(searchString));                                    
        }

    }

}

}

OfficerList.cs

public class OfficerList
{
    public List<string> GetOfficerList(SecurityCoreContext _context, SecurityLog secLog, int rowID, List<string> OfficerIDs)
    {            

        int CurrentID = secLog.ID;

        var SecLogOfficer = _context.SecurityLogOfficer.ToList();
        var Officer = _context.Officer.ToList();


        int count = SecLogOfficer.Where(slo => slo.SecurityLogID == CurrentID).Count();

        if (count >= 0)
        {
            OfficerIDs.Add("");
        }
        foreach (secLog slo in SecLogOfficer.Where(slo => slo.SecurityLogID == CurrentID))
        {
            OfficerIDs[rowID] = OfficerIDs[rowID] + slo.Officer.FullName + ", ";
        }
        if (count > 0)
        {
            OfficerIDs[rowID] = OfficerIDs[rowID].Substring(0, OfficerIDs[rowID].Length - 2);
        }


        return OfficerIDs;

    }

}

Page:

@page
@model WebApplication_core_razorpage.Pages.HomeModel
@{
    ViewData["Title"] = "Home";
    Layout = "~/Pages/Shared/_Layout.cshtml";
    var i = 0;
}

<h1>Home</h1>

<table>
    @foreach (var item in Model.SecurityLog)
    {
        <tr>
            <td style="width:4% !important">
                @Html.DisplayFor(modelItem => item.ID)
            </td>
            <td style="width:5% !important">
                @Html.DisplayFor(modelItem => item.EventDate)
            </td>

            <td style="width:5% !important">
                @Model.OfficerLists[i]
            </td>
        </tr>
        i++;
    }

</table>

PaginatedList.cs

public class PaginatedList<T> : List<T>
{
    public int PageIndex { get; private set; }
    public int TotalPages { get; private set; }        

    public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        TotalPages = (int)Math.Ceiling(count / (double)pageSize);

        this.AddRange(items);
    }

    public bool HasPreviousPage
    {
        get
        {
            return (PageIndex > 1);
        }
    }


    public bool HasNextPage => PageIndex < TotalPages;

    public bool ShowFirst
    {
        get
        {
            return (PageIndex != 1);
        }
    }

    public bool ShowLast
    {
        get
        {
            return (PageIndex != TotalPages);
        }
    }

    public static async Task<PaginatedList<T>> CreateAsync(
        IQueryable<T> source, int pageIndex, int pageSize)
    {
        var count = await source.CountAsync();
        var items = await source.Skip(
            (pageIndex - 1) * pageSize)
            .Take(pageSize).ToListAsync();
        return new PaginatedList<T>(items, count, pageIndex, pageSize);
    }
}