LINQ到EF与查找表工作(LINQ to EF Working with Lookup Table

我有一个主VendorProfile表，包含状态代码和日期戳1-多VendorHistory表。 下面的查询工作在仅检索每个供应商的最新状态（状态代码和日期）。 不过，我也希望做的是状态代码翻译成状态的名字，它位于StatusCodes查找表。

模型图

public IEnumerable<BrowseStatusModel> BrowseByStatus()
{
    IQueryable<BrowseStatusModel> viewModel = _db.VendorProfiles
    .Include("VendorStatusHistory")
    .Include("StatusCodes")
    .Select(s => new BrowseStatusModel
    {
        ProfileID = s.ProfileID,
        Name = s.Name,
        CompanyName = s.CompanyName,
        DateCreated = s.DateCreated,
        Status = s.VendorStatusHistories.OrderByDescending(o => o.DateCreated).FirstOrDefault().Status, 
        StatusDate = s.VendorStatusHistories.OrderByDescending(o => o.DateCreated).FirstOrDefault().DateCreated,
        StatusName = ???
    })
    .OrderBy(x => x.ProfileID);
    return viewModel;
}

作为奖励跟进，我想知道如何修改上面的查询只返回凡是有VendorHistory表的匹配行供应商 - 排除没有历史记录的记录。

Answer 1:

您可以选择最后VendorStatusHistory第一，那么它的属性：

_db.VendorProfiles
.Select(s => new { 
                   Profile = s, 
                   LastHist = s.VendorStatusHistories
                               .OrderByDescending(o => o.DateCreated)
                               .FirstOrDefault()
                 })
    .Select(x => new BrowseStatusModel
    {
        ProfileID = x.Profile.ProfileID,
        Name = x.Profile.Name,
        CompanyName = x.Profile.CompanyName,
        DateCreated = x.Profile.DateCreated,
        Status = x.LastHist.Status, 
        StatusDate = x.LastHist.DateCreated,
        StatusName = x.LastHist.StatusCOde.StatusName
    })
    .OrderBy(x => x.ProfileID);

文章来源: LINQ to EF Working with Lookup Tables