LINQ Query to select latest record

Question

Our table structure looks like below:

appointmentID    

1abc   --------------->1              
1abc (latest)  ------------>2            
1hjt               
990aa             
990aa              
990aa (latest

DateTime start = DateTime.Now.AddDays(0);
DateTime end = DateTime.Now.AddDays(7));

List<JobCustomers> appointments = objectContext.JobCustomers.Where(
                    a => a.StartTime >= start && a.EndTime <= end && !string.IsNullOrEmpty(a.AppointmentId)).ToList();   

foreach (JobCustomers appointmentItem in appointments) {
    // HERE I WANT TO WRITE SOME CODE
    -- WHEN WE ARE INSERTING NEW RECORD OF A SAME ID EX "1abc" IT MUST 
    COMPARE WITH LATEST RECORD "-----2> 
}

My requirement: if there are morethan 1 rows with same id then I need to bring latest record by appointment id something like below

List<JobCustomers> appointments = objectContext.JobCustomers.Where(
                    a => a.StartTime >= start && a.EndTime <= end && !string.IsNullOrEmpty(a.AppointmentId).**take(0**)).ToList();

In simple words : Using LINQ when we are inserting a new record with same id we need to compare with a last inserted record

You need another field to indicate what the "latest" record is. As you're showing it now, there is none. Once you have that, it's a simple order by/grouping with max. — casperOne, Dec 30 '12 at 02:15

score 0 · Answer 1 · answered Dec 30 '12 at 02:44

Neither LINQ nor SQL don't know in which order you insert data, you need to have an extra field to save this order (could be a date, or an autonumeric). Once you have that, you will be able to use:

YourList.OrderBy(i => i.Id).ThenByDescending(i => i.YourColumnToSaveOrder)

Risky Martin · Answer 2 · 2012-12-31T16:10:21.467

0

var latestAppts = appointments.GroupBy(x => x.AppointmentId)
                              .Select(g => g.Last());

edited Dec 31 '12 at 16:10

answered Dec 30 '12 at 15:17

Risky Martin

2,491
2
15
16

score 0 · Answer 3 · answered Dec 31 '12 at 21:41

If I'm reading this correctly, you're trying to specifically grab the latest record with the same ID that's being brought That would simply need to be done with LINQ's LastOrDefault extension method:

//Variable 'yourNewRecord' is what you're about to enter into the database
var latestAppt = objectContext.JobCustomers
                              .LastOrDefault(a => a.StartTime >= start && 
                                                  a.EndTime <= end &&
                                                  a.AppointmentID.Equals(yourNewRecord.AppointmentID)

EDIT: For more information about LastOrDefault, please look at the MSDN link here.

Whilst likely to work in most instances, I seem to remember that unordered linq queries are not guaranteed to be deterministic. Last/First calls are, I believe, best used on previously ordered collections. Previous question/answer http://stackoverflow.com/a/12641528/1161844 discusses it further. — Jim Patterson, Jan 01 '13 at 19:59

LINQ Query to select latest record

3 Answers3