Perform condition on last record of table and get id of that record

Question

I want to perform condition on last record of my below model :

 public partial class TestPart
    {
       public int Id { get; set; }
       public int TestId { get; set; }
       public int Status { get; set; }
       public virtual ICollection<Job> Jobs { get; set; }
    }

Query :

var query = context.TestPart.OrderByDescending(tp=>tp.Id)
                                                .Take(1)
                                                .Where(tp => tp.TestId == 100 &&
                                                 tp.Status == 1 &&
                                                 tp.Jobs.All(j => j.Type == "Recurring")

Here I want to get Id of TestPart whose status = 1 and all jobs are recurring but this should only consider checking last record of test part

But I am unable to select Id of last TestPart in above query.

Update :

Id    TestId       Status     
1     100          1
2     100          2
3     100          0
4     100          1
5     101          1

so here I want to filter out data based on TestId and then select last record for that specific TEST and then check out whether all job types are recurring for that last selected TestPart id i.e in above case TestPartId=4.

Answer 1

The explanation is a bit fragmented. In order to make sure that I'm answering to the right problem, these are my assumptions:

• One Test has many TestPart children.
• You want the last TestPart of a given test, not just the last entry of the table (regardless of test id).
• You're trying to ascertain if this last item fits the criteria, thus making the end result of your code a boolean value.

---

You need to split the data retrieval and data validation steps here.

When you merge them, you get different results. You ask for the last item that fits the criteria. This means that in a list of 10 items (numbered 1 through 10 chronologically) you might end up getting item 8 if it fits the criteria and 9 and 10 do not fit the criteria.

From your description, I surmise that's not what you want. You want to take item 10 (regardless of whether it fits the criteria, and only then check if this item fits the criteria or not.

Think of it this way:

• I want the last person named John who entered this building.
• I want to see if the last person who entered the building is named John.

Your code is trying to do the first. But what you really want to do is the second.

The correct version of your code:

//Get the last testpart of the test.
TestPart item = context.TestPart
                       .Include(tp => tp.Jobs)  //possibly optional dependent on lazy/eager loading.
                       .OrderByDescending(tp=>tp.Id)
                       .First(tp => tp.TestId == 100);

//Check if this item fits the criteria
bool isValid = 
          item.Status == 1 
          && item.Jobs.All(j => j.Type == "Recurring");

isValid contains your answer.

---

Edit - just for completeness

There are ways to merge this into one query, but this makes the code easily prone to misinterpretation.

bool isLastItemValid = context.TestPart
                          .Where(tp => tp.TestId == 100)
                          .OrderByDescending(tp => tp.Id)
                          .Take(1)
                          .Any(tp =>
                                   tp.Status == 1 
                                   && tp.Jobs.All(j => j.Type == "Recurring");

This gives you the same result. It relies on the "trick" that calling Any() on a list with only one item really just evaluates the one item.

While technically correct, I find this version unnecessarily complicated, less readable, and more prone to developer misinterpretation.

Answer 2

I think the appropriate thing to do is break it into steps. I do love a big LINQ statement like the next guy, but only when it elegantly represents the required logic. In this case you're to get a record, check its status, and return its ID, so why not express that in ROC?

var lastPart = context.TestPart.OrderByDescending(tp=>tp.Id)
                      .First();

bool match =  (lastPart.TestId == 100 &&
               lastPart.Status == 1 &&
               lastPart.Jobs.All( j => j.Type == "Recurring"));

if (match) return lastPart.Id;

Relevant: Writing ROC (Really Obvious Code).

Answer 3

Replace .Take(1).Where() with FirstOrDefault()

TestPart item = context.TestPart.OrderByDescending(tp => tp.Id)
                       .FirstOrDefault(tp => tp.TestId == 100 &&
                                             tp.Status == 1 &&
                                             tp.Jobs.All(j => j.Type == "Recurring");
int result = item.Id;