Get latest child record - EF Core

c# entity-framework-core linq

Question

The table Users has a few relationships on another table

So here is the snippet code

var users = DbContext.Users
           .Include(x => x.ApplicationUserGroups)
                .ThenInclude(x => x.ApplicationGroup)
           .Include(x => x.FormSubmit)
                .ThenInclude(x => x.FormLevel)
           .Where(x => true);

So now, I need the latest record from FormSubmit. So I modified the query somewhat like this:

var users = DbContext.Users
           .Include(x => x.ApplicationUserGroups)
                .ThenInclude(x => x.ApplicationGroup)
           .Include(x => x.FormSubmit.OrderByDescending(i => i.CreationDate).Take(1))
                .ThenInclude(x => x.FormLevel)
           .Where(x => true);

But I got the exception:

"Message": "The Include property lambda expression 'x => {from FormSubmit i in x.FormSubmit orderby [i].CreationDate desc select [i] => Take(1)}' 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'. For more information on including related data, see http://go.microsoft.com/fwlink/?LinkID=746393."

Any idea how to get latest record from FormSubmit?

1
0
12/19/2018 10:29:44 AM

Accepted Answer

So far you cannot achieve it directly because neither eager or lazy loading in EF Core supports ordering or filtering in child collection.

As your requirement is not clear I am providing possible alternative solution for all possible cases:

1) If you want latest FormSubmit of a certain user:

var users = DbContext.Users
           .Include(x => x.ApplicationUserGroups)
                .ThenInclude(x => x.ApplicationGroup)
           .Include(x => x.FormSubmit)
                .ThenInclude(x => x.FormLevel)
           .Where(x => true && x.UserId == userId).FirstOrDefault();

var latestFormSubmit = users.Select(u => u.FormSubmit).OrderByDescending(fs => fs.CreationDate).FirstOrDefault();

2) If you want latest FormSubmit among all users:

var users = DbContext.Users
           .Include(x => x.ApplicationUserGroups)
                .ThenInclude(x => x.ApplicationGroup)
           .Include(x => x.FormSubmit)
                .ThenInclude(x => x.FormLevel)
           .Where(x => true).ToList();

var latestFormSubmit = users.SelectMany(u => u.FormSubmit).OrderByDescending(fs => fs.CreationDate).FirstOrDefault();

    //Or

var latestFormSubmit = DbContext.FormSubmits.Include(x => x.FormLevel).OrderByDescending(fs => fs.CreationDate).FirstOrDefault();

Note: You can omit .Include(x => x.ApplicationUserGroups).ThenInclude(x => x.ApplicationGroup) from the query if you need only latest FormSubmit.

2
12/19/2018 9:15:10 AM

Popular Answer

The navigation properties (like your User.FormSubmit property) are designed to hold all the related entities (or nothing, if they're not loaded).

You simply can't use Include() like that. Include() is only used for retrieving all the related records.

If you just want a single related record, you need to do that separately. For example:

var users = DbContext.Users
           .Include(x => x.ApplicationUserGroups)
                .ThenInclude(x => x.ApplicationGroup)
           .Where(x => true);

foreach (var user in users) {
    var latest = DbContext.FormSubmits.Include(s => s.FormLevel).Where(s => s.UserId == user.UserId).OrderByDescending(i => i.CreationDate).FirstOrDefault()
    //Do other stuff...
}

Doing it this way will only retrieve the latest post for each user. If you do .Include(x => x.FormSubmit).ThenInclude(x => x.FormLevel) in your first query and then filter, it will end up getting all the submissions for each user, which is more data than you are going to actually use.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow