LINQ to EF使用查找表 [英] LINQ to EF Working with Lookup Tables

问题描述

我有一个主要的VendorProfile表和一个包含状态代码和日期戳的1-vendorHistory表.下面的查询仅用于检索每个供应商的最新状态(状态代码和日期).但是，我还想将状态代码转换为状态名称，该名称位于StatusCodes查找表中.

I have a main VendorProfile table and a 1-many VendorHistory table that contains status codes and date stamps. The query below works at retrieving only the latest status (status code and date) for each vendor. However, what I also want to do is translate the status code into the status name, which is located in StatusCodes lookup table.

模型图

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表中具有匹配行的Vendors-排除没有历史记录的记录.

As a bonus follow up, I would like to know how to modify the above query to only return Vendors where there are matching rows in VendorHistory table - to exclude records that don't have history records.

推荐答案

您可以先选择最后一个VendorStatusHistory，然后选择其属性:

You can select the last VendorStatusHistory first and then its properties:

_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使用查找表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！