Does Entity Framework allow you to have filtered properties?

Question

I'm wondering if EF-Core offers us the ability to have a property which is already "pre-filtered" with a where clause?

For example:

I have a table of Users (.. therefore.. DbSet<User> Users.) Each user can have zero to many Orders (.. therefore DbSet<Order> Orders).

So a user would look like:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
    public List<Order> Orders { get; set; } 
}

So now I would like to have a property that is THE MOST RECENT ORDER:

e.g.

public Order MostRecentOrder { get; set; }

This would be decided by the hardcoded rule: the most recent Order.

Is this possible?

Otherwise I feel like I have to have this LINQ query in every EF-core query I write where I care about this property.

Also, I once tried making an extension method but the EF query generator or something crashed saying it can't do translate that to SQL.

So, is this possible?

Answer 1

There are Filtered Includes that allow loading only the last order, and Auto Include. Unfortunately, currently they can't be combined (https://github.com/dotnet/efcore/issues/30883)

What you could do as an alternative:

Create the property as a getter on the User Class:

public Order? MostRecentOrder => this.Orders.OrderByDesc(o => o.CreatedOn).FirstOrDefault()

Create a Method on the DbContext returning an IQueryable for Users with the include applied:

public IQueryable<User> UserWithOrder() {
    return this.Users.Include(u => u.Orders.OrderByDesc(o => o.CreatedOn).Take(1))
}

Then you should always use the Method to access the users. Using the DbSet directly and accessing the MostRecentOrder property would still result in an exception.

If you want compile-time safety, you can probably make something work with creating an extra class that has this property and is returned from the method instead of the User class directly, but from the top of my head I'm not sure about an easy generic solution to that.

Answer 2

First you can set the list into descending order of Id using

OrderByDescending(x => x.Id)

Then after using

FirstOrDefault() 

you can get most recent Order.

Or if you want to set it using one line then you can use following code

Order MostRecentOrder = await Orders.OrderByDescending(x => x.OrderId).FirstOrDefault();

Answer 3

you can use query filter and register it in onModelCreating overrided method in your entity configuration.

like this

protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
     modelBuilder.Entity<Order>().HasQueryFilter(
         p => 
         p.MostRecentOrder.hasSpecificCondition()
     );
 }

the filter is automatically applied to all queries that retrieve instances of the Order entity.