Including One Subitem From A List property while Making use of ThenInclude()

Question

I have a function that's purpose is to pull an auction by its id from the data base and include it's highest bid and the highest bid's type.

I was able to get the highest bid using this line

var result = _context.Auctions.
      Include(a => a.AuctionBids.OrderByDescending(b => b.BidAmount).Take(1)).
      FirstOrDefault(a => a.AuctionId == id);

When I added the rest to include the Bid Type I used

  var result = _context.Auctions.
      Include(a => a.AuctionBids.OrderByDescending(b => b.BidAmount).Take(1)).ThenInclude(b => b.AuctionBidType).AsNoTracking().
      FirstOrDefault(a => a.AuctionId == id);

But then I get an error

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'AuctionBidTypeID'.
Invalid column name 'AuctionBidID'.
Invalid column name 'AuctionBidTypeID'.
Invalid column name 'BidAmount'.
Invalid column name 'BidDate'.
Invalid column name 'ExternalUserLoginID'.

Removing the .Take(1) works as expected returning all the bids. I've tried moving around .Take(1) but I just get a null response or a compiler error. Anything to point me in the right direction is appreciated

I have also checked using First() in place of Take(1) it gives this error

System.InvalidOperationException: The expression 'a.AuctionBids.AsQueryable().OrderByDescending(b => b.BidAmount).First()' is invalid inside an 'Include' operation, since it does not represent a property access: 't => t.MyProperty'. To target navigations declared on derived types, use casting ('t => ((Derived)t).MyProperty') or the 'as' operator ('t => (t as Derived).MyProperty'). Collection navigation access can be filtered by composing Where, OrderBy(Descending), ThenBy(Descending), Skip or Take operations. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393.

Simplified Classes

These are simplified versions of my models

public partial class Auction
    {
        public Auction()
        {
            AuctionBids = new HashSet<AuctionBid>();
        }

        public long AuctionId { get; set; }
        public long AuctionTypeId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string LongDescription { get; set; }
        public decimal BidIncrement { get; set; }
        public decimal BidWarningCap { get; set; }
        public decimal Reserve { get; set; }
        public int CreatedById { get; set; }
        public DateTime CreatedDate { get; set; }
        public virtual AuctionType AuctionType { get; set; }
        public virtual ICollection<AuctionBid> AuctionBids { get; set; }
    }

public partial class AuctionBid
    {
        public long AuctionBidId { get; set; }
        public long AuctionId { get; set; }
        public int ExternalUserLoginId { get; set; }
        public long AuctionBidTypeId { get; set; }
        public DateTime BidDate { get; set; }
        public decimal BidAmount { get; set; }
        public virtual Auction Auction { get; set; }
        public virtual AuctionBidType AuctionBidType { get; set; }
    }

    public partial class AuctionBidType
    {
        public AuctionBidType()
        {
            AuctionBids = new HashSet<AuctionBid>();
        }

        public long AuctionBidTypeId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public virtual ICollection<AuctionBid> AuctionBids { get; set; }
    }

Answer 1

This seems to be a bug in EF-core 5. The generated SQL query (with SQL Server) has incorrect aliases in one line:

SELECT [t].[AuctionId], [t].[AuctionTypeId], [t].[BidIncrement], [t].[BidWarningCap], [t].[CreatedById], [t].[CreatedDate], [t].[Description], [t].[LongDescription], [t].[Name], [t].[Reserve], [t1].[AuctionBidId], [t1].[AuctionBidTypeId], [t1].[AuctionId], [t1].[BidAmount], [t1].[BidDate], [t1].[ExternalUserLoginId], [t1].[AuctionBidTypeId0], [t1].[Description], [t1].[Name]
  FROM (
      SELECT TOP(1) [a].[AuctionId], [a].[AuctionTypeId], [a].[BidIncrement], [a].[BidWarningCap], [a].[CreatedById], [a].[CreatedDate], [a].[Description], [a].[LongDescription], [a].[Name], [a].[Reserve]
      FROM [Auctions] AS [a]
      WHERE [a].[AuctionId] = CAST(1 AS bigint)
  ) AS [t]
  OUTER APPLY (
-- Wrong aliases in line below. [t] should be [t0]
      SELECT [t].[AuctionBidId], [t].[AuctionBidTypeId], [t].[AuctionId], [t].[BidAmount], [t].[BidDate], [t].[ExternalUserLoginId], [a1].[AuctionBidTypeId] AS [AuctionBidTypeId0], [a1].[Description], [a1].[Name]
      FROM (
          SELECT TOP(1) [a0].[AuctionBidId], [a0].[AuctionBidTypeId], [a0].[AuctionId], [a0].[BidAmount], [a0].[BidDate], [a0].[ExternalUserLoginId]
          FROM [AuctionBid] AS [a0]
          WHERE [t].[AuctionId] = [a0].[AuctionId]
          ORDER BY [a0].[BidAmount] DESC
      ) AS [t0] -- This alias
      INNER JOIN [AuctionBidType] AS [a1] ON [t].[AuctionBidTypeId] = [a1].[AuctionBidTypeId]
  ) AS [t1]
  ORDER BY [t].[AuctionId], [t1].[BidAmount] DESC, [t1].[AuctionBidId], [t1].[AuctionBidTypeId0]

When I try a similar query on a different database model the correct alias is taken, but I can't conclude which part in the query or class/property names confuses EF.

Fortunately you can use a work-around (at least, I can with your model):

var result = _context.Auctions.AsNoTracking()
    .Include(a => a.AuctionBids.OrderByDescending(b => b.BidAmount).Take(1))
        .ThenInclude(b => b.AuctionBidType)
    .Where(a => a.AuctionId == id)
    .AsEnumerable()
    .FirstOrDefault();

It appears that FirstOrDefault(a => a.AuctionId == id) is the part that confuses the query generator. Replacing it by Where makes the query run. Then it's just a question of switching to in-memory evaluation (.AsEnumerable()) and applying .FirstOrDefault() there.

In EF-core 6 this error is fixed. Alias [t1] is used where the query above has [t] and [t0].