How do I search a list of concatenated names coming from another page model?

Question

I have multiple models in my project, but in the screen below, most of the fields/properties reside in the SecurityLog model.

Below is where I am displaying the concatenated list of officers. I have search and column header sorting working correctly for everything except for the officer names. I am having difficulty incorporating the officer names b/c the list is coming from another page model.

And here is my database schema and sample results

I have sort, search and paging that I was able to implement based on Microsoft's Contoso University's demo.

https://learn.microsoft.com/en-us/aspnet/core/data/ef-rp/sort-filter-page?view=aspnetcore-3.1

How can I address the searching issue for Officer Names in my current code below? and more specifically for the searching... 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 CurrentSort { 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;
        EventDateSort = sortOrder == "EventDate" ? "EventDate_Desc" : "EventDate";            
        OfficerNameSort = sortOrder == "OfficerName" ? "OfficerName_Desc" : "OfficerName";


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


        switch (sortOrder)
        {
            case "EventDate":
                sort = sort.OrderBy(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.ContactName.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);
    }
}

SecurityLog.cs

namespace SecurityCore.Models
{
public class SecurityLog
{                

    [BindProperty(SupportsGet = true)]
    public int ID { get; set; }

    [DataType(DataType.Date)]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    [Display(Name = "Event Date")]
    public System.DateTime EventDate { get; set; }

    public virtual Officer Officer { get; set; }        
    public virtual List<secLog> SecurityLogOfficers { get; set; }       


  }
}

Relationships

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

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

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

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

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

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

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

}

Answer 1

Search based on Many-to-Many Relationship

Talking about articles and authors, when each article may have many authors, let's say you are going to search based on a term and find Articles where the article name or article abstract contains the term or one of the authors of the article have the term in their first name or their last name.

EF 6 - Many-To-May without entity class for Relationship

You can handle these cases in a Linq query using Any, the same way that you can handle in a SQL query using EXISTS:

Where(article=> article.Title.Contains(term) || 
                article.Abstract.Contains(term) || 
                article.Authors.Any(author => 
                    author.FirstName.Contains(term) ||
                    author.LastName.Contains(searchTerm)))

It doesn't exactly generate the following SQL Query, but the logic is quite similar to having the following in SQL:

FROM Articles
WHERE (Articles.Title LIKE '%' + @Term + '%') OR 
      (Articles.Abstract LIKE '%' + @Term + '%') OR 
      EXISTS (SELECT * FROM Authors 
              WHERE (Authors.FirstName LIKE '%' + @Term + '%') OR
                    (Authors.LastName LIKE '%' + @Term + '%'))

EF CORE - Many-To-May with entity class for Relationship

At the moment, Many-to-Many relationships without an entity class to represent the join table are not yet supported.

You can handle these cases in a Linq query using Any, the same way that you can handle in a SQL query using EXISTS + Join:

.Where(article => article.Title.Contains(model.SearchTerm) ||
                  article.Abstract.Contains(model.SearchTerm) ||
                  article.ArticlesAuthors.Any(au =>
                      (au.Author.FirstName).Contains(model.SearchTerm) ||
                      (au.Author.LastName).Contains(model.SearchTerm)))

It doesn't exactly generate the following SQL Query, but the logic is quite similar to having the following in SQL:

FROM Articles
WHERE (Articles.Title LIKE '%' + @Term + '%') OR 
      (Articles.Abstract LIKE '%' + @Term + '%') OR 
      EXISTS (SELECT * FROM ArticlesAuthors 
              INNER JOIN Authors 
              ON ArticlesAuthors.AuthorId = Authors.Id
              WHERE ((Authors.FirstName LIKE '%' + @Term + '%') OR
                     (Authors.LastName LIKE '%'+ @Term + '%')) AND 
                     (Articles.Id = ArticlesAuthors.ArticleId))

EF 6 - Example

The question is a bit cluttered including search sort and a lot of code and needs more focus. To make it more useful and more understandable for you and feature readers, I'll use a simpler model with fewer properties and easier to understand.

