CODEWITHSUNDEEP
c#linq
WonderWorker
WonderWorker

Reputation: 9062

Grouping earliest entry for each day using Linq

Trying to get my head around Linq, and at the same time keep track of the time I log on in the morning, which should be the time I get into the office thereabouts.

My code so far is:

EventLog SecurityLog = new EventLog("Security");

var AccountLoggedOnEntries = SecurityLog.Entries.Cast<EventLogEntry>()
    .Where(x => x.InstanceId == 4624)
    .Select(x => new
    {
        DateGenerated = x.TimeGenerated.ToShortDateString()
        ,
        TimeGenerated = x.TimeGenerated.ToShortTimeString()
        ,
        x.Message
    })
    .ToList();

DgvLogSummary.DataSource = AccountLoggedOnEntries;

DgvLogSummary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;

I want to filter the results so that I only have one entry for each day, which is the earliest time.

In SQL I would normally take the Message of the earliest entry and then group by all fields.

How do I perform a similar query in Linq?

Upvotes: 2

Views: 641

Answers (3)

jdweng
jdweng

Reputation: 34421

Try this : 

 var AccountLoggedOnEntries = log.Entries.Cast<EventLogEntry>()
                .Where(x => x.InstanceId == 4624)
                .GroupBy(x => x.TimeGenerated.Date)
                .Select(days => days.OrderBy(time => time.TimeGenerated).FirstOrDefault())
                .Select(x => new
                {
                    DateGenerated = x.TimeGenerated.ToShortDateString()
                    ,
                    TimeGenerated = x.TimeGenerated.ToShortTimeString()
                    ,
                    x.Message
                })
                .ToList();

Upvotes: -1

Jamiec
Jamiec

Reputation: 136114

You could GroupBy the date and then select the minimum time

var AccountLoggedOnEntries = log.Entries.Cast<EventLogEntry>()
    .Where(x => x.InstanceId == 4624)
    .GroupBy(x => x.TimeGenerated.Date)
    .Select(x => new {
        DateGenerated = x.Key
        , TimeGenerated = x.Min(y => y.TimeGenerated).ToShortTimeString()
    })
    .ToList();

Getting the appropriate Message is a little more tricky. One easy option is to use x.First().Message in the above Select projection.

Upvotes: -1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

In LINQ you would group by, sort each group, and pick the first item:

var AccountLoggedOnEntries = log.Entries.Cast<EventLogEntry>()
    .Where(x => x.InstanceId == 4624)
    .GroupBy(x => x.TimeGenerated.Date)
    .Select(g => g.OrderBy(x => x.TimeGenerated).First())
    .Select(x => new {
        DateGenerated = x.TimeGenerated.ToShortDateString()
    ,   TimeGenerated = x.TimeGenerated.ToShortTimeString()
    ,   x.Message
    })
    .ToList();

Upvotes: 6

Related Questions