Math Absolute In Ef Core?

Question

I am trying to build a query like this

var d = dbContext.Picks
    .Where( /* some conditions */ )
    .GroupBy(x => new { gameDiff = x.Schedule.GameTotal.Value -  x.TieBreakerScore.Value })
    .Select(g => new { name = g.Key.firstname, count = g.Count(), 
        gameDiff = Math.Abs(g.Key.gameDiff) })
    .OrderByDescending(x => x.count)
    .ThenBy(x => x.gameDiff)
    .Take(top)
    .ToList();

But when I run this I get

System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'AS'.'

System.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Incorrect syntax near the keyword 'AS'.
  Source=Core .Net SqlClient Data Provider
  StackTrace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at  GetWeeklyWinners(Int32 week, Int32 season, Int32 top) in line 23
   at ValuesController.test() in line 54
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.<>c__DisplayClass33_0.<WrapVoidMethod>b__0(Object target, Object[] parameters)
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.VoidResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()

Is Math.Abs not supported or do I have to do it differently?

Here is the sql statement (a couple more fields then what was in my example)

exec sp_executesql N'SELECT TOP(@__p_0) COUNT(*) AS [count], ABS([x.Schedule].[GameTotal] - [x].[TieBreakerScore] AS [gameDiff]) AS [gameDiff]
FROM [Picks] AS [x]
INNER JOIN [Schedules] AS [x.Schedule] ON [x].[ScheduleId] = [x.Schedule].[Id]
GROUP BY [x.Schedule].[GameTotal] - [x].[TieBreakerScore]
ORDER BY [count] DESC, [gameDiff]',N'@__p_0 int',@__p_0=5

big this is that it is translating it to

  ABS([x.Schedule].[GameTotal] - [x].[TieBreakerScore] AS [gameDiff]) AS [gameDiff]

so has an extra "as" in it.

Pick class

  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 string EmployeeId { get; set; }
        public DateTime LastUpdated { get; set; }
    }

Shaikh

You are seeing a SQL error. Can you post the generated SQL statement? – Googolplex

I am not sure how to see the sql statement ef generates – Shaikh

You can find the SQL statement by using SQL Server Profiler or the Diagnostic Tools within Visual Studio. – Googolplex

Ok, will update my post. I see what is happening but dont' know why it is happening. – Shaikh

The AS command is used to rename a column with an alias, so I doubt it has to do with Math.Abs. Have you tried using aliases other than name and count in your Select statement? Those seem like they could be problematic, but I don't know – Tricksy

@RufusL - it is an "as" issue but it sort of because of Math.Abs I posted the updated code. – Shaikh

Where does the reference to Picks.TieBreakerScore (as tieBreakerScore) come from? It's not mentioned anywhere in the query. Is it defined as some sort of computed property? Check your model for oddities. – Cabasset

Can you post your Picks class? – Googolplex

What happens if you move the Math.Abs call into the GroupBy (around the subtraction expression)? – Chancellor

@JeroenMostert - sorry not sure what happened there but it should in the groupby clause. It has been updated. – Shaikh

@Chancellor - yeah tried that but same thing happens it makes an extra as. – Shaikh

@MattRowland - ok added it. – Shaikh

I see you have no attributes on the class. Is there any FluentApi regarding your Pick class? – Googolplex

Putting that grouping expression in new {...} appears to be unnecessary for a single column. can you try x => x.Schedule.GameTotal.Value - x.TieBreakerScore.Value ? That might be why it's adding the extra alias. (Really just stabbing in the dark here... this appears to be a bug in EF Core.) – Chancellor

@MattRowland - yes fluentapi but it is very basic, just has ValueGenratedOnAdd and sets TieBreakerScore to nullable – Shaikh

@Chancellor - I actually have more than one column that will be used for grouping but just trying to simplify the code to just show the problem. I also tried a concrete class as well – Shaikh

If I just do Math.Abs(0) then all is well but as soon as I try to use something from the object then I run into this problem. – Shaikh

@Shaikh try with the latest preview of EF Core 3. EF Core is a work in progress with a lot of pieces missing in EF 2. This way you'll know at least whether the bug was fixed. – Reliant

@Shaikh perhaps a better idea would be to not use an ORM at all. This is a reporting query, not something that loads entities. It's probably easier to do whatever you want using ranking functions, CTEs and the OVER clause. None of these is available through an ORM – Reliant

Answer 1

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

Googolplex

I stumbled upon this using a concrete class as well and saw that did sort of work, however when I did this it seemed like my grouping did not work(the Math.Abs seemed to work) but all of sudden my 4 records where not grouped. I will look at see if you did something different. – Shaikh

Looking at your generated sql statement, I don't see a groupby did you just not include it? – Shaikh

@Shaikh I explained that in the answer. The grouping does not happen on the SQL side. It happens within the application. – Googolplex

hmm, won't that then just mean all the records that match will be grabbed and the take will be done in memory? – Shaikh

@Shaikh I updated the answer with a different approach. – Googolplex

cool, so it seems like you added includes what then will add the groupby in. – Shaikh

Answer 2

It may be a EF Core grouping bug, similar to one described here: https://github.com/aspnet/EntityFrameworkCore/issues/12826. The bug should be fixed in latest preview.

If I understand the bug correctly workaround can look like this:

.Select(g => new { name = g.Key.firstname, count = g.Count(), 
    gameDiff = Math.Abs(g.First().Schedule.GameTotal.Value -  g.First().TieBreakerScore.Value) })

Moving

ok, I will look at that but I confused by your statement you wrote. So you move the subtraction to the select? But where is "x" coming from? – Shaikh

my mistake, there also should be g.First() – Moving

this is just a workaround and probably will generate some nasty looking sql – Moving

ok, that's what I thought but I am getting an error "Column 'Picks.ScheduleId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" – Shaikh

you can try efcore 2.0 or 3.0, as in these versions the bug isn't likely there. you can also try doing abs in second select: .Select(g => new { name = g.Key.firstname, count = g.Count(), g.Key.gamegiff }).Select(g => new {g.name, g.count, gamediff =Math.Abs(g.gamediff)) – Moving

I am running 2.2.4 is it easy to go to 3.0? Do I need to go to core 3.0 as well? Or can I just do ef? – Shaikh

efcore 3.0 requires .net core 3.0, both are currently in preview. downgrading do efcore 2.0 should be least problematic. .net core 2.2 doesn't support EF6 (framework), you would have to use preview .net core 3.0 and ef 6.3 previews – Moving