0
487
Viewslinq Group By and take first and lats records
I am dealing with probably simple yet difficult problem for me. I am taking photos of parked cars and save them by car's plate and timestamp it. Same car could be photographed several times during the day.
Sample Data
| Plate | Brand | Model | InsertDate |
|---|---|---|---|
| 99AA111 | Tesla | S | 2022-01-17 04:00:00 |
| 99AA111 | Tesla | S | 2022-01-17 04:30:00 |
| 99AA111 | Tesla | S | 2022-01-17 05:00:00 |
| 59TA3312 | Nissan | Skyline | 2022-01-17 04:00:00 |
| 59TA3312 | Nissan | Skyline | 2022-01-17 04:30:00 |
| 129EA512 | Subaru | Impreza | 2022-01-17 03:30:00 |
What i am trying to achieve is;
| Plate | Brand | Model | FirstPhotoDate | SecondPhotoDate |
|---|---|---|---|---|
| 99AA111 | Tesla | S | 2022-01-17 04:00:00 | 2022-01-17 04:30:00 |
| 99AA111 | Tesla | S | 2022-01-17 05:00:00 | - |
| 59TA3312 | Nissan | Skyline | 2022-01-17 04:00:00 | 2022-01-17 04:30:00 |
| 129EA512 | Subaru | Impreza | 2022-01-17 03:30:00 | - |
I have came up with;
var groupedResult = resultList.GroupBy(f => f.Plate).Select(f => new ResultListView
{
Plate = f.Key,
FirstDate = f.Min(f => f.InsertDate),
SecondDate = f.Max(f => f.InsertDate),
Brand = f.FirstOrDefault().Brand,
Model = f.FirstOrDefault().Model,
TimeDifference = (f.Max(f => f.InsertDate) - f.Min(f => f.InsertDate)).TotalMinutes
});
But as it's obvious in the code, it only gives me the first and the last record but not as I expected. I am trying to group by plates and if same plate goes more than once, match it with the next one. if only there is one photo or three photo, second date should be null.
Of course different logic's could be applied but I believe this is more clear way.
I thougt that looping inside grouped result. Take first and the second and skip two. But this is not I am looking for.
Thank you for your help.
2 answers
Answer question0
Well, having (let use named tuple to demo):
var resultList = new (string Plate, string Brand, string Model, DateTime InsertDate)[] {
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 04,00,00)),
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 04,30,00)),
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 05,00,00)),
("59TA3312", "Nissan", "Skyline", new DateTime(2022,01,17, 04,00,00)),
("59TA3312", "Nissan", "Skyline", new DateTime(2022,01,17, 04,30,00)),
("129EA512", "Subaru", "Impreza", new DateTime(2022,01,17, 03,30,00)),
};
You can GroupBy twice:
var groupedResult = resultList
.GroupBy(item => item.Plate)
.SelectMany(group => group
.OrderBy(item => item.InsertDate)
.Select((item, index) => (item: item, index: index / 2))
.GroupBy(pair => pair.index)
.Select(g => (
Plate: g.First().item.Plate,
Brand: g.First().item.Brand,
Model: g.First().item.Model,
FirstPhotoDate: g.First().item.InsertDate,
SecondPhotoDate: (g.Count() == 1 ? null : (DateTime?)(g.Last().item.InsertDate))
))
);
Let's have a look:
string report = string.Join(Environment.NewLine, groupedResult
.Select(r => $"{r.Plate,-8} : {r.Brand,-6} : {r.Model,-7} : {r.FirstPhotoDate} : {r.SecondPhotoDate}"));
Console.Write(report);
Outcome:
99AA111 : Tesla : S : 17.01.2022 4:00:00 : 17.01.2022 4:30:00
99AA111 : Tesla : S : 17.01.2022 5:00:00 :
59TA3312 : Nissan : Skyline : 17.01.2022 4:00:00 : 17.01.2022 4:30:00
129EA512 : Subaru : Impreza : 17.01.2022 3:30:00 :
0
Here's the cleanest approach I can think of:
var resultList = new (string Plate, string Brand, string Model, DateTime InsertDate)[]
{
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 04,00,00)),
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 04,30,00)),
("99AA111", "Tesla", "S", new DateTime(2022,01,17, 05,00,00)),
("59TA3312", "Nissan", "Skyline", new DateTime(2022,01,17, 04,00,00)),
("59TA3312", "Nissan", "Skyline", new DateTime(2022,01,17, 04,30,00)),
("129EA512", "Subaru", "Impreza", new DateTime(2022,01,17, 03,30,00)),
};
var query =
from r in resultList
group (DateTime?)r.InsertDate by new { r.Plate, r.Brand, r.Model } into grs
from bgrs in grs.OrderBy(x => x).Buffer(2)
select new
{
grs.Key.Plate,
grs.Key.Brand,
grs.Key.Model,
FirstPhotoDate = bgrs.First(),
SecondPhotoDate = bgrs.Skip(1).Any() ? bgrs.Last() : null,
};
I used the Buffer operator from Microsoft's System.Interactive NuGet.
That gives me:
