将表行解析为嵌套集合

Question

我有如下所示的数据表。我尝试使用C#对它们进行分组，使用清单2中的嵌套集合类结构，这样结果就像列表3一样，每个级别上的每个集合都不应该有重复的值。有人知道如何解析表吗？谢谢。

清单1：

    user_ID firstName Role                      service                     facility        RWP      Fac_ID     svc_ID   rol_ID
    ------- --------- -----------------------   --------------------------- --------------  -----
    874     Joe       Tech                      Primary                     New York, NY    9-2       1          1        1 
    874     Joe       Reviewer                  Primary                     New York, NY    2-5       1          1        2
    874     Joe       Reviewer                  Primary                     Los Angeles, CA 2-5       2          1        2
    874     Joe       Super User                Primary                     Los Angeles, CA 9-10      2          1        3
    874     Joe       Administrator             Application Administration  Los Angeles, CA 1-2       2          2        4 
    874     Joe       Super User                Application Administration  Chicago, IL     10-12     3          2        3 

清单2：

Facility {
    fac Facility; 
    List<Service> Services;
}
Service{
    svc Service;
    List<Role> Roles;
}
Role{
    rol Role;
    List<RWP> RWP;
}
RWP{
    string H;
}
fac{
    int fac_ID;
    string fac_Name;
}
svc{
    int svc_ID;
    string svc_Name;
}
rol{
    int rol_ID;
    string rol_Name;
}

清单3：

                 NY
                 |  
          +-- Primary --+   
          |             |
       Tech             Reviewer
       |                       |
       9-2                     2-5

       +------- LA --------------------+
       |                               |
    Application                 +----Primary----+   
    Administration              |               |
       |                        |               |
   Administrator              Reviewer      Super User
       |                        |               |
      1-2                      2-5             9-10

              CH
              |
        Application 
        Administration
              |
         Super User
              |
            10-12

清单4：

private static void populateUserFSR(ref User thisUser, DataTable dt, string FSRtype)
{
    string tmpFacility = string.Empty;
    string tmpService = string.Empty;
    string tmpRole = string.Empty;
    FSR thisFSR = new FSR();
    thisFSR.serviceRoles = new List<SR>();

    SR thisSR = new SR();
    thisSR.service = new List<Service>();
    thisSR.rolesWatchProviders = new List<RP>();

    RP thisRWP = new RP();
    thisRWP.providers = new List<string>();

    foreach (DataRow r in dt.Rows)
    {
        if (r["facility_ID"].ToString().Trim().ToUpper() != tmpFacility)
        {
            //new facility row
            if (!string.IsNullOrEmpty(tmpFacility))
            {
                //add to a proper collection according to their FSRtype
                switch (FSRtype)
                {
                    case "Assigned":
                        thisUser.FSRAssigned.Add(thisFSR);
                        break;
                    case "Preferred":
                        thisUser.FSRPreferred.Add(thisFSR);
                        break;
                    case "Selected":
                        thisUser.FSRSelected.Add(thisFSR);
                        break;
                }
                thisFSR = new FSR();
                thisFSR.serviceRoles = new List<SR>();

                thisSR = new SR();
                thisSR.service = new List<Service>();
                thisSR.rolesWatchProviders = new List<RP>();

                thisRWP = new RP();
                thisRWP.providers = new List<string>();
            }
            tmpFacility = r["facility_ID"].ToString();
            tmpService = string.Empty;
            tmpRole = string.Empty;
            thisFSR.facility = new Facility();
            thisFSR.facility.Facility_ID = int.Parse(r["facility_ID"].ToString());
            thisFSR.facility.Facility_Name = r["facility_name"].ToString();

            //only rank with FSR Assigned, no ranking with FSR Preferred
            if (FSRtype == "Assigned")
                thisFSR.rank = int.Parse(r["rank"].ToString());
        }

        if (r["Service_ID"].ToString().Trim().ToUpper() != tmpService)
        {
            tmpService = r["Service_ID"].ToString();
            tmpRole = string.Empty;
            Service thisService = new Service();
            thisService.ServiceID = int.Parse(r["Service_ID"].ToString());
            thisService.ServiceName = r["fac_service"].ToString();
            thisSR.service = new List<Service>();
            thisSR.service.Add(thisService);
            thisFSR.serviceRoles.Add(thisSR);
        }

        if (r["Role"].ToString().Trim().ToUpper() != tmpRole)
        {
            tmpRole = r["Role"].ToString();
            thisRWP.role = new Role();
            thisRWP.role.Id = int.Parse(r["role_ID"].ToString());
            thisRWP.role.Role = r["Role"].ToString();
            thisRWP.providers = getCohortPCP(thisUser);
            thisSR.rolesWatchProviders.Add(thisRWP);
        }
    }
}

Answer 1

试试这个：

var list3 = list1.AsEnumerable()
                 .GroupBy(x=>x.Field<string>("facility"))
                 .Select(g=> new Facility{ Facility=g.Key,       
                             Services = g.GroupBy(x=>x.Field<string>("Role"))
                                         .Select(g1=> new Service{
                                                    Service = g1.Key,
                                                    Roles = g1.GroupBy(x=>x.Field<string>("RWP"))
                                                              .Select(g2=> new Role{
                                                                  H = g2.Key
                                                               }).ToList()
                                                 }).ToList()
                        }).ToList();    

Answer 2

这个想法很简单：

1. 创建RWPs - Dictionary<string, RWP>字典(命名为rwps)： 数据表字符串strRWP =行“RWP”中的// foreach行；if (!rwps.ContainsKey(strRWP)) rwpsstrRWP =新的RWP(strRWP)；
2. 创建并填充Dictionary<string, Dictionary<string, RWP>> =字典，将{角色名称设置为RWP}。只需预先排行并填写此词典(将其命名为dicRoles)： // foreach行中的数据表字符串strRole =行“角色”，strRWP =行“RWP”；if (!dicRoles.ContainsKey(strRole)) dicRolesstrRole =新Dictionary()；if (!dicRolesstrRole.ContainsKey(strRWP)) dicRolesstrRole = rwpsstrRWP；
3. 从Dictionary<string, Role> roles创建Dictionary<string, Dictionary<string, RWP>> dicRoles： foreach (var对in dicRoles) rolespair.Key =新角色(pair.Key，pair.Value.Values.ToList()；
4. 然后创建并填充Dictionary<string, Dictionary<string, Role>> dicServices。 // foreach行中的数据表字符串strService =行“strService”，strRole =行“角色”；if (!dicServices.ContainsKey(strService)) dicServicesstrService =新Dictionary()；if strRole dicServicesstrService = rolesstrRole；
5. 重复步骤3从dicServices创建服务。
6. 重复步骤3-4以创建设施。

记住，这不是最佳代码。我只是想更容易地解释我的想法。