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