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

Question

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();

What is the certain criteria? What is wrong with your query? You also mention "some issues" but then only show one. — Tim Schmelter, Dec 19 '17 at 09:38
Your question is really unclear. The description doesn't explain what you mean by "on certain criteria" and then you've given code but not explained what the problem is. Don't get me wrong - it's *great* that you've shown us what you've done so far, and I want to encourage that - but you need to go a few steps further to describe what you're trying to do and how your current code falls short. — Jon Skeet, Dec 19 '17 at 09:39
Sorry for any inconvenience caused. Criteria :Get all last date records where user is '1' and isactive='true' Above query is giving my records of two different dates — Deepika, Dec 19 '17 at 09:42
@Deepika: If the query gives you records of different dates you might want to include the date in the group. — Tim Schmelter, Dec 19 '17 at 09:43
@Deepika check this ans here they are firstly sorting the list and getting the result alter https://stackoverflow.com/questions/4913071/linq-select-records-closest-to-date?rq=1 — Arijit Mukherjee, Dec 19 '17 at 09:47
@ArijitMukherjee yes by latest date, but I want all records for that particular user by latest date — Deepika, Dec 19 '17 at 09:47

score 1 · Answer 1 · answered Dec 19 '17 at 09:56

1

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
        }));

answered Dec 19 '17 at 09:56

Tim Schmelter

450,073
74
686
939

Is it true ? ".GroupBy(x => x.CreatedOn.Date)" – lucky Dec 19 '17 at 10:48
@Rainman: what means _"is it true"_? You ask if that's correct? I hope so. I think she wants to group the group by the date-property. Since a `DateTime` contains also the time i have removed it by using [`DateTime.Date`](https://msdn.microsoft.com/en-us/library/system.datetime.date(v=vs.110).aspx). If the LINQ provider doesn't support this she either has to use [`EntityFunctions.TruncateTime`](http://msdn.microsoft.com/en-us/library/dd395596.aspx) or omit it(f.e. if `CreatedOn` is a `date` field in the database). – Tim Schmelter Dec 19 '17 at 10:51
I mean, you can't use the ".Date" for IQueryable. – lucky Dec 19 '17 at 10:53
@Deepika: why not? – Tim Schmelter Dec 19 '17 at 10:54

Hemid Abbasov · Answer 2 · 2017-12-19T11:45:08.440

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.

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

2 Answers2