Link to home
Start Free TrialLog in
Avatar of MohitPandit
MohitPanditFlag for India

asked on

C#.Net: Calculate group number from a list object

Hi,


I've a list in C# with below two columns i.e. Department and User (as per below snap shot)

User generated imageI would like to create new list along with group number and result should be like as below snap shot.


User generated imagePlease note, in case users are associated with other departments & count; then consider in one group. For example, aforementioned "Group Number" snap shot (yellow background). The "Dept1" and "Dept4" should get "1" group number.

But, "Dept5" should get "4" group number as user "Lee" is added into it.

Apart, above snap shots are just example; I may have about 50 departments and about 100 users (associated with 50 departments)


Could you assist into it?


Best Regards,

Mohit Pandit


ASKER CERTIFIED SOLUTION
Avatar of Misha
Misha
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You should put the data in the DB schema - In C# you can get required result with 1 SELECT from it. In the DB it is easier to update your data in future.
User generated image

   USE [testDB]
   GO
   IF 1=2 BEGIN -- 1=1 to drop
      DROP TABLE UserDepartmentGroups;
      DROP TABLE Groups;
      DROP TABLE Departments;
      DROP TABLE UsersAll;
   END
   GO
   IF NOT Exists(SELECT * FROM sys.tables WHERE name = 'Groups')
   CREATE TABLE Groups(GroupNumber Int PRIMARY KEY, GroupName NVarChar(50))
   GO
   IF NOT Exists(SELECT * FROM sys.tables WHERE name = 'Departments')
   CREATE TABLE Departments(DepartmentNumber Int PRIMARY KEY, DepartmentName NVarChar(50))
   GO
   IF NOT Exists(SELECT * FROM sys.tables WHERE name = 'UsersAll')
   CREATE TABLE UsersAll(UserNumber Int PRIMARY KEY IDENTITY, UserName NVarChar(50))
   GO
   IF NOT Exists(SELECT * FROM sys.tables WHERE name = 'UserDepartmentGroups')
   CREATE TABLE UserDepartmentGroups(Id Int PRIMARY KEY IDENTITY, UserNumber Int, GroupNumber Int, DepartmentNumber Int)
   GO
   IF NOT Exists(SELECT * FROM sys.foreign_keys WHERE name = 'fk_UserDepartment')
   ALTER TABLE UserDepartmentGroups 
   ADD CONSTRAINT fk_UserDepartment FOREIGN KEY (DepartmentNumber) 
   REFERENCES Departments(DepartmentNumber);
   GO
   IF NOT Exists(SELECT * FROM sys.foreign_keys WHERE name = 'fk_UserGroup')
   ALTER TABLE UserDepartmentGroups 
   ADD CONSTRAINT fk_UserGroup FOREIGN KEY (GroupNumber) 
   REFERENCES Groups(GroupNumber);
   GO
   IF NOT Exists(SELECT * FROM sys.foreign_keys WHERE name = 'fk_User')
   ALTER TABLE UserDepartmentGroups 
   ADD CONSTRAINT fk_User FOREIGN KEY (UserNumber) 
   REFERENCES UsersAll(UserNumber);
   GO
   INSERT INTO UsersAll (UserName) VALUES
   ('Michael'),('Henry'),('Lee');
   INSERT INTO Departments VALUES
   (1,'Dept1'),(2,'Dept2'),(3,'Dept3'),(4,'Dept4'),(5,'Dept5');
   INSERT INTO Groups VALUES
   (1,'Group1'),(2,'Group2'),(3,'Group3'),(4,'Group4');
   INSERT INTO UserDepartmentGroups (DepartmentNumber, UserNumber, GroupNumber) VALUES
   (1,1,1),(1,2,1),(2,1,2),(3,2,3),(4,1,1),(4,2,1),(5,1,4),(5,2,4),(5,3,4); -- <- Note 1
   SELECT * FROM UsersAll
   SEleCT * FROM Departments
   SELECT * FROM Groups
   SELECT * FROM UserDepartmentGroups
   
   SELECT d.DepartmentName, u.UserName, g.GroupNumber
   FROM UserDepartmentGroups udg
   LEFT JOIN UsersAll u ON u.UserNumber = udg.UserNumber 
   LEFT JOIN Groups g ON g.GroupNumber = udg.GroupNumber
   LEFT JOIN Departments d ON d.DepartmentNumber = udg.DepartmentNumber

Note 1. If you want insert data from your table into this set:
1. INSERT INTO Departments (DepartmentName) SELECT DISTINCT DepartmentName FROM yourTable.
2. INSERT INTO Groups SELECT DISTINCT ...
3. INSERT INTO UsersAll ...
4. Insert INTO UserDepartmentGroups (DepartmentNumber, UserNumber, GroupNumber)
SELECT ...

5. in the end
SELECT d.DepartmentName, u.UserName, g.GroupNumber
   FROM UserDepartmentGroups udg
   LEFT JOIN UsersAll u ON u.UserNumber = udg.UserNumber
   LEFT JOIN Groups g ON g.GroupNumber = udg.GroupNumber
   LEFT JOIN Departments d ON d.DepartmentNumber = udg.DepartmentNumber

