Returning multiple records using first/earliest date
Multi tool use
Returning multiple records using first/earliest date
This is a simple concept but the LINQ/SQL solution has me stumped!
In the example data below, for each ID I need to retrieve one ISRC, taking the earliest dated one for each.
ISRC
The data is sorted by ID/ReleaseDate/ISRC, so I could read the first/top record for each ID.
ie ID 1 = GBMNA1600001, ID 2 = GBMNA1600002, ID 3 = GBMNA1600003, ID 20 = GBMNA1680058...
ID/ReleaseDate/ISRC
ID ProductID ISRC ReleaseDate
1 16 GBMNA1600001 2016-03-27 00:00:00.0000000
1 26 GBMNA1680038 2016-04-24 00:00:00.0000000
1 32 GBMNA1680057 2016-05-01 00:00:00.0000000
1 132 GBMNA1680482 2016-11-13 00:00:00.0000000
1 223 GBMNA1781107 2017-03-26 00:00:00.0000000
2 5 GBMNA1600002 2016-02-14 00:00:00.0000000
2 32 GBMNA1680049 2016-05-01 00:00:00.0000000
3 13 GBMNA1600003 2016-03-13 00:00:00.0000000
3 38 GBMNA1680095 2016-05-29 00:00:00.0000000
3 485 GBMNA1880099 2018-06-26 00:00:00.0000000
20 32 GBMNA1680058 2016-05-01 00:00:00.0000000
20 39 GBMNA1680084 2016-05-29 00:00:00.0000000
20 116 GBMNA1680399 2016-10-30 00:00:00.0000000
20 281 GBMNA1780886 2017-06-05 00:00:00.0000000
20 360 GBMNA1600028 2018-01-08 00:00:00.0000000
But I don't know where to start with the LINQ syntax, or even figure out the SQL it should create.
I'm using ASP.NET Core 2.0 with EF, code is C# and db is SQL server 2016.
It's in a table I reference using a dbcontext, so I'm starting with
var earliestISRCS = from i in _context.allISRCS
select i;
I've tried using MIN() for the date, or sorting the data and using .FirstOrDefault() or Take(1) but only manage to get one record back.
MIN()
.FirstOrDefault()
Take(1)
What I need is to return a set of data - the earliest ISRC per ID.
ISRC
ID
If there happens to be more than one ISRC for the same date then the sort order of ISRCS will additionally be used to determine the first one.
ISRC
ISRCS
As an aside, your
ID values are not unique.. that betrays the meaning of "ID" (for "identity").– Dai
Jun 30 at 7:48
ID
Group by, Min, problem solved
– TheGeneral
Jun 30 at 7:48
2 Answers
2
You can use GroupBy and Min methods. Something like this:
GroupBy
Min
var result = from d in _context.allISRCS
group d by d.ID into grouped
let min = grouped.Min(d => d.ReleaseDate)
select new {
ID = grouped.Key,
_context.allISRCS.FirstOrDefault(c => c.ReleaseDate == min)?.ISRC
};
This sample code was very useful to follow. I've not used the 'let' clause so it's great to learn something new which will be useful for future development. I just had to add another condition ...FirstOrDefault(c => c.ReleaseDate == min && c.ID == grouped.Key) to make sure I was getting data for the relevant track. Thanks!
– codePenny
Jun 30 at 9:45
try following :
var earliestISRCS = _context.allISRCS
.OrderByDescending(x => x.ReleaseDate)
.GroupBy(x => x.ID)
.Select(x => x.First())
.OrderBy(x => x.ID)
.ToList();
While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.
– Sebastian Hofmann
Jun 30 at 9:35
Thanks for alternative code. I just had to amend and use .OrderBy(x => x.ReleaseDate).ThenBy(x => x.ISRC) to ensure x.First() returned the correct data.
– codePenny
Jun 30 at 10:10
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Are the ISRC values unique? It's hard to tell from the sample data you posted.
– Dai
Jun 30 at 7:47