Multi tool use

Get Max Value in Child Table with LINQ lambda

so i have data as follows (note that these table are simplified):

Header:
Id
1
2
3

Child:
Id HeaderId Code Round
1 1 A 1
2 1 A 2
3 1 B 1
4 2 A 1
5 2 B 1
6 2 C 1
7 3 A 2
8 3 B 1
9 3 A 1


I want to count how many header that doesnt have B in their child in their max round, from data tables above the result should be 2 because:

Header 1 doesnt have B in round 2 (header 1 max round is 2)

Header 2 have B in round 1

Header 3 doesnt have B in round 2 (header 3 max round is 2)

this is the linq i've try

var result = data.Where(e =>
!e.Child.Any(a =>
a.Code == "B" &&
a.Round == e.Child.Max(c => c.Round))).Count();


but doesnt work and i got this

ORA-00904: "Extent1"."EXPENSE_ID": invalid identifier

"Extent1"."ID" is Id column on Header table

how i can get the max Round in this case?

-----solved, thank you-----

2 Answers
2

All you need is the Child table. You can group the children by HeaderId and then find the max Round for each HeaderId, and check the children in that round for the Code.

Child

HeaderId

Round

HeaderId

Code

var ans = Child.GroupBy(c => c.HeaderId)
.Where(cg => {
var maxRound = cg.Max(c => c.Round);
return cg.Where(c => c.Round == maxRound).All(c => c.Code != "B");
})
.Count();


Turns out, my linq is not work because var result is a IQueryable so i need to change it to List using .ToList() before change it back to Queryable using .AsQueryable() (if needed), this is the final linq:

var result

IQueryable

.ToList()

.AsQueryable()

var result = data.ToList().Where(e =>
!e.Child.Any(a =>
a.Code == "B" &&
a.Round == e.Child.Max(c => c.Round))).Count();


thanks to everyone who helped me

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.