Convert SQL query to linq method syntax. Multiple group by and week of year required-CodePudding

I have this tSQL query that is returning what I need to see from the data.

  select AppName, MethodName, sum(ElapsedMilliseconds)/count(MethodName) as avgMS
  from [dbo].[AppMethodPerformance]
  where YEAR(MethodDateTime) = 2023 and DatePart(week, MethodDateTime) = 4
  group by appname, MethodName
  order by AppName, MethodName

I need this in linq method syntax to work as close as possible

Ive been trying these two attempts but im not getting anywhere.

            List<AppMethodPerformance> allEntires = new();

            using (var ctx = new AppLogContext(clientName))
            {
                //allEntires = await ctx.AppMethodPerformances
                //                        .Select(s => new { s.ElapsedMilliseconds, s.AppName, s.MethodName, s.MethodDateTime})
                //                        .Where(s => s.MethodDateTime.Year == year && ISOWeek(s.MethodDateTime) == week)
                //                        .GroupBy(s => new { s.AppName, s.MethodName, s.ElapsedMilliseconds } ).Sum(x => x.Key.ElapsedMilliseconds)
                //                        .Select(s => 
                //                                new AppMethodPerformance
                //                                {
                //                                    AppName = s.Key.AppName,
                //                                    MethodName = s.Key.MethodName,
                //                                    ElapsedMilliseconds = s.Key.ElapsedMilliseconds
                //                                })
                //                        .OrderBy(s => s.AppName).ThenBy(s => s.MethodName)
                //                        .ToListAsync();
                allEntires = await ctx.AppMethodPerformances
                                        .Where(s => s.MethodDateTime.Year == year && ISOWeek(s.MethodDateTime) == week)
                                        .GroupBy(s => new
                                        {
                                            s.AppName,
                                            s.MethodName,
                                            s.ElapsedMilliseconds
                                        })
                                        .Select(s => 
                                                new AppMethodPerformance 
                                                { 
                                                    AppName = s.Key.AppName,
                                                    MethodName = s.Key.MethodName,
                                                    ElapsedMilliseconds = s.Key.ElapsedMilliseconds
                                                })
                                        .ToListAsync();
            }

I also realize that now that ISOweek method will not work inside EF because its not convertible to sql. So ill need to find another way to match with the week of the year. I need some direction on how to best tackle this.

ref

        private static int ISOWeek(DateTime methodDateTime)
        {
            CultureInfo myCI = new CultureInfo("en-US");
            Calendar myCal = myCI.Calendar;

            // Gets the DTFI properties required by GetWeekOfYear.
            CalendarWeekRule myCWR = myCI.DateTimeFormat.CalendarWeekRule;
            DayOfWeek myFirstDOW = myCI.DateTimeFormat.FirstDayOfWeek;

            return myCal.GetWeekOfYear(DateTime.Now, myCWR, myFirstDOW);
        }

CodePudding user response：

You can calculate the week from any date (the first day or the first monday/sunday of the year) and then divide by 7. In the documentation from SQL server Datepart (https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver16#week-and-weekday-datepart-arguments) is calculated from january first of a given year.

allEntires = await ctx.AppMethodPerformances
    .Where(s => (s.MethodDateTime - GetFirstMondayOfYear(year)).TotalDays / 7 == week - 1)
    //.Where(s => (s.MethodDateTime - new DateTime(year, 1, 1)).TotalDays / 7 == week - 1)
    .GroupBy(s => new
        {
            s.AppName,
            s.MethodName,             
        })
    .Select(s => 
        { 
             AppName = s.Key.AppName,
             MethodName = s.Key.MethodName,
             AvgMs = s.Average(y => y.EllapsedMilliseconds)
        })
    .OrderBy(x => x.AppName)
    .ThenBy(x => x.MethodName)
    .ToListAsync();

In this example, the GetFirstMondayOfYear method is resolved before generating the SQL to something like this:

... /* for week 4 of 2023 from jan 1*/
WHERE DATEDIFF(DAY, DATEFROMPARTS(2023, 01, 01), MethodDateTime) / 7 = 3
...

and also, you may simplify the average calculation with Average().

FirstMonday function: whats the simplest way to calculate the Monday in the first week of the year

CodePudding user response：

As for the date/time filter, you may get the best results by calculating a start and end date/time for the given week up front, and then filter on MethodDateTime within that range. This allows you to filter using a simple range comparison and allows efficient use of indexes.

A common best practice is to calculate an inclusive start date and an exclusive end date (00:00 on the following day). This cleanly handles times without having to resort to truncation or imprecise "23:59:59.???" logic.

The AvgMS value can be calculated as a simple average of elements within the group.

The following calculates the start date from the given year and ISO week, calculates the exclusive end date as 7 days later, and applies the filter as described above.

DateTime startDate = ISOWeek.ToDateTime(year, week, DayOfWeek.Monday);
DateTime endDate = start.AddDays(7); // Exclusive

allEntires = await ctx.AppMethodPerformances
    .Where(s => s.MethodDateTime >= startDate && s.MethodDateTime < endDate)
    .GroupBy(s => new {
        s.AppName,
        s.MethodName
    })
    .Select(s => new AppMethodPerformance { 
        AppName = s.Key.AppName,
        MethodName = s.Key.MethodName,
        AvgMS = s.Average(s.Key.ElapsedMilliseconds)
    })
    .OrderBy(s => s.AppName).ThenBy(s => s.MethodName)
    .ToListAsync();

The above assumes that ISO Weeks always start on a Monday (possibly at the end of the preceding year).