LINQ Problem I want to get the last records of a table for each register-CodePudding

I have these list

var raw = new[] {    new { Title = "Sergio", Date= "18-12-2021"},
                     new { Title = "Sergio", Date= "18-10-2021"},
                     new { Title = "Sergio", Date= "18-11-2021"},
                     new { Title = "Jose", Date= "19-12-2021"},
                     new { Title = "Jose", Date= "18-12-2021"},
                     new { Title = "Jose", Date= "17-12-2021"},
                     new { Title = "Marco", Date= "16-12-2021"}
        };

I want a list that contains the lastest register for each "Title". For example these will be the output:

raw = {{ Title = "Sergio", Date= "18-12-2021"},
{ Title = "Jose", Date= "19-12-2021"},
{ Title = "Marco", Date= "16-12-2021"}
}

I tried using groupBy but it doesn't work

CodePudding user response：

You can find the last title using the date like this:

var last = raw.OrderBy(r => r.Date).Last().Title;

However, your data model has Date as a string using a format that doesn't sort properly.

For this code to work, you have the options:

Change the type of Date to DateOnly or DateTime
Format the date like YYYY-MM-DD
Convert the data type as suggested in @yossean-yamil's answer (less efficient than storing it in a naturally sortable form)

Data conversion alternative:

// You can use DateOnly instead of DateTime from .NET 6 on
var last = raw
    .Select(r => new 
        { 
            r.Title, 
            Date = DateTime.ParseExact(r.Date, "dd-MM-yyyy", null)
        })
    .OrderBy(r => r.Date).Last().Title;

Note, since your time resolution is to the day, the selected title is non-deterministic among all records sharing the same timestamp.

If you wish to get a list of all titles in such a group (note this suffers from the same issue with date sorting):

var last = raw
    .GroupBy(r => r.Date)
    .OrderBy(r => r.Key)
    .Last()
    .Select(r => r.Title)
    .ToList();

CodePudding user response：

You can still use the GroupBy method. It will return an enumeration of IGrouping object which can also be iterated. You need to group the array by Title, then order by Date in descending order for each group object.

record Register(string Title, DateOnly Date);
public static void Main()
{

    Register[] raw = new[]
    {
        new Register("Sergio", DateOnly.ParseExact("18-12-2021", "dd-MM-yyyy")),
        new Register( "Sergio", DateOnly.ParseExact( "18-10-2021", "dd-MM-yyyy")),
        new Register( "Sergio", DateOnly.ParseExact( "18-11-2021", "dd-MM-yyyy")),
        new Register( "Jose", DateOnly.ParseExact( "19-12-2021", "dd-MM-yyyy")),
        new Register( "Jose", DateOnly.ParseExact( "18-12-2021", "dd-MM-yyyy")),
        new Register( "Jose", DateOnly.ParseExact( "17-12-2021", "dd-MM-yyyy")),
        new Register( "Marco", DateOnly.ParseExact( "16-12-2021", "dd-MM-yyyy"))
    };

    IEnumerable<IGrouping<string, Register>> groups = raw.GroupBy(register => register.Title);

    foreach (IGrouping<string, Register> group in groups)
    {
        string title = group.Key;
        DateOnly? latestDate = group
            .OrderByDescending(register => register.Date)
            .Select(register => register.Date)
            .FirstOrDefault();

        Console.WriteLine("For title {0} the latest date is {1}", title, latestDate);
    }
}

Output:

For title Sergio the latest date is 12/18/2021
For title Jose the latest date is 12/19/2021
For title Marco the latest date is 12/16/2021

CodePudding user response：

A bit of a verbose answer but here I create a class and use that to parse out and then use a list of those which I then order and group a bit.

See it run here: https://dotnetfiddle.net/Lm12pX

This produces this output:

Hello World
18-10-2021 Group: Sergio January 18, 2021
17-12-2021 Group: Jose January 17, 2021
16-12-2021 Group: Marco January 16, 2021
Overall First one is: 16-12-2021 Marco January 16, 2021

Code:

using System;
using System.Globalization;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main()
    {
        Console.WriteLine("Hello World");
        List<Thing> things = new List<Thing>()
        {new Thing{Title = "Sergio", DateCreated = "18-12-2021"}, new Thing{Title = "Sergio", DateCreated = "18-10-2021"}, new Thing{Title = "Sergio", DateCreated = "18-11-2021"}, new Thing{Title = "Jose", DateCreated = "19-12-2021"}, new Thing{Title = "Jose", DateCreated = "18-12-2021"}, new Thing{Title = "Jose", DateCreated = "17-12-2021"}, new Thing{Title = "Marco", DateCreated = "16-12-2021"}};
        foreach (var thing in things.GroupBy(x => x.Title).Select(x => x.OrderBy(x => x.Timestamp).First()))
        {
            Console.WriteLine($"{thing.DateCreated} Group: {thing.Title} {thing.Timestamp.ToString("MMMM dd, yyyy")}");
        }

        var firstThing = things.OrderBy(t => t.Timestamp).First();
        Console.WriteLine($"Overall First one is: {firstThing.DateCreated} {firstThing.Title} "   firstThing.Timestamp.ToString("MMMM dd, yyyy"));
    }

    public class Thing
    {
        public Thing()
        {
        }

        public Thing(string title, string dateCreated)
        {
            Title = title;
            DateCreated = dateCreated;
        }

        public string Title { get; set; }

        public string DateCreated { get; set; }

        public DateTime Timestamp
        {
            get
            {
                CultureInfo provider = CultureInfo.InvariantCulture;
                var format = "dd-mm-yyyy";
                return DateTime.ParseExact(DateCreated, format, provider);
            }
        }
    }
}