LINQ to EF Working with Lookup Tables

问题:

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.

Model Diagram

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

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.

回答1:

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