I have many to many relationship between entities User-UsersProjects-Project.

I'd like to get mapped list of users assigned to the specified (by projectID) project with information about number of projects assigned to the user generally, not per project.

Please see my code

Controller

        [HttpGet("{projectId}/users-assigned")]
        [Description("Get list of users assigned to a specified project.")]
        public async Task<ActionResult> GetProjectUsers(int projectId)
        {
            if (!ProjectExistById(projectId)) return NotFound("Project doesn't exist");
            var project = await _context.Projects.Include(x => x.UserProjects).ThenInclude(z => z.User).SingleOrDefaultAsync(x => x.ProjectId == projectId);
            var users = project.UserProjects.Select(x => x.User).ToList();

            var usersDto = _mapper.Map<ICollection<UserForProjectDTO>>(users);
            return Ok(usersDto);
        }

Entites

public class AppUser : IdentityUser<int>
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public ICollection<AppUserProject> UserProjects { get; set; }
    }


public class Project
    {
        [Key]
        public int ProjectId { get; set; }
        public string Number { get; set; }
        public string Name { get; set; }
        public ICollection<AppUserProject> UserProjects { get; set; }

    }

public class AppUserProject
    {
        [Key]
        public int UserId { get; set; }
        public AppUser User { get; set; }


        [Key]
        public int ProjectId { get; set; }
        public Project Project { get; set; }
    }

DTO

    public class UserForProjectDTO
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int ProjectsCount { get; set; }

    }

AutoMapperProfiles

    CreateMap<AppUser, UserForProjectDTO>();
    CreateMap<AppUserProject, UserForProjectDTO>();

Current Result /api/Projects/11/users-assigned

[
    {
        "id": 4,
        "firstName": "Robert",
        "lastName": "Lewandowski",
        "projectsCount": 0
    },
    {
        "id": 6,
        "firstName": "Ernest",
        "lastName": "Giovannni",
        "projectsCount": 0
    },
    {
        "id": 7,
        "firstName": "Mark",
        "lastName": "Looser",
        "projectsCount": 0
    },
    {
        "id": 8,
        "firstName": "Frank",
        "lastName": "Lampard",
        "projectsCount": 0
    },
    {
        "id": 9,
        "firstName": "Mark",
        "lastName": "Ruffalo",
        "projectsCount": 0
    }
]

Expected result

[
    {
        "id": 4,
        "firstName": "Robert",
        "lastName": "Lewandowski",
        "projectsCount": 4
    },
    {
        "id": 6,
        "firstName": "Ernest",
        "lastName": "Giovannni",
        "projectsCount": 2
    },
    {
        "id": 7,
        "firstName": "Mark",
        "lastName": "Looser",
        "projectsCount": 2
    },
    {
        "id": 8,
        "firstName": "Frank",
        "lastName": "Lampard",
        "projectsCount": 1
    },
    {
        "id": 9,
        "firstName": "Mark",
        "lastName": "Ruffalo",
        "projectsCount": 1
    }
]

My Database

Now I am getting always 0 or 1 (depend on how I map but it is still not what I expect).

The problem is because i want to get list of users with project qty by project Id, not for all projects. This is first time i facing with this kind of problem. Any hint will be useful.

CodePudding user response：

I don't think that you need AutoMapper here. It's one-time query and you can project needed properties by yourself without third-party library.

var users = 
    from p in context.Projects
    where p.ProjectId == projectId
    from up in p.UserProjects
    select new UserForProjectDTO
    {
        Id = up.UserId,
        FirstName = up.User.FirstName,
        LastName = up.User.LastName,
        ProjectsCount = up.User.UserProjects.Count()
    };

As benefit you have faster query which loads only needed fields without overhead.