As you can see in the EF diagram, the ArticlesAuthors table has not been shown in diagram because it's a many-to-many relationship containing just Id columns of other entities without any extra fields

Search Logic

We want to find articles based on a SerachTerm, PublishDateFrom and PublishDateTo:

• If the title or abstract of article contains the term, article should be part of the result.
• If the combination of first name and last name of an author of the article contains the term, article should be part of the result.
• If the publish date is greater than or equal to PublishDateFrom, article should be part of the result, also if the publish date is less than or equal to PublishDateTo, article should be part of the result.

Here is a model for search:

public class ArticlesSearchModel
{
    public string SearchTerm { get; set; }
    public DateTime? PublishDateFrom { get; set; }
    public DateTime? PublishDateTo { get; set; }
}

Here is the code for search:

Please note: Inculde doesn't have anything to do with search and it's just for including the the related entities in output result.

public class ArticlesBusinessLogic
{
    public IEnumerable<Article> Search(ArticlesSearchModel model)
    {
        using (var db = new ArticlesDBEntities())
        {
            var result = db.Articles.Include(x => x.Authors).AsQueryable();

            if (model == null)
                return result.ToList();

            if (!string.IsNullOrEmpty(model.SearchTerm))
                result = result.Where(article => (
                    article.Title.Contains(model.SearchTerm) ||
                    article.Abstract.Contains(model.SearchTerm) ||
                    article.Authors.Any(author =>
                    (author.FirstName + " " + author.LastName).Contains(model.SearchTerm))
                ));

            if (model.PublishDateFrom.HasValue)
                result = result.Where(x => x.PublishDate >= model.PublishDateFrom);

            if (model.PublishDateFrom.HasValue)
                result = result.Where(x => x.PublishDate <= model.PublishDateTo);

            return result.ToList();
        }
    }
}

EF CORE - Example

As I mentioned above, at the moment, Many-to-Many relationships without an entity class to represent the join table are not yet supported, so the model using EF CORE will be:

Here is the code for search:

Please note: Inculde doesn't have anything to do with search and it's just for including the the related entities in output result.

public IEnumerable<Article> Search(ArticlesSearchModel model)
{
    using (var db = new ArticlesDbContext())
    {
        var result = db.Articles.Include(x=>x.ArticleAuthor)
                                .ThenInclude(x=>x.Author)
                                .AsQueryable();

        if (model == null)
            return result;

        if (!string.IsNullOrEmpty(model.SearchTerm))
        {
            result = result.Where(article => (
                article.Title.Contains(model.SearchTerm) ||
                article.Abstract.Contains(model.SearchTerm) ||
                article.ArticleAuthor.Any(au =>
                    (au.Author.FirstName + " " + au.Author.LastName)
                        .Contains(model.SearchTerm))
            ));
        }
        if (model.PublishDateFrom.HasValue)
        {
            result = result.Where(x => x.PublishDate >= model.PublishDateFrom);
        }
        if (model.PublishDateFrom.HasValue)
        {
            result = result.Where(x => x.PublishDate <= model.PublishDateTo);
        }

        return result.ToList();
    }
}

Answer 2

You are doing a lot of things wrong :

1. you can not use .ToString() on classes or lists. so first you have to remove or change these lines. for example :

  sort = sort.Where(s => OfficerIDs.ToString().Contains(searchString));

  sort = sort.OrderBy(s => officerList.ToString()).ThenBy(s => s.EventDate);

  sort = sort.OrderByDescending(s => officerList.ToString()).ThenBy(s => s.EventDate);

2. you are almost loading the entire data from your database tables every time your page loads or your search or sorting changed. Of course, having paging makes this problem a little fuzzy here

3. you are not using entity framework to load your relational data so you can not write a query that loads what you need or what user searched for. (you are fetching data from the database on separate parts)

---

I know this is not what you looking for but honestly, I tried to answer your question and help you solved the problem but I ended up rewriting whole things again ... you should break your problem into smaller pieces and ask a more conceptual question.