c# - 如何仅在 EF Core 中包含相关表的最新记录

Question

我正在 .net core 3.1 中构建应用程序。我想显示 Assets 列表以及每个 Assets 进行视觉和完整拍打测试的最后日期。

我使用的表格/模型如下:

  public class Asset
    {
        public int AssetID { get; set; }
        public string Title { get; set; }
        public int AssetCatID { get; set; }
        public string Manufacturer { get; set; }
        public bool RequiresPAT { get; set; }

        public AssetCat AssetCat { get; set; }
        public PAT PAT { get; set; }
    }


    public class AssetCat
    {
        public int AssetCatID { get; set; }
        public string CategoryTitle { get; set; }

        public virtual ICollection<Asset> Assets { get; set; }
    }

    public class PAT
    {
        public int PATID { get; set; }
        public int AssetID { get; set; }
        public int CheckTypeID { get; set; }
        public DateTime CheckDate { get; set; }
        public string CheckedBy { get; set; }

        public Asset Asset { get; set; }
        public CheckType CheckType { get; set; }
    }

我没有在此处包含 CheckType 表，但 CheckTypeID 1 = Full Test，CheckTypeID 2 = Visal Test

RequiresPAT 的每个 Assets 都将有多个条目用于两种类型的测试，我需要列出 Assets 以及每种类型的最后一次测试的日期。

  var AssetQuery = _context.Assets
    .Where(a => a.RequiresPAT)
    .Include(a => a.AssetCat)
    .Include(a => a.PAT) // needs to just include latest PAT record where CheckTypeID = 1 if it exists AS FullPAT
    .Include(a => a.PAT) // needs to just include latest PAT record where CheckTypeID = 2 if it exists AS VisualCheck
    .ToListAsync();

我已经看到在 EF 5 中稍后会在包含中使用 .Where，但目前尚不可用。我试过在 PAT 记录上使用 .OrderByDecending(p => p.CheckDate).FirstorDefault() 的变体，但我无法弄清楚如何将它们组合在一起。

我想返回一个模型以在索引样式页面上使用，理想情况下显示如下内容:

Model.Asset.AssetID | Model.Asset.AssetTitle | Model.Asset.AssetCat.CategoryTitle | Model.Asset.FullPat.CheckDate | Model.Asset.VisualCheck.CheckDate

我是 .net/ef/c# 的新手，因此不胜感激。我确切地知道如何在 SQL 中为经典 ASP 编写 View ，但试图找出 .net 核心的最佳方法

Answer 1

正如您在 ef-core 问题 issue-1833 here 中看到的那样有以下评论

Historically this was not supported by EF, However this would be a nice feature to have. We had to implement something similar ourselves in the our code base using Linq tree rewriting. It hope the EF team considers this.

所以你唯一的解决方案是转到 EF-Core 5，或者做一些类似变通解决方案的事情，这在性能方面很糟糕

var story = await _context.Stories
    .Include(x => x.Paragraphs)
    .Include(x => x.User)
    .Include(x => x.Tips)
    .Include(x => x.Images)
    .Include(x => x.Comments)
    .ThenInclude(x => x.User)
    .SingleOrDefaultAsync(x => x.Id == storyId);
if (story == null)
    return null;

story.Comments = story.Comments.Where(x => !x.IsDeleted).ToList();
story.Images = story.Images.Where(x => !x.IsDeleted).ToList();
story.Paragraphs = story.Paragraphs.Where(x => !x.IsDeleted).ToList();
story.Tips = story.Tips.Where(x => !x.IsDeleted).ToList();
return story;

所以暂时解决它(如果性能不是太忙)或使用 EF Core 5 预览 preview announcement

如评论中所述，唯一可行的选择:

The only workaround is writing raw SQL but in many cases you need it for almost all of your queries, so that's not an option or else why use an ORM to begin with.