Linq - Order by in Include

Question

I have a situation where OrderBy need to be done for Include object. This is how I have tried so far

Customers query = null;

try
{
    query = _context.Customers
        .Include(x => x.CustomerStatus)
        .ThenInclude(x => x.StatusNavigation)
        .Select(x => new Customers()
        {
            Id = x.Id,
            Address = x.Address,
            Contact = x.Contact,
            Name = x.Name,
            CustomerStatus = new List<CustomerStatus>
            {
                x.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
            }
        })
        .FirstOrDefault(x => x.Id == 3);
}
catch (Exception ex)
{
    throw;
}

The above code successfully ordering the include element but it is not including it's child table. Eg: Customer include CustomerStatus but CustomerStatus not including StatusNavigation tables.

I even tried with this but neither it can help me

_context.Customers
    .Include(x => x.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault())
    .ThenInclude(x => x.StatusNavigation).FirstOrDefault(x => x.Id == 3);

What am I doing wrong please guide me someone

Even I tried this way

var query = _context.CustomerStatus
    .GroupBy(x => x.CustomerId)
    .Select(x => x.OrderByDescending(y => y.Date).FirstOrDefault())
    .Include(x => x.StatusNavigation)
    .Join(_context.Customers, first => first.CustomerId, second => second.Id, (first, second) => new Customers
    {
        Id = second.Id,
        Name = second.Name,
        Address = second.Address,
        Contact = second.Contact,
        CustomerStatus = new List<CustomerStatus> {
            new CustomerStatus
            {
                Id = first.Id,
                CustomerId = first.CustomerId,
                Date = first.Date,
                StatusNavigation = first.StatusNavigation
            }
        },
    }).FirstOrDefault(x => x.Id == 3);

but this is hitting a databases a 3 times and filtering the result in memory. First select all data from customer status and then from status and then from customer then it filter all the data in memory. Is there any other efficient way to do this??

This is how I have prepared by entity class

Answer 1

As @Chris Pratt mentioned once you are doing new Customer inside the select you are creating a new model. You are discarding the models build by the EntityFramework. My suggestion would be have the query just:

query = _context.Customers
    .Include(x => x.CustomerStatus)
    .ThenInclude(x => x.StatusNavigation);

Like this you would have an IQueryable object which it would not be executed unless you do a select from it:

var customer3 = query.FirstOrDefault(x=>x.Id==3)

Which returns the customer and the interlinked tables (CustomerStatus and StatusNavigation). Then you can create the object that you want:

var customer = new Customers()
    {
        Id = customer3.Id,
        Address = customer3.Address,
        Contact = customer3.Contact,
        Name = x.Name,
        CustomerStatus = new List<CustomerStatus>
        {
            customer3.CustomerStatus.OrderByDescending(y => y.Date).FirstOrDefault()
        }
    })

In this way you can reuse the query for creating different response objects and have a single querying to database, but downside is that more memory is used then the original query (even though it shouldn't be too much of an issue).

If the model that is originally return from database doesn't meet the requirements (i.e. you always need to do: CustomerStatus = new List {...} ) it might indicate that the database schema is not well defined to the needs of the application, so a refactoring might be needed.