jeancallisti
Reputation: 1681
Linq with inner join and grouping
Sorry, this is the kind of question that will annoy the experts, but I've been searching for similar cases and I'm sure they exist, but I'm simply not good enough to recognize what applies to my scenario.
I have two tables, where there's a 1-n relation between MeasuresSet and Measure :
MeasuresSet {
Id : Guid
Date : DateTime
}
Measure {
Id : Guid
MeasuresSetId : Guid
MeasureCategory : string
Value : double
VehicleName: string
}
MeasuresSet is a group of measures that were performed on the same day, hence the date.
Each row in table Measure represents a specific measure that was performed on the vehicle. For example it could be have MeasureCategory "tyre pressure" and have a corresponding value in psi.
In other words : Each vehicle has many measures, of same or different categories, performed on the same day or on different days.
I want to be able to write a query that means "Give me all the measures that were performed for Vehicle 'Mike's car', but group on MeasureCategory so that I get only the most recent value of each MeasureCategory." The issue is that the date is stored in MeasuresSet, not Measure. Unfortunately I get lost in the join and the grouping.
I would have imagined that this would be the correct query, but the syntax is incorrect :
from MeasuresSet
join Measure
on MeasuresSet.Id equals Measure.MeasuresSetId //your typical inner join
where VehicleName == "Mike's car"
group Measure by Measure.MeasureCategory into g
select new ResultType {
MeasureCategory : g.Key,
Date: g.OrderByDescending(data => data.Date).First().Date,
Value: g.OrderByDescending(data => data.Date).First().Value,
};
Upvotes: 1
Views: 82
Answers (1)
anilcemsimsek
Reputation: 814
public class MeasuresSet
{
public string Id { get; set; } //: Guid
public DateTime Day { get; set; } //: DateTime
}
public class Measure
{
public string Id { get; set; }//: Guid
public string MeasuresSetId { get; set; }//: Guid
public string MeasureCategory { get; set; } //: string
public double Value { get; set; } //: double
public string VehicleName { get; set; }//: string
}
var measureSets = new List<MeasuresSet>() {
new MeasuresSet() { Daisy = DateTime.Today, Id = "GUID-0" },
new MeasuresSet() { Daisy = DateTime.Today.AddDays(1), Id = "GUID-1" },
new MeasuresSet() { Daisy = DateTime.Today.AddDays(2), Id = "GUID-2" },
new MeasuresSet() { Daisy = DateTime.Today.AddDays(3), Id = "GUID-3" },
new MeasuresSet() { Daisy = DateTime.Today.AddDays(4), Id = "GUID-4" }};
var measures = new List<Measure>() {
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-0", Value = 10 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-0", Value = 11 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-0", Value = 12 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-1", Value = 20 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-1", Value = 21 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-1", Value = 22 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-2", Value = 30 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-2", Value = 31 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-2", Value = 32 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-3", Value = 40 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-3", Value = 41 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-3", Value = 42 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-4", Value = 50 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-4", Value = 51 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-4", Value = 52 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-0", Value = 110 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-0", Value = 111 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-0", Value = 112 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-1", Value = 220 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-1", Value = 221 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-1", Value = 222 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-2", Value = 330 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-2", Value = 331 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-2", Value = 332 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-3", Value = 440 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-3", Value = 441 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-3", Value = 442 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "tyre pressure", MeasuresSetId = "GUID-4", Value = 550 },
//new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "engine", MeasuresSetId = "GUID-4", Value = 551 },
new Measure() { Id = Guid.NewGuid().ToString(), VehicleName = "NOT Mike's car", MeasureCategory = "air conditioner", MeasuresSetId = "GUID-4", Value = 552 }};
var result = from set in (from measureSet in measureSets
join measure in measures
on measureSet.Id equals measure.MeasuresSetId
select new
{
Day = measureSet.Day,
ParentId = measureSet.Id,
MeasureCategory = measure.MeasureCategory,
Id = measure.Id,
Value = measure.Value,
VehicleName = measure.VehicleName
})
where set.VehicleName == "Mike's car"
group set by set.MeasureCategory into g
select new
{
MeasureCategory = g.Key,
Day = g.Max(x => x.Day),
Value = g.First(x => x.Day == g.Max(y => y.Day)).Value
};
RESULTS :
Mike's car
tyre pressure | 04/06/2020 00:00:00 | 50
engine | 04/06/2020 00:00:00 | 51
air conditioner | 04/06/2020 00:00:00 | 52
NOT Mike's car
tyre pressure | 02/06/2020 00:00:00 | 330
engine | 03/06/2020 00:00:00 | 441
air conditioner | 04/06/2020 00:00:00 | 552
Upvotes: 1