c# - 如何搜索来自另一个页面模型的串联名称列表？

Question

我的项目中有多个模型，但是在下面的屏幕中，大多数字段/属性都位于SecurityLog模型中。

下面是我显示级联人员列表的位置。我的搜索和列标题排序对于除警官姓名以外的所有内容均正常工作。我很难合并官员姓名b/c，该列表来自其他页面模型。



这是我的数据库架构和示例结果



我可以基于Microsoft Contoso University的演示进行排序，搜索和分页。

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

我如何在下面的当前代码中解决有关人员姓名的搜索问题？
以及更具体地用于搜索...我如何阅读(迭代)OfficialID列表并搜索每个列表项(连接的官员列表行)的字符串值？

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


  }
}

关系

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

基于多对多关系的搜索

谈论文章和作者时，当每篇文章可能有很多作者时，假设您要根据term进行搜索，并找到其中文章名称或文章摘要包含该术语或其中一位作者的术语位于他们的名字或姓氏。

EF 6-不存在用于关系的实体类的多对五

您可以使用Any在Linq查询中处理这些情况，与使用EXISTS在SQL查询中处理的情况相同:

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

它不会完全生成以下SQL查询，但是其逻辑与在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-与关系的实体类的多对五

目前，尚不支持没有实体类来表示联接表的多对多关系。

您可以使用 Any在Linq查询中处理这些情况，与使用 EXISTS + Join在SQL查询中处理的情况相同:

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

它不会完全生成以下SQL查询，但是其逻辑与在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-范例

这个问题有点困惑，包括搜索排序和大量代码，需要更多关注。为了使它对您和功能读者更有用和更易理解，我将使用具有更少属性且更易于理解的更简单模型。

如您在EF图中所见， ArticlesAuthors表未在图中显示，因为它是一个多对多关系，仅包含其他实体的ID列，而没有任何其他字段



搜索逻辑

我们要查找基于 SerachTerm， PublishDateFrom和 PublishDateTo的文章:

如果文章的标题或摘要包含该术语，则文章应成为结果的一部分。

如果文章作者的名字和姓氏的组合包含该术语，则文章应成为结果的一部分。

如果发布日期大于或等于PublishDateFrom，则article应该是结果的一部分；如果发布日期小于或等于PublishDateTo，则article应该是结果的一部分。

这是一个搜索模型:

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

这是搜索代码:

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-示例

如上所述，目前，尚不支持没有实体类来表示联接表的多对多关系，因此使用EF CORE的模型将是:



这是搜索代码:

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