使用实体框架的复杂LINQ查询

本文关键字：复杂 LINQ 查询 框架 实体 | 更新日期: 2023-09-27 17:49:35

我一直在做一些LINQ，我似乎不知道如何过滤这些数据。我对LINQ还是个新手，这是我遇到的第一个真正的绊脚石。

I have table:

TaskID | Name | TypeID | ParentTaskID

价值观:

1 | TaskName | 1 | NULL

2 | TaskName| 6 | NULL

3 | TaskName| 6 | 4

4 | TaskName| 2 | NULL

5 | TaskName| 6 | 4

6 | TaskName| 3 | 4

7 | TaskName| 6 | 1

8 | TaskName| 6 | 1

9 | TaskName| 2 | NULL

子任务是一个具有ParentTaskID的任务。

我需要获得符合此标准的所有任务:

  • 如果Task没有分配子任务且TypeID = 6，则删除Task
  • 删除父任务和子任务如果一个ParentTask有子任务都有一个TypeID = 6

因此我期望返回

3 | TaskName| 6 | 4

4 | TaskName| 2 | NULL

5 | TaskName| 6 | 4

6 | TaskName| 3 | 4

9 | TaskName| 3 | NULL

上表的结果如下:

taskkid 1被移除，因为taskkid 7 &8是TypeID = 6的子任务

删除TaskID 2，因为它没有SubTasks和TypeID = 6

task7 &删除8，因为它们是ParentTask 1的子任务，TypeID = 6

是否有可能在LINQ中做到这一点，或者我必须在foreach循环中执行此操作?

<标题> 我的解决方案

所以我最终使用了Foreach的老式方法，并让Resharper将它可以转换回LINQ。

        var parents = tasks.Where(task => task.SubTasks.Any());
        var tasksWithNoSubs = tasks.Where(task => !task.SubTasks.Any());
        var parentIdsToRemove = new List<int?>();
        var childIdsToRemove = new List<int?>();
        foreach (var parent in parents)
        {
            var children = tasks.Where(task => task.ParentTaskID == parent.Id);
            if (children.All(task => task.StatusId == 6))
            {
                parentIdsToRemove.Add(parent.Id); //mark this parent for removal
                childIdsToRemove.AddRange(Enumerable.Select(children.Select(child => child.Id), dummy => (int?) dummy));
            }
        }
        //get all Tasks that have NO Subtasks and also have a StatusID = 6
        parentIdsToRemove.AddRange(Enumerable.Select((from task in tasksWithNoSubs where task.StatusId == 6 select task.Id), dummy => (int?) dummy));
        //get a list of children with an Id contained in the list parentIdsToRemove
        var tasksToRemove =
            tasks.Where(task => parentIdsToRemove.Contains(task.Id) || childIdsToRemove.Contains(task.Id));
        //remove the tasks from the collection
        tasks = tasks.Where(task => !tasksToRemove.Contains(task));

使用实体框架的复杂LINQ查询

您应该直接在您的任务对象(由实体框架生成)上具有属性，其中包括对父/子的引用，我假设父对象名为ParentTask，而子对象在我的示例中名为Tasks，因此重命名。你不需要使用id这是非常不真实的，EF的全部意义;linq是让它处理所有的数据库反规范化的废话& &;使用对象图。

YourContext.Tasks
    // Only keep those where it isn't true that it contains no tasks while being type 6
    .Where(t=>!((!t.Tasks.Any()) && t.TypeId == 6))
    // Remove all parents that contains subtasks and only have subtasks with type 6
    .Where(t=>!(t.Tasks.Any() && t.Tasks.All(sub=>sub.TypeId == 6)))
    // Same process on the children, if it has a parent and that parent matches the previous filter
    .Where(t=>!(t.ParentTask != null &&(t.ParentTask.Tasks.Any() && t.ParentTask.Tasks.All(sub=>sub.TypeId == 6))))

注意这是完全未经测试的，如果它不工作，请提供一个链接到您编译的模型DLL

请参阅原始帖子(也发布在这里)的解决方案。

    var parents = tasks.Where(task => task.SubTasks.Any());
    var tasksWithNoSubs = tasks.Where(task => !task.SubTasks.Any());
    var parentIdsToRemove = new List<int?>();
    var childIdsToRemove = new List<int?>();
    foreach (var parent in parents)
    {
        var children = tasks.Where(task => task.ParentTaskID == parent.Id);
        if (children.All(task => task.StatusId == 6))
        {
            parentIdsToRemove.Add(parent.Id); //mark this parent for removal
            childIdsToRemove.AddRange(Enumerable.Select(children.Select(child => child.Id), dummy => (int?) dummy));
        }
    }
    //get all Tasks that have NO Subtasks and also have a StatusID = 6
    parentIdsToRemove.AddRange(Enumerable.Select((from task in tasksWithNoSubs where task.StatusId == 6 select task.Id), dummy => (int?) dummy));
    //get a list of children with an Id contained in the list parentIdsToRemove
    var tasksToRemove =
        tasks.Where(task => parentIdsToRemove.Contains(task.Id) || childIdsToRemove.Contains(task.Id));
    //remove the tasks from the collection
    tasks = tasks.Where(task => !tasksToRemove.Contains(task));

感谢大家的贡献，我学到了很多关于LINQ的知识。

Second

吧!所以，在很长一段时间和一些误解之后，第三次幸运了——下面是一个声明:

entities.
// discard tasks of type 6 and with no subtasks
Where(x => !(x.TypeId == 6 && x.ParentTaskId == null && 
      !entities.Any(y => y.ParentTaskId == x.TaskId))).
// discard tasks with subtasks where all those subtasks are of type 6
Where(x => !(entities.Any(y => y.ParentTaskId == x.TaskId) &&
             entities.Count(y => y.ParentTaskId == x.TaskId) ==
             entities.Count(y => y.ParentTaskId == x.TaskId && y.TypeId == 6))).
// discard subtasks that have parents that are not to be found in the list of 
// tasks that were not discarded for having subtasks that were all of type 6.
Where(z => z.ParentTaskId == null || 
             entities.Where(x => !(entities.Any(y => y.ParentTaskId == x.TaskId) &&
                                   entities.Count(y => y.ParentTaskId == x.TaskId) ==
                                   entities.Count(y => y.ParentTaskId == x.TaskId && y.TypeId == 6))).
                                            Any(y => y.TaskId == z.ParentTaskId));
相关文章：