Get latest record and group with highest date - LINQ

Question

I have table Billing as below

AccoundID | Group | DateOfBill
1234      | A     | 2017-07-12
1234      | B     | 2017-07-16
1234      | C     | 2017-07-31
1235      | A     | 2017-07-31
1236      | B     | 2017-07-31

As you see, AccountID 1234 have made 3 transaction on July 2017. So I need a list where the AccountID 1234 must be in Group C because that's is latest date on that transaction.

Here is my code snippet

var LatestAccount = from n in Billing
                    where (n.Group == "A" || n.Group == "B" || n.Group == "C")
                    group n by new { n.AccountID, n.Group } into g
                    select new { 
                        AccountId = g.Key.AccountID, 
                        Group = g.Key.Group , 
                        DateOfBill = g.Max(t => t.DateOfBill) 
                    };

But the result is wrong. How to do in LINQ?

Answer 1

class Program
{
    static void Main(string[] args)
    {
        List<Billing> Billings = new List<Billing>()
        {
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,12), Group = "A"

            },
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,16), Group = "B"

            },
            new Billing()
            {
                AccountID = 1234, DateOfBill = new DateTime(2017,07,31), Group = "C"

            },
            new Billing()
            {
                AccountID = 1235, DateOfBill = new DateTime(2017,07,31), Group = "A"

            },
            new Billing()
            {
                AccountID = 1236, DateOfBill = new DateTime(2017,07,31), Group = "B"

            }               
        };

        var LatestAccount = from n in Billings
                            where (n.Group == "A" || n.Group == "B" || n.Group == "C")
                            group n by new { n.AccountID } into g
                            select g.Where(d => d.DateOfBill == g.Max(m => m.DateOfBill)).Select(x => new { AccountId = g.Key.AccountID, Group = x.Group, DateOfBill = x.DateOfBill }).FirstOrDefault();

        foreach (var item in LatestAccount)
        {
            Console.WriteLine("AccountID: " + item.AccountId + "  Date of Bill: " + item.DateOfBill + "  Group: "+ item.Group);
        }
        Console.ReadLine();
    }
}
class  Billing
{
    public int AccountID { get; set; }
    public string Group { get; set; }
    public DateTime DateOfBill { get; set; }
}

Is below what you want? If you show me the output you want, I can modify my answer.