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)

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


Please 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


C#.NET ProgrammingASP.NET

Avatar of undefined
Last Comment
MohitPandit

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Misha

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Andrei Fomitchev

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.


   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:



_agx_

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

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
MohitPandit

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



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!
Misha

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
ASKER
MohitPandit

Okay, thanks. I'll execute and let you know.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
MohitPandit

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
Misha

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

Open in new window

ASKER
MohitPandit

I tried with sort list in another list and worked with previous code. Thanks.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes