c# - C# Linq or querying IEnumerable

问题描述

I have an Employee table which also has Department Manager information. I need to populate two dropdowns - one with Employees and other with Managers. Instead of using two queries to pull employees and another query to pull managers, I am querying table once and storing all info in cache in an IEnumerable EmployeeList.

I need some query to pull managers from that query - either using LINQ or loop within C# code. I have written loop but it is very inefficient.

Here is the SQL query to populate HCache:

    SELECT [Dept_Mgr_ID] As MgrId,
            EmployeeId,
        EmpLastName,
        EmpFirstName
        FROM Employee_tbl

Here I am trying to loop through the cache and join EmployeeId and MgrId

            List<DTO.Employee> Mgrs = new List<DTO.Employee>(0);
            for (int i = 0; i < HCache.EmployeeList.Count(); i++)
            {
                foreach(var e in HCache.EmployeeList)
                {

                    if (HCache.EmployeeList.ElementAt(i).EmployeeId == e.MgrId)
                    {

                        Mgrs.Add(new DTO.Employee() { MgrID = e.MgrId,

                            ManagerLastName = e.EmpLastName,
                            ManagerFirstName = e.EmpFirstName
             });
                    }
                }
            }

I am not using this query, however, this is how I can get the results using 2nd query to get managers:

 WITH CTE_Manager_ID
        AS
        (
        SELECT DISTINCT [Dept_Mgr_ID]
        FROM Employee_tbl
        )
        SELECT EmployeeId,
            EmpLastName,
            EmpFirstName
        FROM Employee_tbl Emp
        INNER JOIN CTE_Manager_ID cteMgr 
            ON cteMgr.Dept_Mgr_ID = Emp.EmployeeId

标签： c#linqfor-loopinner-joinienumerable

我会说您应该使用第二个 SQL 查询来获取管理器，但我会尝试加快您的代码速度。

问题：

假设EmployeeList是一个IEnumerable，EmployeeList.ElementAt(i)是一个 O(n) 操作，即慢。这是幕后的嵌套循环。
EmployeeList.Count()是一个 O(n) 操作，即慢。
代码的最终复杂度为 O(n^3)，即非常慢。

怎么提高：

做一次从EmployeeId到Employee（或你存储的任何东西HCache.EmployeeList）构建地图。这将使您能够通过 id 快速找到它们（在 O(1) 中）。
再次通过EmployeeList以收集经理。
EmployeeList总体复杂度为 O(n)，即与集合的大小成正比。

下面是一些代码来说明这个想法：

class Emp {
    public int EmployeeId {get;set;}
    public int MgrId {get;set;}
    public string EmpLastName {get;set;}
}
IEnumerable<Emp> EmployeeList = new List<Emp> {
    new Emp { EmployeeId = 1, MgrId = 0, EmpLastName = "boss" },
    new Emp { EmployeeId = 2, MgrId = 1, EmpLastName = "dude" } };
IDictionary<int, Emp> dict = EmployeeList.ToDictionary(e => e.EmployeeId);
var managers = EmployeeList
  .Select(e => dict.TryGetValue(e.MgrId, out Emp mgr) ? mgr : null)
  .OfType<Emp>()
  .ToList()
// List<Emp>(1) { Emp { EmpLastName="boss", EmployeeId=1, MgrId=0 } }

请注意，此代码可能会在managers列表中产生重复项，这可能是您想要的，也可能不是您想要的，但是您的代码以这种方式运行，因此我保留了该行为。

c# - C# Linq or querying IEnumerable

问题描述

解决方案

推荐阅读