5

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.

enter image description here

And here is my database schema and sample results

enter image description here

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);
    }

}
Rob C
  • 150
  • 2
  • 14
  • `_context.SecurityLog.Include(x=>x.SecurityLogOfficer).ThenInclude(x=>x.Officer).Where(....).Select(x=> new PageViewModel{ OfficersNames=string.Join(",",x.SecurityLogOfficers.Select(o=>o.Officer.Name)) }).ToPagedList(page)`, modify your Models and ViewModels, until you can use this query. – Dongdong Jan 30 '20 at 14:20
  • 1
    this post will help you map many-many relationship: https://www.thereformedprogrammer.net/updating-many-to-many-relationships-in-entity-framework-core/ – Dongdong Jan 30 '20 at 14:26
  • this is good one for paging: https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-3.1 – Dongdong Jan 30 '20 at 14:31
  • Thank you for your suggestions and links... This appears to be a solution for MVC and not asp.net core razor pages, which I am using. Will this work for razor? I also added my many-to-many relationships to my original question. – Rob C Jan 30 '20 at 14:37
  • 1
    Please reduce the code to required parts. Even 2-3 properties should be fine, no need to have code of the View, not sure if DBContext is much of help. And it's not quite clear what the question is. – Reza Aghaei Feb 07 '20 at 16:18
  • @RezaAghaei - I reduced my lines of code to the required parts per your suggestion. The dbcontext code was added to show the many-to-many relationships based on a prior comment. All remaining code I believe to be relevant to the problem. I also included the paging model so that the reader was aware. To clarify the question... I am trying to get search functionality working for the Officer Names column, which comes from another model. With my main model, SecurityLog, I am able to search for anything in this model such as EventDate, location, entity, etc... – Rob C Feb 07 '20 at 20:33
  • @RobC When you want to search base on a 1-* or *-* relationship, you usually need to use `Any` in the criteria. The same thing than you do using `EXISTS` in SQL. You can also use `Contains` in linq, like you use `IN` in SQL. I tried to address the problem in a clearer example. Hope it helps :) – Reza Aghaei Feb 08 '20 at 09:23

2 Answers2

5

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

enter image description here

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:

enter image description here

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();
    }
}
Reza Aghaei
  • 120,393
  • 18
  • 203
  • 398
  • Don't get confused with `ArticlesSearchModel` and `ArticlesBusinessLogic`. They are just to have a better structure of the code, however the main point of the answer is in the first part. The code sample here is to help you to get a better understanding of `Any` and how it works. It also shows you a better way of creating many-to-many relationships. – Reza Aghaei Feb 10 '20 at 18:05
  • Thank you. I will review your answer. – Rob C Feb 10 '20 at 18:19
  • You're welcome. Hope it helps :) The reason that I used this pattern is because it's very clear and easy to understand by separating the concerns. You can see I've used here in [this answer](https://stackoverflow.com/a/33154580/3110834) as well. – Reza Aghaei Feb 10 '20 at 18:25
  • I was able to post a working solution, although my search filter does not work while trying to search for a list of officers that are within the securitylog model. Any ideas? Error --> "Sequence contains more than one element" – Rob C Feb 12 '20 at 16:29
  • The first thing that you need to fix in the model (Which will be help a lot) is removing `SecurityLogOfficer` as entity, and have a real many-many relationship between `SecurityLog` and `Officer`. Then each `SecurityLog` will have a collection of `Officers` isn't it much cleaner? – Reza Aghaei Feb 12 '20 at 16:34
  • The other problem that I see in your code is `SecurityLogOfficers` which should be virtual property like this `public virtual ICollection SecurityLogOfficers { get; set; }` – Reza Aghaei Feb 12 '20 at 16:49
  • Rob, if you would like learn it, I strongly recommend you to start by my example. It makes a lot of things clearer for you. First create these tables **Articles(** Id, Title, Abstract, PublishDate **)** - **Authors(** Id, FirstName, LastName **)** - **ArticlesAuthors(** ArticleId, AuthorId **)** . Then create model from database and then write the code. – Reza Aghaei Feb 12 '20 at 16:55
  • Reza - Yes I would like to learn. I will try and re-create your example with the articles, authors and articlesauthors. Going back to your first comment about removing SecurityLogOfficer as an Entity. I am not sure that I can do this b/c that table ties SecurityLog and Officer together. – Rob C Feb 12 '20 at 17:17
  • Remove the column `ID` from table `SecurityLogOfficer`. Make the combination of `SecurityLogId, OfficerId` as Primary Key. Save changes in database. Delete your entity framework model. Create model from database again man in wizard and you will see there is no more `SecurityLogOfficer` entity in entity framewrork models while it exists in db. And then as a result, you will see `Officers` as children of `SecurityLog` ans vise versa. – Reza Aghaei Feb 12 '20 at 17:23
  • Reza - I removed the ID from SecurityLogOfficer. Made SecurityLogId and OfficerId as Primary, saved changes. I then just deleted the SecurityLog model. I did not use the wizard initially so I am unfamiliar. When I try to run this as the command line it errors... PM> dotnet ef dbcontext scaffold- "Server=servername;Database=dbname;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models – Rob C Feb 12 '20 at 20:37
  • dotnet : Could not execute because the specified command or file was not found. At line:1 char:1 + dotnet ef dbcontext scaffold- "Server=servername;Database=dbname ... + CategoryInfo : NotSpecified: (Could not execu... was not found.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError Possible reasons for this include: * You misspelled a built-in dotnet command. * You intended to execute a .NET Core program, but dotnet-ef does not exist. * You intended to r un a global tool, but a dotnet-prefixed executable with this name could not be foun – Rob C Feb 12 '20 at 20:39
  • I think I cannot help to fix this issue. You may want to do it in a clean project. The same way that you had created it first. – Reza Aghaei Feb 12 '20 at 20:41
  • Thanks for the feedback, hope you can get the problem solved by following the pattern explained in the answer. – Reza Aghaei Feb 13 '20 at 12:52
  • Reza - I removed the ID from SecurityLogOfficer. Made SecurityLogId and OfficerId as Primary, saved changes. I then created a new project, ran the scaffolding prompt, and SecurityLogOfficer table is still showing under Models. Any suggestions? – Rob C Feb 13 '20 at 18:43
  • Are you using ASP.NET CORE + .NET CORE + EF Core? or ASP.NET CORE + .NET 4.X + EF 6.X? – Reza Aghaei Feb 14 '20 at 03:45
  • ASP.NET CORE + .NET CORE + EF Core – Rob C Feb 14 '20 at 13:01
  • It's limitation of EF CORE that cannot generate relations like that. I've used EF 6.x. Sorry for the confusion, but that's why you should always use good tags for the answer. I'll try to share something about EF code, again, using Article-Author relationship. – Reza Aghaei Feb 14 '20 at 17:06
  • @RobC I added EF CORE example as well – Reza Aghaei Feb 14 '20 at 18:55
1

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);
  1. 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

  2. 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.

AliReza Sabouri
  • 4,355
  • 2
  • 25
  • 37