如何搜索来自另一个页面模型的串联名称列表? [英] How do I search a list of concatenated names coming from another page model?
问题描述
我的项目中有多个模型，但是在下面的屏幕中，大多数字段/属性都位于SecurityLog模型中.
下面是我要显示级联人员的列表.我的搜索和列标题排序对于除警官姓名以外的所有内容均正常工作.我很难将官员姓名b/c纳入列表，该列表来自其他页面模型.
这是我的数据库架构和示例结果
基于Microsoft的Contoso大学的演示，我可以实现排序，搜索和分页.
搜索逻辑
我们要查找基于 SerachTerm ， PublishDateFrom 和 PublishDateTo 的文章:
- 如果文章的标题或摘要包含该术语，则文章应成为结果的一部分.
- 如果文章作者的名字和姓氏的组合包含该术语，则文章应成为结果的一部分.
- 如果发布日期大于或等于
PublishDateFrom，则文章应成为结果的一部分，并且如果发布日期小于或等于PublishDateTo，文章应该是结果的一部分.
以下是搜索模型:
公共类ArticlesSearchModel{公共字符串SearchTerm {get;放;}公开DateTime?PublishDateFrom {get;放;}公开DateTime?PublishDateTo {get;放;}} 以下是搜索代码:
请注意:
Inculde与搜索和只是为了在输出结果中包含相关实体.
公共类文章BusinessLogic{公共IEnumerable< Article>搜索(ArticlesSearchModel模型){使用(var db = new ArticlesDBEntities()){var result = db.Articles.Include(x => x.Authors).AsQueryable();如果(型号==空)返回result.ToList();如果(！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 ..包含(model.SearchTerm))))；如果(model.PublishDateFrom.HasValue)result = result.Where(x => x.PublishDate> = model.PublishDateFrom);如果(model.PublishDateFrom.HasValue)result = result.Where(x => x.PublishDate< = model.PublishDateTo);返回result.ToList();}}} EF CORE-示例
如上所述，目前，尚不支持没有实体类来表示联接表的多对多关系，因此使用EF CORE的模型将是:
以下是搜索代码:
请注意:
Inculde与搜索和只是为了在输出结果中包含相关实体.
public IEnumerable< Article>搜索(ArticlesSearchModel模型){使用(var db = new ArticlesDbContext()){var result = db.Articles.Include(x => x.ArticleAuthor).nnInclude(x => x.Author).AsQueryable();如果(型号==空)返回结果；如果(！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).包含(model.SearchTerm))))；}如果(model.PublishDateFrom.HasValue){result = result.Where(x => x.PublishDate> = model.PublishDateFrom);}如果(model.PublishDateFrom.HasValue){result = result.Where(x => x.PublishDate< = model.PublishDateTo);}返回result.ToList();}} 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://docs.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);
}
}
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 toPublishDateTo, 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:
Inculdedoesn'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:
Inculdedoesn'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();
}
}
这篇关于如何搜索来自另一个页面模型的串联名称列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
