Linq Select First Active Record in a Set

Question

I am trying to return the most recent revision of a document that is not in a cancelled status. However, if all the revision of the document are cancelled, it should still return the most revision in the cancelled status.

Data looks something like: ID | Name | Rev | Status 00 | Manual | 000 | Active 00 | Manual | 001 | Active 00 | Manual | 002 | Active 00 | Manual | 003 | Active 00 | Manual | 004 | Active //return this one

ID | Name | Rev | Status 01 | Manual2 | 000 | Active 01 | Manual2 | 001 | Active 01 | Manual2 | 002 | Active 01 | Manual2 | 003 | Active //return this one 01 | Manual2 | 004 | Cancel

ID | Name | Rev | Status 02 | Manual3 | 000 | Cancel 02 | Manual3 | 001 | Cancel 02 | Manual3 | 002 | Cancel 02 | Manual3 | 003 | Cancel 02 | Manual3 | 004 | Cancel //return this one

I can group and sort the records easily. I can also filter our records with a cancelled status, but in the case of the 3rd data set, all documents are in cancelled status and I get an exception.

List<Records> r = records
    .GroupBy(a => a.ID)
    .Select(b => new Record
            {
                ID = b.Key,
                Name = b.First().Name,
                Rev = b.OrderByDescending(o => o.Rev)
                       .First(x=> x.status != "Cancel").Rev
        }).ToList();

Answer 1

You're almost there with what you have, you can use ThenBy after an OrderBy to add subsequent ordering:

List<Records> r = records
    .GroupBy(a => a.ID)
    .Select(b => new Record
            {
                ID = b.Key,
                Name = b.First().Name,
                Rev = b.OrderBy(o => o.status == "Cancel")
                       .ThenByDescending(o => o.Rev)
                       .First().Rev
        }).ToList();

NOTE: In this example I've ordered on status == "Cancel" as false < true.

---

EDIT

Based on the comments section requirement for extra statuses: you could create a function to convert the status into a numeric rank:

public int GetStatusRank(string status)
{
    switch (status)
    {
        case "active":
            return 0;
        case "endorsed"
            return 1;
        case "cancelled":
            return 2;
        //etc...
        default:
            return int.MaxValue;
    }
}

You could then use this in your OrderBy:

...b.OrderBy(o => GetStatusRank(o.status))

Answer 2

The following Query first orders the Items by Status and Rev. Then the group by picks every ID once. distinctRecord.First() returns the topmost item of that group, hence the ordering.

var query =
    from record in records
    orderby record.Status ascending, // Active before Cancel ('A' is alphabetically before 'C')
        record.Rev descending // highest revision first
    group record by record.ID
    into distinctRecord
    select distinctRecord.First();
var r = query.ToList();

The cool thing about this is, that you won't need to create a new instance of Record. Instead it brings back the actual Object from your Collection.

---

As you require to create new Instances of Record and not use references to the ones in the collection. You can do It like I've also explained in my comment:

var query =
    from record in records
    orderby record.Status ascending, // Active before Cancel ('A' is alphabetically before 'C')
        record.Rev descending // highest revision first
    group record by record.ID
    into distinctRecord
    select new Record { 
        ID = distinctRecord.Key,
        Name = distinctRecord.First().Name,
        Rev = distinctRecord.First().Rev,
        Status = distinctRecord.First().Status
    };
var r = query.ToList();

Answer 3

I would use reducing based on Aggregate-method:

    var resul = list
        .GroupBy(
            k => k.ID,
            (key, groups) =>
                groups.Aggregate((accumulatedItem, item) =>
                {
                    if (accumulatedItem.Status == item.Status)
                    {
                        return string.Compare(accumulatedItem.Rev, item.Rev) >= 0
                            ? accumulatedItem
                            : item;
                    }

                    return accumulatedItem.Status == "Active"
                        ? accumulatedItem
                        : item;
                })
            )
        .ToArray();

    var list = new[] {
        new Record("00", "Manual", "000","Active"),
        new Record("00", "Manual", "001","Active"),
        new Record("00", "Manual", "002","Active"),
        new Record("00", "Manual", "003","Active"),
        new Record("00", "Manual", "004","Active"),
        new Record("01", "Manual2", "000", "Active"),
        new Record("01", "Manual2", "001", "Active"),
        new Record("01", "Manual2", "002", "Active"),
        new Record("01", "Manual2", "003", "Active"),
        new Record("01", "Manual2", "004", "Cancel"),
        new Record("02", "Manual3", "000", "Cancel"),
        new Record("02", "Manual3", "001", "Cancel"),
        new Record("02", "Manual3", "002", "Cancel"),
        new Record("02", "Manual3", "003", "Cancel"),
        new Record("02", "Manual3", "004", "Cancel")
        };

public class Record
{
    public Record(string id, string name, string rev, string status)
    {
        ID = id;
        Name = name;
        Rev = rev;
        Status = status;
    }
    public string ID { get; set; }
    public string Name { get; set; }
    public string Rev { get; set; }
    public string Status { get; set; }
}

Answer 4

You can try this:

• Create an anonymous object with the Revision object (Active or Cancel)

• Create your colllection of Record using the Rev property.

  var r = records.GroupBy(x => new {x.Id, x.Name})
                 .Select(x => new {
                     ID = x.Key.ID,
                     Name = x.Key.Name,
                     RevObj = x.OrderBy(y => y.Rev).LastOrDefault(y => y.Status != "Cancel") ??
                              x.OrderBy(y => y.Rev).Last(y => y.Status == "Cancel")
                 })
                 .ToArray()
                 .Select(x => new Record() {
                     ID = x.ID,
                     Name = x.Name,
                     Rev = x.RevObj.Rev
                 })
                 .ToList();
  

Answer 5

List<Records> r = records
                .OrderByDescending(x => x.Status != "Cancel")
                .ThenBy(r => r.Rev).ToList();

after (r=>r.Rev) you can Sorting with anything else what you need. Just use .ThenBy(...)

Answer 6

try this:

.First(x=> x.status != "Cancel" || x.status == "Cancel")