Get the Common data from the List

I have the below List in C#.

enter image description here

I need to generate the below data.

  1. In one row, all the users and apps they have in common for the particular Department, Profile, and Role
  2. In the 2nd row, each user has a unique application that is not included in the 1st row.

Below is the output I need.

enter image description here

Please Can anyone help here?

Below are the details.

Class:

class UserData
{
    public string Department { get; set; }
    public string Role { get; set; }
    public string Profile { get; set; }
    public string User { get; set; }
    public string Workstations { get; set; }
    public string Applications { get; set; }
}

List:

List<UserData> tempList = new List<UserData>
            {
                new UserData {Department="A", Role="B", Profile="C", User="X", Workstations="1", Applications="Skype" },
                new UserData {Department="A", Role="B", Profile="C", User="X", Workstations="1", Applications="Teams" },
                new UserData {Department="A", Role="B", Profile="C", User="Y", Workstations="2", Applications="Skype" },
                new UserData {Department="A", Role="B", Profile="C", User="Y", Workstations="2", Applications="Teams" },
                new UserData {Department="A", Role="B", Profile="C", User="Z", Workstations="3", Applications="Skype" },
                new UserData {Department="A", Role="B", Profile="C", User="Z", Workstations="3", Applications="Teams" },
                new UserData {Department="A", Role="B", Profile="C", User="X", Workstations="1", Applications="Office" },
                new UserData {Department="A", Role="B", Profile="C", User="X", Workstations="1", Applications="PDF" },
                new UserData {Department="A", Role="B", Profile="C", User="Y", Workstations="2", Applications="Test" },
                new UserData {Department="D", Role="E", Profile="F", User="X", Workstations="1", Applications="Skype" },
                new UserData {Department="D", Role="E", Profile="F", User="X", Workstations="1", Applications="Teams" },
                new UserData {Department="D", Role="E", Profile="F", User="Y", Workstations="2", Applications="Skype" },
                new UserData {Department="D", Role="E", Profile="F", User="Y", Workstations="2", Applications="Teams" },
                new UserData {Department="D", Role="E", Profile="F", User="Z", Workstations="3", Applications="Skype" },
                new UserData {Department="D", Role="E", Profile="F", User="Z", Workstations="3", Applications="Teams" },
                new UserData {Department="D", Role="E", Profile="F", User="X", Workstations="1", Applications="Office" },
                new UserData {Department="D", Role="E", Profile="F", User="X", Workstations="1", Applications="PDF" },
                new UserData {Department="D", Role="E", Profile="F", User="Y", Workstations="2", Applications="Test" }
            };

Here is my Solution:

List<UserData> CommonList = new List<UserData>();
List<UserData> UnCommonList = new List<UserData>();

   var UniqueUsers = tempList.GroupBy(r => new { r.User, r.Department, r.Profile, r.Role, })
                                                .OrderBy(g => g.Key.User).Distinct().ToList();

            var UniqueApplications = tempList.GroupBy(r => new { r.Applications, r.Department, r.Profile, r.Role, })
                                                .OrderBy(g => g.Key.Applications).ToList();

            foreach (var app in UniqueApplications)
            {
                var usersForApp = tempList.Where(x => x.Applications.Equals(app.Key.Applications) && x.Department.Equals(app.Key.Department) && x.Profile.Equals(app.Key.Profile) && x.Role.Equals(app.Key.Role)).Distinct().ToList();
                var usersGroup = UniqueUsers.GroupBy(r => new { r.Key.User, r.Key.Department, r.Key.Profile, r.Key.Role, })
                                                        .Where(x => x.Key.Department.Equals(app.Key.Department) && x.Key.Profile.Equals(app.Key.Profile) && x.Key.Role.Equals(app.Key.Role))
                                                .OrderBy(g => g.Key.User).Distinct().ToList();

                if (usersForApp != null)
                {
                    if(usersForApp.Count() == usersGroup.Count())
                    {
                        CommonList.AddRange(usersForApp);
                    }
                    else
                    {
                        UnCommonList.AddRange(usersForApp);

                    }
                }
            }
            var FinalCommon = CommonList.GroupBy(ac => new
            {
                ac.Department, 
                ac.Profile,
                ac.Role
            })
                    .Select(ac => new UserData
                    {
                        Department = ac.Key.Department,
                        Profile = ac.Key.Profile,
                        Role = ac.Key.Role,
                        User = string.Join(",", ac.Select(y => y.User).ToList().Distinct().ToArray()),
                        Applications = string.Join(",", ac.Select(y => y.Applications).Distinct().ToList().ToArray())
                    }).ToList();

            var FinalUnCommon = UnCommonList.GroupBy(ac => new
            {
                ac.Department,
                ac.Profile,
                ac.Role,
                ac.User
            })
                   .Select(ac => new UserData
                   {
                       Department = ac.Key.Department,
                       Profile = ac.Key.Profile,
                       Role = ac.Key.Role,
                       User = ac.Key.User,
                       Applications = string.Join(",", ac.Select(y => y.Applications).Distinct().ToList().ToArray())
                   }).ToList();

Can anyone help me to use Linq to avoid the looping?

Thanks in Advance.

1 answer

  • answered 2020-10-16 05:53 vedda

    Here is my attempt. It is not a single LINQ statement though.

    // find out what applications are common
    var firstPass = tempList.GroupBy(g => new
    {
        g.Department,
        g.Role,
        g.Profile,
        g.User,
        g.Workstations
    }).Select(g => new
    {
        g.Key,
        Applications = g.Select(o => o.Applications)
    });
    
    var distinctGroupsCount = firstPass.Count();
    
    // assume if an application repeats this amount it is available everywhere
    
    var appsEveryoneHas = tempList.GroupBy(s => s.Applications)
        .Where(g => g.Count() == distinctGroupsCount)
        .Select(s => s.Key)
        .ToHashSet();
      
    
    var finalResult = tempList
        .GroupBy(g => new
        {
            g.Department, 
            g.Role, 
            g.Profile, 
            User = appsEveryoneHas.Contains(g.Applications) ? string.Empty : g.User
        }).
        Select(s => new UserData
        {
            Department = s.First().Department,
            Role = s.First().Role,
            Profile = s.First().Profile,
            Workstations = string.Join(',', s.Select(o => o.Workstations).Distinct()),
            User = string.Join(',', s.Select(o => o.User).Distinct()),
            Applications = string.Join(',', s.Select(o => o.Applications).Distinct())
        })
        .ToList();