❔ 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.

  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.