Math Absolute In Ef Core?

Explanation

I was able to get it to work by moving the Math.Abs() call to the .GroupBy().

I'll show the answer first and below that I will post my entire mock up.

Solution

LINQ statement:

var temp = context.Picks
    .Include(x => x.Schedule)
    .Include(x => x.TeamChoice)

    .GroupBy(x => new { gameDiff = Math.Abs(x.Schedule.GameTotal.Value - x.TieBreakerScore.Value), name = x.TeamChoice.Value })

    .Select(g => new
        {
            name = g.Key.name,
            count = g.Count(),
            gameDiff = g.Key.gameDiff
        })
    .OrderByDescending(x => x.count)
    .ThenBy(x => x.gameDiff)
    .Take(top)
    .ToList();

This is the generated SQL:

SELECT TOP(@__p_0) [x.TeamChoice].[Value] AS [name], COUNT(*) AS [count], ABS([x.Schedule].[GameTotal] - [x].[TieBreakerScore]) AS [gameDiff]
FROM [Picks] AS [x]
INNER JOIN [Teams] AS [x.TeamChoice] ON [x].[TeamChoiceId] = [x.TeamChoice].[Id]
INNER JOIN [Schedules] AS [x.Schedule] ON [x].[ScheduleId] = [x.Schedule].[Id]
GROUP BY ABS([x.Schedule].[GameTotal] - [x].[TieBreakerScore]), [x.TeamChoice].[Value]
ORDER BY [count] DESC, [gameDiff]

Full Mock

I generated a full mock for anyone that wants to validate this or try to build from it.

SQL

IF OBJECT_ID('dbo.Picks', 'U') IS NOT NULL
    DROP TABLE dbo.Picks

IF OBJECT_ID('dbo.Teams', 'U') IS NOT NULL
    DROP TABLE dbo.Teams

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
    DROP TABLE dbo.Employees

IF OBJECT_ID('dbo.Schedules', 'U') IS NOT NULL
    DROP TABLE dbo.Schedules


CREATE TABLE Teams
(
    Id INT PRIMARY KEY IDENTITY
    ,[Value] VARCHAR(100) NOT NULL
)

CREATE TABLE Employees
(
    Id INT PRIMARY KEY IDENTITY
    ,[Value] VARCHAR(100) NOT NULL
)

CREATE TABLE Schedules
(
    Id INT PRIMARY KEY IDENTITY
    ,GameTotal INT NULL
)

CREATE TABLE Picks
(
    Id INT PRIMARY KEY IDENTITY
    ,ScheduleId INT FOREIGN KEY REFERENCES Schedules(Id) NOT NULL
    ,TeamChoiceId INT FOREIGN KEY REFERENCES Teams(Id) NOT NULL
    ,EmployeeId INT FOREIGN KEY REFERENCES Employees(Id) NOT NULL
    ,LastUpdated DateTime NOT NULL
    ,TieBreakerScore INT NULL
)

INSERT INTO Teams VALUES ('Team1')
INSERT INTO Employees VALUES ('Employee1')
INSERT INTO Schedules VALUES (150),(200)
IINSERT INTO Picks VALUES (1,1,1,GETDATE(),100),(2,1,1,GETDATE(),150)

Context and Entities

public class GameContext : DbContext
{
    public GameContext() { }

    public DbSet<Team> Teams { get; set; }
    public DbSet<Pick> Picks { get; set; }
    public DbSet<Schedule> Schedules { get; set; }
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.;Database=Sandbox;Trusted_Connection=True;ConnectRetryCount=0");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Pick>(entity =>
        {
            entity.HasOne(x => x.Employee)
                .WithMany(x => x.Picks)
                .HasForeignKey(x => x.EmployeeId);

            entity.HasOne(x => x.TeamChoice)
                .WithMany(x => x.Picks)
                .HasForeignKey(x => x.TeamChoiceId);

            entity.HasOne(x => x.Schedule)
                .WithMany(x => x.Picks)
                .HasForeignKey(x => x.ScheduleId);
        });
    }
}

public class Employee
{
    public int Id { get; set; }
    public string Value { get; set; }

    public ICollection<Pick> Picks { get; set; }
}

public class Schedule
{
    public int Id { get; set; }
    public int? GameTotal { get; set; }

    public ICollection<Pick> Picks { get; set; }
}

public class Team
{
    public int Id { get; set; }
    public string Value { get; set; }

    public ICollection<Pick> Picks { get; set; }
}

public class Pick
{
    public int Id { get; set; }
    public virtual Schedule Schedule { get; set; }
    public int ScheduleId { get; set; }
    public virtual Team TeamChoice { get; set; }
    public int TeamChoiceId { get; set; }
    public int? TieBreakerScore { get; set; }
    public virtual Employee Employee { get; set; }
    public virtual int EmployeeId { get; set; }
    public DateTime LastUpdated { get; set; }
}