C#•2y ago

❔ SQL RAW to LINQ question

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
            ";

  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
            ";

5 Replies

Angius•2y ago

Sounds like two selects possibly?

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();

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();

The ??? would be row_number() OVER(PARTITION BY [FieldBoundaryId] ORDER BY [Start] DESC ORDER BY [Start] DESC is just .OrderByDescending(t => t.Start) The row number and partition stuff, tho... Distinct()...? .GroupBy()...? .DistinctBy()...?

wijnandOP•2y ago

It's basicly newest on top So sort.and 1 is what the s does

Saber•2y ago

so basically grouping by the FieldBoundaryId and taking the first item in each group ordered by Start desc for the participant,

wijnandOP•2y ago

That is correct atleast that is how I read it 🙂

            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;

            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;

I made something like this

Accord•2y ago

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.

Gaming

Programming

❔ SQL RAW to LINQ question