c# - 如何避免在 C# LINQ 中连续嵌套 .Any-6ren

c# - 如何避免在 C# LINQ 中连续嵌套 .Any

转载作者：太空狗更新时间：2023-10-29 21:43:37

我有一个收藏列表 BossList。因为我使用嵌套的 .Any() 来确定条件。现在我的真实项目中的性能非常慢。请考虑以下示例源代码。

void Main()
{

    List<Boss> BossList = new List<Boss>()
    {
        new Boss()
        {
            ID = 101,
            Name = "Harry",
            Department = "Development",
            Gender = "Male",
            Role = "Manager",
            Employees = new List<Person>() {
                new Person() {
                    ID = 101, 
                    SID = 102, 
                    Name = "Peter", 
                    Department = "Development", 
                    Gender = "Male", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>()
                    {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                },
                new Person() {
                    ID = 101, 
                    SID = 103, 
                    Name = "Emma Watson", 
                    Department = "Development", 
                    Gender = "Female", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>() {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                },

            }
        },
        new Boss()
        {
            ID = 104,
            Name = "Raj",
            Department = "Development",
            Gender = "Male",
            Role = "Manager",
            Employees = new List<Person>()
            {
                new Person() {
                    ID = 104, 
                    SID = 105, 
                    Name = "Kaliya", 
                    Department = "Development", 
                    Gender = "Male", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>() {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                },
                new Person() {
                    ID = 104, 
                    SID = 103, 
                    Name = "Emma Watson", 
                    Department = "Development", 
                    Gender = "Female", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>() {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                },

            },
        },
        new Boss()
        {
            ID = 102,
            Name = "Peter",
            Department = "Development",
            Gender = "Male",
            Role = "Manager",
            Employees = new List<Person>()
            {
                new Person() {
                    ID = 102, 
                    SID = 105, 
                    Name = "Kaliya", 
                    Department = "Development", 
                    Gender = "Male", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>() {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 6 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 8 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                },
                new Person() {
                    ID = 102, 
                    SID = 103, 
                    Name = "Emma Watson", 
                    Department = "Development", 
                    Gender = "Female", 
                    Role = "Assistant",
                    PayInfo = new List<PayrollInfo>() {
                        new PayrollInfo() { Monthof2015 = 1, NetWorkingDays = 24, AbsentDays = 5 },
                        new PayrollInfo() { Monthof2015 = 2, NetWorkingDays = 23, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 3, NetWorkingDays = 20, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 4, NetWorkingDays = 22, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 5, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 6, NetWorkingDays = 26, AbsentDays = 9 },
                        new PayrollInfo() { Monthof2015 = 7, NetWorkingDays = 25, AbsentDays = 4 },
                        new PayrollInfo() { Monthof2015 = 8, NetWorkingDays = 21, AbsentDays = 3 },
                        new PayrollInfo() { Monthof2015 = 9, NetWorkingDays = 20, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 10, NetWorkingDays = 25, AbsentDays = 2 },
                        new PayrollInfo() { Monthof2015 = 11, NetWorkingDays = 24, AbsentDays = 1 },
                        new PayrollInfo() { Monthof2015 = 12, NetWorkingDays = 26, AbsentDays = 1 },
                    }
                }
            }
        }
    };

    BossList.Where(i => i.Employees.Any(j => j.PayInfo.Any(s => s.AbsentDays >6))).Select(m => m.Name).Dump();
}

模型类是

public class Person
{
    public int ID { get; set; }
    public int SID { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public string Gender { get; set; }
    public string Role { get; set; }
    public List<PayrollInfo> PayInfo { get; set; }
}

public class Boss
{
    public int ID { get; set; }
    public int SID { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public string Gender { get; set; }
    public string Role { get; set; }
    public List<Person> Employees { get; set; }
}

public class PayrollInfo
{
    public int Monthof2015 { get; set; }
    public int NetWorkingDays { get; set; }
    public int AbsentDays { get; set; }
}

主要的 LINQ 查询是

BossList.Where(i => i.Employees
                      .Any(j => j.PayInfo
                                 .Any(s => s.AbsentDays >6)))
        .Select(m => m.Name);

在我的主项目中，它包含 6 个嵌套的 .Any()。由于代码冗长，这里我无法创建。

我将 EF 中的查询用作 IQueryable。数据库包含超过 1000K 条记录。

请建议我，有什么有效的方法可以最大限度地提高性能吗？

最佳答案

可能最好的方法仍然是使用 BossList 作为起点，但是构建一个 ID 列表，其中应该出现它们的 ID:

db.BossList.Where(b => db.PayrollInfos.Where(s => s.AbsentDays >6)
                                .Select(p => p.Employee.BossId)
                   .Contains(b.BossId));

在 SQL 中，这可能会转化为简洁高效的 EXISTS 查询。

请注意，我假设在您的真实模型中您有反向引用 (PayrollInfos.Employee)，并且我使用了一个占位符 BossId，您应该将其替换为真正的关键属性。从你的例子中有点不清楚引用 ID 和 SID 是如何工作的。

db 是您的 DbContext 实例。

再多解释一下这种方法:根据我的经验，通常最有效的方法是从包含您要查找的结果的集合开始查询，即 Bosslist，然后添加谓词过滤结果。其他答案以 PayrollInfo 开头。没关系，但最后您需要分组或 Distinct 来删除重复项。通常这不会使查询计划比相对简单的 WHERE EXSIST( subqyery ) 表更好。

关于c# - 如何避免在 C# LINQ 中连续嵌套 .Any，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36599076/

文章推荐： c++ - 带有 cmake 和 glew glfw3 和 glm 库的 Opengl

文章推荐： c++ - 在 clang 中显式调用伪析构函数

文章推荐： c++ - 是否可以从混合的 C++/CLI 代码中调用 IronPython？

Jquery检查元素是否隐藏(连续)
如何检查一个元素是否立即隐藏。即如何通知元素的可见性。在我的例子中，该元素是通过 slideUp 函数隐藏的。我应该立即收到有关该元素的可见性的通知。我想到了使用bind()方法。但它没有类似 o
连续 if 语句流程
if (srcbloc == NULL) { fprintf(stderr, "warning!: memrip source is null!\n"); exit(1); } if
SQL - 连续 "ON"语句
当我在数据库的旧 View 中清理一些问题时，我遇到了这个“奇怪”的连接条件: from tblEmails [e] join tblPersonEmails [pe]
css - 如何水平对齐多个图像(连续)？
如何水平对齐多张图像，一张一张地？它们不必适合宽度屏幕:相反，我希望它们超过后者的宽度，如果这有任何意义的话。我已经检查了很多类似问题的答案，但找不到任何可以解决我的问题的答案。 HTML:
Cassandra ttl 连续
我知道 Cassandra 中的列有 TTL。但是也可以在一行上设置 TTL 吗？在每列上设置 TTL 并不能解决我的问题，如下面的用例所示: 在某些时候，一个进程想要删除一个带有 TTL 的完整行(
macos - 连续 NSTextField
我有一个 NSTextField 和 Label，其值绑定(bind)到 View Controller 中的相同 NSString 这里的问题是标签只有在我按 Tab 时才会更新。如何使其连续，以
javascript - 在字符串中插入下划线代替单个或多个空格(连续)
例如。 1."abc"; ===>abc 2."ab c"; ===>ab_c 3."ab c"; ===>ab_c 4."ab c" ===>ab_c 对于多个连续空格也是如此。我怎样
javascript - 如何获取前一天(连续)
大家好，我想获取前一天或最后一天的信息，只有当我按下按钮时，它才会显示最后一天(星期六)的所有信息，如果我再次单击按钮，它将显示最后一天的信息(星期五)如果我再次点击它(星期四)谢谢你们帮助我编辑:
audio - 使用mplayer从音频流中提取冰冷的元数据(连续)
我需要从实时音频流中提取ICY元数据，并正在使用mplayer进行此操作，因为它在播放音频流时会输出元数据。我欢迎其他方式执行此操作，目标是将更新的元数据(歌曲信息)保存到文本文件中，只要歌曲(或数据
web - (连续)网络浏览器中有限单词的语音识别
语音识别有没有解决方案只有几个字(2 个就够了，10 个就不错了。100 个就很棒了。不需要更多) 也在移动浏览器上运行(是否可以为此使用 flash(而不是 java)？) 可以安装在您自己的服务
Python条件连接不以标点符号结尾的*连续*字符串
我有一个单词列表， list1 = ['hello', 'how', 'are', 'you?', 'i', 'am', 'fine', 'thanks.', 'great!'] 我想加入， list
连续 "undefined reference to..."
我正在开发一个程序，但我不断收到“对‘dosell’的 undefined reference ”，我不太明白发生了什么。这是函数的声明: void dosell(int *cash, int *nu
MYSQL - 连接三个表(连续？)
我无法提出执行我要做的事情所需的查询。我有三个这样的表: client_files ----------------------- client_id file_id ---------
javascript - 连续/无限滚动
我一直在寻找一个插件/脚本，当到达底部时，它会从头开始继续滚动网站，就像一个连续的循环。示例:http://unfold.no/和 http://www.aquiesdonde.com.ar/ 我尝
连续 scanf 正在添加字符串
这个问题在这里已经有了答案: How to prevent scanf causing a buffer overflow in C? (6 个答案) 关闭 6 年前。我一直在使用一个非常简单的程
c++ - 子数组中两个数字的相同出现(连续)
给定一个整数数组，找到具有相同数量的 x 和 y 的连续子序列的总数。例如 x=1 和 y=2 的数组 [1,2,1] ans = 2 表示它的两个子数组 [1,2] 和 [2,1]。检查每个连续的子
arrays - 查找总和小于给定值的最大元素(连续)？
所以，我有一个所有正自然数的数组。我得到了一个阈值。我必须找出总和小于给定阈值的数字(连续)的最大计数。 For example, IP: arr = {3,1,2,1} Threshold = 5
java - 如何像内置相机一样实现Android相机对焦(连续)
我制作了像内置相机一样的相机应用。我想实现像内置相机一样的连续对焦功能。(此功能我不触摸屏幕，但相机会尝试自行对焦。) 因此，将其设置为 surfaceCreated : Camera.Pa
r - 连续 block 上的平均
我有这样的数据: f x A 1.1 A 2.2 A 3.3 B 3.5 B 3.7 B 3.9 B 4.1 B 4.5 A 5.1 A 5.2 C 5.4 C 5.5 C 6.1 B 6.2 B
sql - 连续 n 分钟超过阈值
假设我有一个包含一组数据点的表，每个数据点由一个时间戳和一个值组成。如果至少有 N 个连续记录(按时间戳排序)高于给定值 X，我将如何编写返回 true (1) 的查询，否则返回 false (0)？

太空狗

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

c# - 如何避免在 C# LINQ 中连续嵌套 .Any