C
Join ServerC#
help
❔ SQL RAW to LINQ question
WWijnand2/21/2023
Greetings, I have a SQL query that was given to me but I was wondering if this query could be simplefied into a LINQ query.
A lot of the program was made during the time of EF core 1.0. we have a ton of SQL RAW.
A lot of the program was made during the time of EF core 1.0. we have a ton of SQL RAW.
const string query = @"
WITH cteRowNumber AS (
SELECT [PersonalFieldBoundaryId]
,[FieldBoundaryId]
,[ParticipantId]
,[Start]
,[RedUpper]
,[OrangeUpper]
,[GreenUpper]
,[GreenLower]
,[OrangeLower]
,[RedLower]
,row_number() OVER(PARTITION BY [FieldBoundaryId] ORDER BY [Start] DESC) AS RowNum
FROM PersonalFieldBoundaries
WHERE [Start] <= {1} AND [ParticipantId] = {0}
)
SELECT [PersonalFieldBoundaryId]
,[FieldBoundaryId]
,[ParticipantId]
,[Start]
,[RedUpper]
,[OrangeUpper]
,[GreenUpper]
,[GreenLower]
,[OrangeLower]
,[RedLower]
FROM cteRowNumber
WHERE RowNum = 1
";
AAngius2/21/2023
Sounds like two selects possibly?
AAngius2/21/2023
var stuff = await _context.Things
.Where(t => t.Start <= 1)
.Where(t => t.ParticipantId = 0)
.Select(t => new {
...,
RowNum = ???
})
.Where(x => x.RowNum = 1)
.Select(x => new ThingDto { ... })
.ToListAsync();
AAngius2/21/2023
The
???
would be row_number() OVER(PARTITION BY [FieldBoundaryId] ORDER BY [Start] DESC
AAngius2/21/2023
ORDER BY [Start] DESC
is just .OrderByDescending(t => t.Start)
AAngius2/21/2023
The row number and partition stuff, tho...
AAngius2/21/2023
Distinct()
...? .GroupBy()
...? .DistinctBy()
...?WWijnand2/21/2023
It's basicly newest on top
WWijnand2/21/2023
So sort.and 1 is what the s does
DDeluxe2/21/2023
so basically grouping by the
FieldBoundaryId
and taking the first item in each group ordered by Start desc for the participant,WWijnand2/22/2023
That is correct atleast that is how I read it 🙂
WWijnand2/22/2023
var PersonalFieldBoundary = await _medicalDbContext.PersonalFieldBoundaries.Include( x=>x.FieldBoundary)
.AsNoTracking()
.Where(x => x.ParticipantId == participantId && x.Start <= insertDate)
.GroupBy(x => new { x.FieldBoundaryId })
.Select(x => x.OrderByDescending(x => x.Start)
.Take(1))
.SelectMany(x => x)
.ToListAsync();
return PersonalFieldBoundary;
WWijnand2/22/2023
I made something like this
AAccord2/23/2023
Was this issue resolved? If so, run
/close
- otherwise I will mark this as stale and this post will be archived until there is new activity.