jeudi 25 juin 2015

Linq Group By, Skip 1 where the skipped 1 was the most recent

I have a data set of objects that i have stored in the events List (the variables have all been declared earlier at class level):

    [SetUp]
    public void Setup()
    {
        eventLogObj = new EventLogObj();

        event1 = new EventLogObj() { RecordId = 1, TableKey = "PERSON_CODE=1", Status = "S", EventTime = Convert.ToDateTime("2013-07-13 14:00:00") };
        event2 = new EventLogObj() { RecordId = 2, TableKey = "PERSON_CODE=2", Status = "S", EventTime = Convert.ToDateTime("2013-07-15 13:00:00") };
        event3 = new EventLogObj() { RecordId = 3, TableKey = "PERSON_CODE=3", Status = "S", EventTime = Convert.ToDateTime("2013-07-15 13:00:00") };
        event4 = new EventLogObj() { RecordId = 4, TableKey = "PERSON_CODE=2", Status = "S", EventTime = Convert.ToDateTime("2013-07-15 14:00:00") };
        event5 = new EventLogObj() { RecordId = 5, TableKey = "PERSON_CODE=1", Status = "S", EventTime = Convert.ToDateTime("2013-07-15 13:00:00") };

        events = new List<EventLogObj>() { event1, event2, event3, event4, event5 };
    }

I was initially just extracting the duplicates - which worked (below)

    [Test]
    public void StoreOnlyDuplicateDetailsFromRowsIntoCollection()
    {
        var duplicates = events.GroupBy(s => s.TableKey)
            .SelectMany(grp => grp.Skip(1)).ToList();

        Assert.AreEqual(2, duplicates.Count);
    }

However, now I want to extract duplicates with the lowest dates and I'm not quite sure how to adjust the linq query i setup.

Here is what I have done so far but it fails.

If you are wondering what duplicates2 is, it a failed attempt to implement this: LINQ: Group by aggregate but still get information from the most recent row?

    [Test]
    public void pickDuplicateEventWithLeastDate()
    {
        var duplicates = events
            //.OrderBy(e => e.EventTime)
            .GroupBy(s => s.TableKey)
            .SelectMany(grp => grp.Skip(1))
            .ToList();

        var duplicates2 = from res in events
                          group res by res.TableKey into g
                          select new
                          {
                              Count = g.Count(),
                              MemberID = g.Key,
                              MostRecent = g.OrderByDescending(x => x.EventTime)
                              .First()
                          };

        Assert.AreEqual(2, duplicates.Count);
        var e1 = duplicates[0];
        var e2 = duplicates[1];

        Assert.AreEqual(e1.EventTime, Convert.ToDateTime("2013-07-15 13:00:00"));
        Assert.AreEqual(e2.EventTime, Convert.ToDateTime("2013-07-15 13:00:00"));
    }

If you want to try it without having to setup interfaces, classes, etc in Visual Studio, see here http://ift.tt/1Kcv9aO and fiddle about. Basically if the tests pass, you should get nothing in the 'console window'.

Aucun commentaire:

Enregistrer un commentaire