score:2
Accepted answer
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 :
score: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:
Source: stackoverflow.com
Related Articles
- linq Group By and take first and lats records
- How can I group by in LINQ and then take first from the group from a sequential set of checks
- Code First Entity Framework Linq Statement Returning Missing Records
- How to get first record in each group using Linq
- Linq Query Group By and Selecting First Items
- Selecting first 100 records using Linq
- Take the first five elements and the last five elements from an array by one query using LINQ
- LINQ syntax to take all rows except the first one?
- Linq - Group by first letter of name
- LINQ Source Code Available
- how to take 100 records from linq query based on a condition
- Group a collection and take first 5 from each group
- Linq with where clause in many-to-many EF Code First object
- Selecting first element of a group from LINQ search results
- LINQ to Entities, join two tables, then group and take sums of columns from both tables
- How to group records and retrieve only first group with top N records
- Group by a list of words by their first letter in LINQ
- LINQ In Clause With Group By And TOP N Records Within Each Group
- C# LINQ get records from datatable group by hour
- How to filter a list with linq depends on counting a property at the same list and take a random group at least minimum total
- LINQ to SQL group by with take
- LINQ and EntityFramework: Getting first result after group by
- creating Linq to sqlite dbml from DbLinq source code
- EF Code First - Linq to Entities Union EqualityComparer
- how to use group by in linq C# and fetch the list of records
- Linq sub query when using a repository pattern with EF code first
- SQL subquery result in LINQ and Entity Framework Code First
- LINQ query to group records by month within a period
- Take unique records from text file using LINQ
- code first approach error: the specified type member 'yyyxx' is not supported in linq to entities
- C# Date Time Comparison Issue
- How do I convert a string array(having xml content) into an XML file?
- C# LINQ - Get pairs, when second value is null in all pairs that have the same first value
- Entity Framework Query Result as List of Complex Object
- EF Core: Group By Failure - Translation of 'Select' which contains grouping parameter without composition is not supported
- [LINQ]InsertOnSubmit NullReferenceException
- LINQ to SQL entity to object conversion
- Looking for elegant / efficient solution for integer keyed collection of models
- C# iteration and interpolation syntax
- Calculate Sum of column using groupby
- How to SELECT WHERE NOT EXIST using LINQ?
- Getting Count from Large IEnumerable
- Looking for an elegant LINQ query, alternative to SelectMany
- Date comparision
- How do I get related entity nested in linq to entities? Survey questions, subquestions, and options
- return one string with multiple column value linq
- Many to Many EF LINQ
- Moving specific elements after others but not to top or bottom
- Joins with Multiple Table and showing data from multiple tables in the View
- Fill List<T> with IEnumerable<T> when projecting a new type using LINQ
