Max sequence from a view containing multiple record using Linq lambda

I've been at this for a while. I have a data set that has a reoccurring key and a sequence similar to this:

id    status     sequence

1     open       1

1     processing 2

2     open       1

2     processing 2

2     closed     3

a new row is added for each 'action' that happens, so the various ids can have variable sequences.开发者_开发知识库 I need to get the Max sequence number for each id, but I still need to return the complete record.

I want to end up with sequence 2 for id 1, and sequence 3 for id 2.

I can't seem to get this to work without selecting the distinct ids, then looping through the results, ordering the values and then adding the first item to another list, but that's so slow.

var ids = this.ObjectContext.TNTP_FILE_MONITORING.Select(i => i.FILE_EVENT_ID).Distinct();
List<TNTP_FILE_MONITORING> vals = new List<TNTP_FILE_MONITORING>();
            foreach (var item in items)
            {

                vals.Add(this.ObjectContext.TNTP_FILE_MONITORING.Where(mfe => ids.Contains(mfe.FILE_EVENT_ID)).OrderByDescending(mfe => mfe.FILE_EVENT_SEQ).First<TNTP_FILE_MONITORING>());
            }

There must be a better way!

Here's what worked for me:

var ts = new[] { new T(1,1), new T(1,2), new T(2,1), new T(2,2), new T(2,3) };
var q = 
    from t in ts 
    group t by t.ID into g 
    let max = g.Max(x => x.Seq)
    select g.FirstOrDefault(t1 => t1.Seq == max);

(Just need to apply that to your datatable, but the query stays about the same)

Note that with your current method, because you are iterating over all records, you also get all records from the datastore. By using a query like this, you allow for translation into a query against the datastore, which is not only faster, but also only returns only the results you need (assuming you are using Entity Framework or Linq2SQL).