c# - Ef Core 中的数学绝对值？

Question

我正在尝试构建这样的查询

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

但是当我运行它时我得到了

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

Math.Abs​​ 不受支持还是我必须以不同的方式进行？

这是 sql 语句(比我的示例中多了几个字段)

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

大这是它正在将它翻译成

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

所以里面多了一个“as”。

选择类

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

Answer 1

解释

我能够通过将 Math.Abs​​() 调用移动到 .GroupBy() 来让它工作。

我将首先展示答案，然后在下面发布我的整个模型。

解决方案

LINQ语句:

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

这是生成的 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]

完整模拟

我为任何想要验证它或尝试从中构建的人生成了一个完整的模拟。

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)

上下文和实体

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