1

I'm having a hard time creating a LINQ-to-Entities query that would fulfill my requirement.

I have two tables: Booking and ProcessStatusLog.

Booking table:

  • PNNumber (PK)
  • AccountName

ProcessStatusLog table:

  • ID (PK)
  • PNNumber (FK)
  • InsuranceCode
  • Status
  • UpdatedOn

Here is the sample data for these tables:

Booking table

| PNNumber | Account Name   |
+----------+----------------+
| 11111    | Boston Celtics |
| 22222    | Miami Heat     |
| 33333    | LA Lakers      |

ProcessStatusLog table:

| ID   | PNNumber  | InsuranceCode | Status       | UpdatedOn   |
+------+-----------+---------------+--------------+-------------+
| 1    | 11111     | null          | NEW          | 02/22/2020  |
| 2    | 11111     | FIRE          | FOR REVIEW   | 02/23/2020  |
| 3    | 22222     | null          | NEW          | 02/24/2020  |
| 4    | 22222     | MORTGAGE      | FOR REVIEW   | 02/25/2020  |
| 5    | 22222     | MORTGAGE      | CORRECTION   | 02/26/2020  |
| 6    | 22222     | FIRE          | FOR REVIEW   | 02/27/2020  |
| 7    | 33333     | null          | NEW          | 02/28/2020  |
| 8    | 22222     | FIRE          | APPROVED     | 02/29/2020  |

Now, I want to get a list of bookings per latest status.

For example: if I want to filter the bookings where the latest status is "CORRECTION", I will get the booking with PNNumber 22222.

If searching for "FOR REVIEW", I will just get the booking with PNNumber 11111.

If searching for "NEW", I will just get the booking with PNNumber 33333.

How can I write the EF query for this?

Thanks.

Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
kuriboh
  • 13
  • 4
  • Could you post some queries that you have tried and Does your `ProcessStatusLog` have a Navigation key to `Booking`? – vikscool Feb 24 '20 at 04:29
  • Wouldn't `FOR REVIEW` = `22222` ? if not, you will need to explain why – TheGeneral Feb 24 '20 at 04:35
  • @MichaelRandall 22222 will not be included in the desired result for FOR REVIEW because I am querying for the latest status. The FOR REVIEW in 22222 status has already been "overwritten" by the CORRECTION and APPROVED status. – kuriboh Feb 24 '20 at 04:45
  • @vikscool Yes the `ProcessStatusLog` has a Navigation key to `Booking`. One booking can have many process status logs. – kuriboh Feb 24 '20 at 04:49

5 Answers5

1
context.ProcessStatusLog.where(x=>x.Status == "FOR REVIEW")
                        .OrderByDescending(x => x.UpdatedOn)
                        .Take(1);
Balagurunathan Marimuthu
  • 2,927
  • 4
  • 31
  • 44
Hamza Ali
  • 380
  • 5
  • 21
0

You can use Join() to achieve it.

var result = context.ProcessStatusLog.Join(context.Booking, s => s.PNNumber, b => b.PNNumber, (s, b) => new { booking = b, StatusLog = s).Where(BookingAndStatusLog => BookingAndStatusLog.StatusLog.Status == your_parameter)
                        .OrderByDescending(BookingAndStatusLog  => BookingAndStatusLog.StatusLog.UpdatedOn)
                        .Take(1);
Nguyễn Văn Phong
  • 13,506
  • 17
  • 39
  • 56
0

Try below Query.

context.ProcessStatusLog.where(x=>x.Status == "FOR REVIEW")
.OrderByDescending(x => x.UpdatedOn)
.ToList();
Joel Dharansingh
  • 181
  • 1
  • 1
  • 9
0

So far my understanding of your question, you can try-

var result= context.ProcessStatusLog.OrderByDescending(x => x.UpdatedOn)
                        .FirstOrDefault(x=>x.Status == "CORRECTION");

OR

var result= context.ProcessStatusLog.where(x=>x.Status == "CORRECTION")
                        .OrderByDescending(x => x.UpdatedOn)
                        .FirstOrDefault();

it's return a single object of your ProcessStatusLog class.

Ashiquzzaman
  • 5,129
  • 3
  • 27
  • 38
-1
using (var ctx = new SchoolDBEntities())
{
var student = ctx.Students
                .SqlQuery("Select top1 from ProcessStatusLog where Status like '%"+@APPROVED+"%' order by UpdatedOn", new SqlParameter("@APPROVE", yourinputparameter))
                .FirstOrDefault();
}
Praveen
  • 11
  • 1