shows the result:
User generated image


You should put the data in the DB schema 
+1.  Managing the relationships in the database is far more maintainable
Avatar of MohitPandit

ASKER

Hi Misha, thanks. I'll look into it and get back to you.
Hi Andrei, _agx_, thanks for your input. But I can't change database schema and have to be done in C# only.
Hi Misha,

Thanks again for code snippet. It works but as per below people list. The "Dept3" should also have GroupNumber = 1. But Henri comes first in that case. Could we handle respective scenario? Also, please find snap shot of debug mode, it got GroupNumber = 2.

List<Person> people = new List<Person>
            {   
                new Person("Dept1","Michael"),
                new Person("Dept1","Henri"),
                new Person("Dept2","Michael"),
                new Person("Dept2","Henri"),
                new Person("Dept3","Henri"),
                new Person("Dept3","Michael"),
                new Person("Dept4","Michael"),
                new Person("Dept4","Henri"),
                new Person("Dept5","Michael"),
                new Person("Dept5","Henri"),
            };

Open in new window


User generated image
Apart from this,
1. Could you please explain below "Equals" property
public override bool Equals(object obj) => obj is Person person && User == person.User; 

Open in new window

2. What is the use of below property of "Person" class
public override int GetHashCode() => base.GetHashCode();

Open in new window


Thanks in advance!
My solution does not work, because I forgot about elements order.

new Person("Dept1","Michael")
new Person("Dept1","Henri")

new Person("Dept3","Henri"),
new Person("Dept3","Michael")

Same people , but other order.

New solution:
    class Program
    {
        static void Main(string[] args)
        {
            List<Person> people = new List<Person>
            {
                new Person("Dept1","Michael"),
                new Person("Dept1","Henri"),
                new Person("Dept2","Michael"),
                new Person("Dept2","Henri"),
                new Person("Dept3","Henri"),
                new Person("Dept3","Michael"),
                new Person("Dept4","Michael"),
                new Person("Dept4","Henri"),
                new Person("Dept5","Michael"),
                new Person("Dept5","Henri"),
            };
            List<Person> res = new List<Person>();
            int groupNumber = 1;
            foreach (var group in people.GroupBy(x => x.Department))
            {
                var existingGroup = res.GroupBy(x => x.Department).FirstOrDefault(y => EqulasCollectionsIgnoreOrder(y, group));
                if (existingGroup != null)
                {
                    int existingGroupNumber = existingGroup.FirstOrDefault().GroupNumber;
                    foreach (Person p in group)
                    {
                        p.GroupNumber = existingGroupNumber;
                        res.Add(p);
                    }
                }
                else
                {
                    foreach (Person p in group)
                    {
                        p.GroupNumber = groupNumber;
                        res.Add(p);
                    }
                    groupNumber++;
                }
            }

            foreach (Person p in res)
                Console.WriteLine(p.ToString());
                  
            Console.ReadKey();
        }

        private static bool EqulasCollectionsIgnoreOrder<T>(IEnumerable<T> list1, IEnumerable<T> list2)
        {
            Dictionary<T, int> cnt = new Dictionary<T, int>();
            foreach (T s in list1)
            {
                if (cnt.ContainsKey(s))
                    cnt[s]++;

                else
                    cnt.Add(s, 1);
            }
            foreach (T s in list2)
            {
                if (cnt.ContainsKey(s))
                    cnt[s]--;

                else
                    return false;
            }
            return cnt.Values.All(c => c == 0);
        }
    }

    class Person
    {
        public Person(string department, string user)
        {
            Department = department;
            User = user;
        }

        public int GroupNumber { get; set; }

        public string Department { get; set; }

        public string User { get; set; }

        public override bool Equals(object obj) => obj is Person person && User == person.User;

        public override int GetHashCode() => HashCode.Combine(User);

        public override string ToString() => $"Department={Department}; User={User}; GroupNumber={GroupNumber.ToString()}";
    }

Open in new window

1)
Could you please explain below "Equals" property
I used SequenceEqual method to compare collections in group in the first solution. This method compare each object in both collections. Compare by calling Equals method. That`s why I override it.
2)
 What is the use of below property of "Person" class
Also I need to override GetHashCode if Person object will be used as a key in a Dictionary or HashSet.
This does not apply to first solution, but it need to the second solution!!! (It`s used in method EqulasCollectionsIgnoreOrder).
Also it is good practice and I not get warning message in Visual Studio when I override Equals method
Okay, thanks. I'll execute and let you know.
Hi Misha,
Sorry, I couldn't respond due to exigency.

Below property is not working, FYI, I do have target framework .NET Standard 2.0.
Could you assist into it?
 public override int GetHashCode() => HashCode.Combine(User);

Open in new window

Best Regards
Hi!
Try this code:
  public override int GetHashCode()
        {
            return base.GetHashCode();
        }

Open in new window

I tried with sort list in another list and worked with previous code. Thanks.