时间戳

首页 > 解决方案 > C# 实体框架按 Tie Breaker 分组

c# - C# 实体框架按 Tie Breaker 分组

问题描述

我有一个Person像这样定义的类：

public class Person {
    string Name; // "Joe", "Alex", etc.
    string State; // "Minnesota", "Texas", etc.
    int Age; // "12", "23", etc.
}

我使用 PostgresQL 作为带有 EF Core 的后备数据库，将person表绑定到Person类。

我想执行一个查询，返回List<Person>每个州最年长的人。例如，如果我有：

Person p1 = new{
   Name = "Joe",
   State = "Minnesota",
   Age = 34
};
Person p2 = new{
  Name = "Alex",
  State = "Minnesota",
  Age = 55
};
Person p3 = new{
  Name = "George",
  State = "Texas",
  Age = 62
}

我想执行一个返回p2and的查询p3（结果列表的顺序p2和p3内部的顺序无关紧要）。

现在，我正在使用以下查询：

List<Person> oldestByState = await _context.Person.GroupBy(x => x.State).Select(x => x.OrderByDescending(y => y.Age).First()).ToListAsync();

问题在于 C# 不知道如何将其转换为 SQL。我收到一个x.OrderByDescending(y => y.Age)无法翻译的错误。

我知道快速解决方法是在客户端上进行分组，但我更愿意通过修复 Linq 查询来在数据库上运行查询。

我应该使用什么查询来返回与 Linq 到 PostgresQL 转换兼容的每个州中最年长的人的列表？

EF 核心：5.0.7 Npgsql：5.0.6

标签： c#entity-frameworklinq

解决方案

我用 SQL Server 对此进行了测试，从 EF 收到的错误是“First只能用作最终运算符，请尝试FirstOrDefault”。使用FirstOrDefault工作返回预期的最旧行。

await _context.Person
    .GroupBy(x => x.State)
    .Select(x => x.OrderByDescending(y => y.Age).FirstOrDefault())
    .ToListAsync();

如果它仍然不能与 PostgreSQL 一起使用，那么它可能是该提供程序的限制。

编辑：一种可能的解决方法是使用原始 SQL 查询，等待 npgsql 团队的修复。对于我的测试架构有点不同，EF for SQL Server 产生了：

SELECT 
    [Limit1].[PersonId] AS [PersonId], 
    [Limit1].[Name] AS [Name],
    [Limit1].[State] AS [State], 
    [Limit1].[Age] AS [Age], 
    FROM   (SELECT DISTINCT 
        [Extent1].[State] AS [State]
        FROM [dbo].[Persons] AS [Extent1] ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[PersonId] AS [PersonId], [Project2].[Name] AS [Name], [Project2].[Age] AS [Age], [Project2].[State] AS [State]
        FROM ( SELECT 
            [Extent2].[PersonId] AS [PersonId], 
            [Extent2].[Name] AS [Name], 
            [Extent2].[Age] AS [Age], 
            [Extent2].[State] AS [State]
            FROM [dbo].[Children] AS [Extent2]
            WHERE [Distinct1].[State] = [Extent2].[State]
        )  AS [Project2]
        ORDER BY [Project2].[Age] DESC ) AS [Limit1]

然后执行：

await _context.Person.FromSqlRaw(@"    SELECT 
    [Limit1].[PersonId] AS [PersonId], 
    [Limit1].[Name] AS [Name],
    [Limit1].[State] AS [State], 
    [Limit1].[Age] AS [Age], 
    FROM   (SELECT DISTINCT 
        [Extent1].[State] AS [State]
        FROM [dbo].[Persons] AS [Extent1] ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[PersonId] AS [PersonId], [Project2].[Name] AS [Name], [Project2].[Age] AS [Age], [Project2].[State] AS [State]
        FROM ( SELECT 
            [Extent2].[PersonId] AS [PersonId], 
            [Extent2].[Name] AS [Name], 
            [Extent2].[Age] AS [Age], 
            [Extent2].[State] AS [State]
            FROM [dbo].[Children] AS [Extent2]
            WHERE [Distinct1].[State] = [Extent2].[State]
        )  AS [Project2]
        ORDER BY [Project2].[Age] DESC ) AS [Limit1]").ToListAsync();

像 &*^ 一样丑陋，并且可能与您的架构不匹配，但如果您可以编写一个返回数据的查询，那么只要它返回您想要的实体，EF 应该能够填充它。如果您尝试返回更复杂的对象图，这将更加复杂。（即具有许多列和相关实体的人员）在这些情况下，我建议运行两个查询，一个执行仅返回 PersonId 的分组和选择，然后使用这些 PersonIds 通过 ID 获取相关人员及其相关数据。

推荐阅读