Exception when trying to load single child property from entity in Entity Framework Core

Question

I'm trying to get just one 'Data' inside my 'Matter' entity.

I can get all from this statement:

.ThenInclude(matter => matter.Data)

but I want just 1 => where it has the latest date created.

Something like this below but it doesn't work.

.ThenInclude(matter => matter.Data.OrderByDescending(i => i.CreatedDate).FirstOrDefault())

I'm getting an exception on my .ThenInclude(). It says the expression should represent a property. Isn't .Data a property?

I also tried it without the .Data with no luck

My exception says:

The ThenInclude property lambda expression 'matter => {from MatterData i in matter.Data orderby [i].CreatedDate desc select [i] => FirstOrDefault()}.Data' is invalid.
The expression should represent a property access: 't => t.MyProperty'.
To target navigations declared on derived types, specify an explicitly typed lambda parameter of the target type, e.g. '(Derived d) => d.MyProperty'.

Here is how I'm trying to fetch a single property of 'Data'

public async Task<Claim> GetClaim(int id)
{
   var query = _autoContext.Claims
     .Include(claim => claim.Matter)
         .ThenInclude(matter => matter.Data.OrderByDescending(i => i.CreatedDate).FirstOrDefault().Data)
     .AsQueryable();

   var result = await query.FirstOrDefaultAsync(c => c.Id == id);

   return result;
}

Here are the classes Claim, Matter and MatterData:

public class Matter
{
    public Matter()
    {
        CreatedDate = DateTime.Now;
        EditedDate = DateTime.Now;
        Data = new List<MatterData>();
        Claims = new List<Claim>();
    }

    public int Id { get; set; }
    public string MatterNumber { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set;}
    public ICollection<MatterData> Data { get; set; }
    public ICollection<Claim> Claims { get; set; }
    public DateTime CreatedDate { get; set; }
    public User CreatedBy { get; set; }
    public DateTime EditedDate { get; set; }
    public User EditedBy { get; set; }
}

public class MatterData
{
    public MatterData()
    {
        CreatedDate = DateTime.Now;
    }

    public int Id { get; set; }
    public string Data { get; set; } 
    public Matter Matter { get; set; }
    public DateTime CreatedDate { get; set; }
    public User CreatedBy { get; set; }
}

public class Claim
{
    public Claim()
    {
        CreatedDate = DateTime.Now;
        EditedDate = DateTime.Now;
        InjuredParty = new List<Injured>();
    }

    public int Id { get; set; }
    public int? ClaimNumber { get; set; }
    public Matter Matter { get; set; }
    public TrustGroup TrustGroup { get; set; } 
    public Attorney Attorney { get; set; }
    public User Contact { get; set; }
    public Status ClaimStatus { get; set; }
    public ClaimType ClaimType { get; set; }
    public bool Approved { get; set; }
    public DateTime? DateApproved { get; set; }
    public DateTime? DateExported { get; set; }
    public ICollection<Injured> InjuredParty { get; set; }
    public DateTime CreatedDate { get; set; }
    public User CreatedBy { get; set; }
    public DateTime EditedDate { get; set; }
    public User EditedBy { get; set; }
}

Michael Kokorin Michael Kokorin · Accepted Answer · 2019-12-04T02:38:45

This row

.ThenInclude(matter => matter.Data.OrderByDescending(i => i.CreatedDate).FirstOrDefault().Data)

is completely incorrect. You can't include "order by" and FirstOrDefault() operations into Include expression. Basically Include expression defines which tables it is required to join in SQL for this query. So you need something like this:

.ThenInclude(matter => matter.Data)
.Select(claim => new {
claim,
MatterData = claim.Matter.Data.OrderByDescending(i => i.CreatedDate).FirstOrDefault().Data
});

Exception when trying to load single child property from entity in Entity Framework Core

1 Answers