How to write the the access control

How can I write the access control algorithm or helper classfor below conditions.

I have following design:

Table Customer (Id, Name...)
Table User (Id, Username, Name, Manager (UserId - from this User Table))
Table UserCustomer (UserId, CustomerId)

Only User or User's Manager assigned to Customers can see the Customer.

It should be like this.

User1 has Manager UserM1
User2 has Manager UserM2
User3 has Manager UserM3
UserM1 has Manager UserMM1
UserM2,M3 has Manager UserMM2
UserMM1, UserMM2 has Manager UserMMM
UserMMM can have its Manager as well and so and so.

Note: Recursive relationship of User and User's Manager could be deep like 10 levels.

So technically, any customers assigned to the User under the User Manager can see the client.

How can I write this dynamic condition in C#.

======= Additonal explaination ========

I have 10 clients = I can only view 10 clients
Pogba have 5 clients = He can view 5 clients.
Paul is my manager and he also has 5 clients = He can view 15 clients
Logan is Pual Manager and he also has 10 clients = He can view 25 clients (Me, Paul, and Logan clients)
Henry is Pogba and Logan Manager = He can view 30 clients (25 client from Logan and Logan's staff + 5 clients from Pogba )

so and so.

My attempted alogoritm but it could only accomulate 2 level deep.

Im going to create helper class. (CustomerAccessControlHelper.cs)

  public static class CustomerAccessControlHelper.cs
    {
    public static List<Customer> GetAccessClients(int userId)
{

var userListsUnderCurrentUser = new List<User>();

 var listOfAssignedStaffsLevel1 = _context.Users.Where(x => x.ManagerId == userId);
userListsUnderCurrentUser.AddRange(listOfAssignedStaffsLevel1);

foreach(var user in listOfAssignedStaffs)
{
   var listOfAssignedStaffsLevel2 =  _context.Users.Where(x => x.ManagerId == user.Id);
   userListsUnderCurrentUser.AddRange(listOfAssignedStaffsLevel1);

}

}
    
    }

Solution

Of course you can recursively query database and find all users under a manager and then find all clients he can access. but it's not optimized solution.

Another way is to load every users in singleton or static list and create tree in memory.

But I suggest you use another column to your table and keep hierarchy of managers. This is called path enumeration.

Id	Username	ManagerId	ManagerHierarchy
1	User1	4	/0/9/7/4/
2	User2	5	/0/9/8/5/
3	User3	6	/0/9/8/6/
4	UserM1	7	/0/9/7/
5	UserM2	8	/0/9/8/
6	UserM3	8	/0/9/8/
7	UserMM1	9	/0/9/
8	UserMM2	9	/0/9/
9	UserMMM	NULL	/0/

now imagine we want list of clients that UserMMM can see.

var userId = 9;
dbContext.Customers.Where(item => item.UserCustomers.Any(uc => uc.User.ManagerHierarchy.Contains($"/{userId}/") || uc.UserId == userId))

I encourage you to take a look at this link https://www.databasestar.com/hierarchical-data-sql/

For keeping ManagerHierarchy updated you need to calculate it's value when you add, edit or delete a user.

Add

var managerId = 1;
var manager = dbContext.Users.First(item => item.id == managerId);
var user = new User()
{
    ManagerHierarchy = $"{manager.ManagerHierarchy}{managerId}/",
    ManagerId = managerId
};

Delete

var userIdToDelete = 1;
var usersHasThisUserAsManager = dbContext.Users.Where(item => item.ManagerHierarchy.Contains($"/{userIdToDelete}/").ToList();
foreach(var u in usersHasThisUserAsManager)
    u.ManagerHierarchy = u.ManagerHierarchy.Replace($"/{userIdToDelete}/","/");

Edit ManagerId

var user =; // Some user you get from database and need to update it's managerId column
var newManagerId = 1;
var newManager = dbContext.Users.First(item => item.id == managerId);

var usersNeedToUpdateManagerHierarchy = dbContext.Users.Where(item => item.ManagerHierarchy.StartsWith($"{user.ManagerHierarchy}{user.Id}/").ToList();

foreach(var u in usersNeedToUpdateManagerHierarchy)
    u.ManagerHierarchy = u.ManagerHierarchy.Replace($"{user.ManagerHierarchy}{user.Id}/",$"{newManager.ManagerHierarchy}{user.Id}/")