How do I include the latest record only of a related table in EF Core

Question

I'm building an app in .net core 3.1. I want to show a list of Assets along with the last date each asset had a visual and a full pat test.

The tables/models I'm using are as follows:

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

I haven't included the CheckType table here, but CheckTypeID 1 = Full Test, CheckTypeID 2 = Visal Test

Each Asset that RequiresPAT will have multiple entries for both types of test, and I need to list the assets along with the date of the last test of each type.

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

I've seen that using a .Where on a include is coming later in EF 5, but isn't available yet. I've tried using varations of .OrderByDecending(p => p.CheckDate).FirstorDefault() on the PAT records, but I just can't work out how to fit it all together.

I want to return a model to use on an index style page, ideally showing something like:

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

I'm quite new to .net / ef / c# so any pointers would be appreciated. I know exactly how to do this writing views in SQL for classic ASP, but trying to work out the most optimal methods for .net core

user_1643676 · Answer

As you can see in the ef-core issues issue-1833 here there is the following comment

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.

So your only solution would be to go to EF-Core 5, or do something like the workaround solution which is dreadful performance wise

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;

So work around it for the time being (if performance is not too much of a hustle) or go with the EF Core 5 preview preview announcement

As stated in the comments, the only other viable option:

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.

How do I include the latest record only of a related table in EF Core

1 Answers