Entity Framework query the same table for the sequence of steps in WHERE

Question

I have the following EF code-first class to track the arrival status of the person at the airport. In each checking the record will be inserted in this table. Following are the steps demonstrates the flow.

Landed -> FirstCheck Approved -> Clearance Approved -> Arrived
Landed -> FirstCheck Approved -> Clearance Approved -> Security Check -> Denied // This is the one I need to query
Landed -> FirstCheck Approved -> Clearance Approved -> Security Check -> Arrived

I need to find the denied records which has FirstCheck Approved and also SecurityCheck to track the reason.

public class ArrivalStatusLog
{
    public ArrivalStatusLog();
    public int ArrivalStatusLogId { get; set; }
    public int PersonNumber{ get; set; }
    public DateTime CreationDate { get; set; }
    public string Status { get; set; }
}

EF query

var denialPersons = await db.ArrivalStatusLogs
                            .Where(s => s.Status == "Denied") // Additional filter
                            .Select(x => x.PersonNumber)

How to do AND condition with the other records in the same table for the given person with the other statuses? In my case I need to find Denied followed by FirstCheck Approved and Security Check.

Answer 1

You can can use .Any() to find all records where another record exists for the same person where the status is denied:

var denialPersons= await db.ArrivalStatusLogs
          .Where(s => db.ArrivalStatusLogs.Any(d => d.PersonNumber == s.PersonNumber && d.Status == "Denied")) // Additional filter
          .Select(x=> x.PersonNumber);

I expect the .Select() is superfluous here, and in fact you would just want the original record, with the created date and state, but left it in just in case that is all you want.

The generated SQL would be something like:

SELECT  PersonNumber
FROM    ArrivalStatusLogs AS s
WHERE   EXISTS
        (   SELECT  1
            FROM    ArrivalStatusLogs AS d
            WHERE   d.PersonNumber = s.PersonNumber
            AND     d.Status = 'Denied'
        );

If you only want to select the first check record you want, then you would need a further filter, e.g.

var denialPersons= await db.ArrivalStatusLogs
          .Where(s => s.Status == "FirstCheck Approved" && 
                        db.ArrivalStatusLogs.Any(d => d.PersonNumber == s.PersonNumber 
                                    && d.Status == "Denied")) // Additional filter
          .Select(x=> x.PersonNumber);    

Which is equivalent to:

SELECT  PersonNumber
FROM    ArrivalStatusLogs AS s
WHERE   s.Status = 'FirstCheck Approved'
AND     EXISTS
        (   SELECT  1
            FROM    ArrivalStatusLogs AS d
            WHERE   d.PersonNumber = s.PersonNumber
            AND     d.Status = 'Denied'
        );