CODEWITHSUNDEEP
c#.netlinq
Lahib
Lahib

Reputation: 1365

Using linq to find MIN(date) within linq query

I am using linq to extract data. This data contains a date and some other values. The thing i that these dates can occur more then once, because the dates can have the same value but a different timestamp. I want to extract the anonymous type with the earliest timestamp. How can i do this in linq ?

this is my code:

var result = (from a in UnitOfWork.ActivityLessonParticipantService.Query
                         .Where(a => a.ActivityLesson.Activity.Id == activityId)
                         .Where(a => a.ActivityLesson.From >= startDate && (a.ActivityLesson.To == startDate || a.ActivityLesson.To <= endDate)).OrderBy(d => d.ActivityLesson.From)
                          where !a.ActivityLesson.IsDeleted && !a.ActivityLesson.Activity.IsDeleted && a.Appeared == true
                          select new
                          {
                              CPR = a.User.UserName,
                              FullName = a.User.FullName,
                              ActivityFromDate = a.ActivityLesson.From,
                          }).OrderBy(c => c.CPR).ToList();

thanks

Upvotes: 0

Views: 119

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can GroupBy the Date property of DateTime and then order this group by DateTime and use First to pick only the first record/object:

var query = from a in UnitOfWork.ActivityLessonParticipantService.Query
             where a.ActivityLesson.Activity.Id == activityId
                && a.ActivityLesson.From >= startDate 
                && (a.ActivityLesson.To == startDate || a.ActivityLesson.To <= endDate)
                && !a.ActivityLesson.IsDeleted 
                && !a.ActivityLesson.Activity.IsDeleted 
                && a.Appeared
             select a;
var firstByDate = query.GroupBy(a => a.ActivityLesson.From.Date)
    .Select(grp => grp.OrderBy(a => a.ActivityLesson.From).First())
    .OrderBy(a => a.User.UserName)
    .Select(a => new
    {
        CPR = a.User.UserName,
        FullName = a.User.FullName,
        ActivityFromDate = a.ActivityLesson.From,
    }).ToList();

Due to LINQ's deferred execution this is actually a single query that gets executed at the final ToList. I'm mixing query and method syntax because i prefer method syntax when it comes to GroupBy but it's a matter of taste.

Upvotes: 4

Related Questions