Entity Framework ASP.NET MVC : find last record from related table in a many to many scenario

Question

I just finished a solution but the client requested to implement many filters on the results.

In this case the client needs to filter all the orders that are in what ever status they choose.

In summary this is a sample of the tables I'm having problems to query and filter with Entity Framework and lambda syntax.

Each complaint has a description and insert date.
Users can change the status of the complaint. That change is recorded in the intermediate table ComplaintStatus

The Status table has all the possible status and the records are "On Hold", "Pending Repair", "In Delivery", etc

As you can see, this is many to many relation as we can have many complaints in many statuses.

More over, a complaint can change to status id 1, later to status id 2 and return back to status 1 and all is recorder historically in the ComplaintStatus intermediate table avoiding deletions.

So, for the filter, I need to find the last Status of a Complaint.

This is part of my code:

var query = dbContext.Complaint                         // To get the Complaints table 
                     .Include(c => c.ComplaintStatus)   // To get the ComplaintStatus table
                     .Include(s => d.ComplaintStatus.Select(st => st.Status))       // To get the Status table
                     .AsQueryable();

var complaintId = 0;
var statusId = 0; 

// As per my research we need to check if the filters variables has values or return all 
// if we do not have filters in place.
// For this, I check if any filter has a value different than 0 and also re-check in the where 
// clause if I need to filter by that specific item

if (complaintId || statusId)
{
    query = query.Where(x => (
                // This returns perfectly                   
                (complaintId && x.Id.Equals(complaintId))      

                // This is where I'm totally stuck with no clue on how to search for the last Complaint Status equal to the designated statusId

                || (statusId && ((x.Complaint.GroupBy(c => c.id)
                                    .Select(co => co.OrderByDescending(co => co.InsertDate).FirstOrDefault()) )
                                    .Any(s => s.StatusId.Equals(statusId))
                                )
                    )
}

// Finally I fire the DB request 
var response = query.ToListAsync();

EF is complaining that I cannot perform and orderby inside a where

One possible solution I found around is to use a delegate inside the where clause Subquery in Where Clause of LINQ statement

Is there any versed EF developer that can help achieve this?

Cheers,

score 0 · Answer 1 · answered Aug 14 '20 at 20:48

I just come across a solution for this using the suggestion from a colleague.

Adding a column in the Table ComplaintStatus to mark if the status is the last one. The new column name is Active of type bit and default value 1

Each time a status change, 1st I search for all the previous status and change the Active column to 0. I also stamp the dateTime when this happen. 2nd I insert the new status relation and as the default of the column is 1 this will be the only state that has the Active Mark.

This is the query to find the last state of a complaint:

var query = dbContext.Complaint                         // To get the Complaints table 
                     .Include(c => c.ComplaintStatus)   // To get the ComplaintStatus table
                     .Include(s => d.ComplaintStatus.Select(st => st.Status))       // To get the Status table
                     .AsQueryable();

var complaintId = 0;
var statusId = 0; 

// As per my research we need to check if the filters variables has values or return all 
// if we do not have filters in place.
// For this, I check if any filter has a value different than 0 and also re-check in the where 
// clause if I need to filter by that specific item

if (complaintId || statusId)
{
    query = query.Where(x => (
                // This returns perfectly                   
                (complaintId && x.Id.Equals(complaintId))      

                // This is where I'm totally stuck with no clue on how to search for the last Complaint Status equal to the designated statusId

                || (statusId && x.Status.Any(y => (y.Status == statusId && y.Active == true)))
}

// Finally I fire the DB request 
var response = query.ToListAsync();

Please, comment if you know how to perform a select + orderBy inside a dynamic where clause as that will be super helpfull on this type of intricate search

cheer

score 0 · Answer 2 · answered Aug 27 '20 at 04:00

Well, well... just by luck I land on the solution of this query ... Hope this help others stuck at same situation.

To find the last record of the intermediate table we can use the MAX verb and compare agains dates as follows:

if (complaintId || statusId)
{
    query = query.Where(x => (
                // This returns perfectly                   
                (complaintId && x.Id.Equals(complaintId))      

                // This is where I'm totally stuck with no clue on how to search for the last Complaint Status equal to the designated statusId

                || (statusId && ((x.Complaint.Any(y => (y.InsertDate == x.ComplaintStatus.Max(f => f.InsertDate))))
                                )
                    )
                ))
}

Entity Framework ASP.NET MVC : find last record from related table in a many to many scenario

2 Answers2