CODEWITHSUNDEEP
c#linq
Deepika
Deepika

Reputation: 5

Get all records (from single table) by latest date using LINQ

I am trying to get all records (from single table) by latest date using LINQ but having some issue. For eg if table has 2 rows with latest date then I need to fetch these two rows on certain criteria. Please help

Here is my code. 

var q = (from n in Table
         where n.CustomerId == customerId && n.Isactive==true
         group n by new { n.CustomerId, n.ConsentId } into grp
         select new
         {
            // select all fields
            grp.Key.ConsentId,
            grp.Key.CustomerId,
            Date = grp.Max(t=>t.CreatedOn)
         }).ToList();

Upvotes: 0

Views: 1110

Answers (2)

Hemid Abbasov
Hemid Abbasov

Reputation: 175

As I understood, you need all records from Table, that belongs to active(IsActive = true) Customer with certain Id (CustomerId = <id>), and that records must be the latest (max(CreatedOn)). I didn't understand what is the ConsentId and do you realy need it. You already have done the half of solution, you got the max(CreatedOn) value for this customer. Now, you just need select the rows from Table for this Customer and CreatedOn = founded max

var q1 = from n in Table
            join max in (from n1 in Table
                        where n1.CustomerId == customerId && n1.Isactive==true
                        group n1 by new { n1.CustomerId, n1.ConsentId }
                        into grp
                        select new { grp.Key.CustomerId, grp.Key.ConsentId, MaxCreatedOnDate = grp.Max(r => r.CreatedOn) }) 
            on new { CustomerId = n.CustomerId, ConsentId = n.ConsentId, date = n.CreatedOn } equals new { CustomerId = max.CustomerId, ConsentId = max.ConsentId, date = max.MaxCreatedOnDate }
            select n;

UPD. The inner query (it is your query) can give more than 1 row in case when there is more than 1 groups (CustomerId, ConsentId) in Table. If you need groups only by CustomerId, then delete all ConsentId appearances in query.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460138

You have to split the CustomerId+ConsentId-group into a sub-group(by CreatedOn):

var q = Table
    .Where(x => x.CustomerId == customerId && x.Isactive)
    .GroupBy(x => new { x.CustomerId, x.ConsentId })
    .SelectMany(g => g
        .GroupBy(x => x.CreatedOn.Date).OrderByDescending(x => x.Key).First()
        .Select(x => new
        {
            g.Key.ConsentId,
            g.Key.CustomerId,
            Date = x.CreatedOn // this is the max-date per group
        }));

Upvotes: 1

Related Questions