C#•2y ago

❔ Querying Department Names using DepartmentID from User Queries

I'm attempting to query users based on their status, but I also want to display the department name associated with each user in the result. To accomplish this, I plan to use the department ID provided in the user query and perform a subsequent query to retrieve the associated department name. What approach would you recommend for achieving this?

public class GetUserByStatus
{
    public class GetUserByStatusQuery : IRequest<IEnumerable<GetUserByStatusResult>>
    {
        public bool IsActive { get; set; }
    }

    public class GetUserByStatusResult
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public bool IsActive { get; set; }
        public string DepartmentName { get; set; }
        public string DateAdded { get; set; }
        public string AddedBy { get; set; }
        public string ModifiedBy { get; set; }
        public string Reason { get; set; }
    }

    public class Handler : IRequestHandler<GetUserByStatusQuery, IEnumerable<GetUserByStatusResult>>
    {
        private readonly IServiceManager _serviceManager;
        private readonly IMapper _mapper;

        public Handler(IServiceManager serviceManager, IMapper mapper)
        {
            _serviceManager = serviceManager;
            _mapper = mapper;
        }

        public async Task<IEnumerable<GetUserByStatusResult>> Handle(GetUserByStatusQuery request,
            CancellationToken cancellationToken)
        {
            var user = await _serviceManager.User.GetUserByStatus(request.IsActive);
            if (user == null)
                throw new NoUserFoundException();

            var result = _mapper.Map<IEnumerable<GetUserByStatusResult>>(user);
            return result;
        }
    }
}

public class GetUserByStatus
{
    public class GetUserByStatusQuery : IRequest<IEnumerable<GetUserByStatusResult>>
    {
        public bool IsActive { get; set; }
    }

    public class GetUserByStatusResult
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public bool IsActive { get; set; }
        public string DepartmentName { get; set; }
        public string DateAdded { get; set; }
        public string AddedBy { get; set; }
        public string ModifiedBy { get; set; }
        public string Reason { get; set; }
    }

    public class Handler : IRequestHandler<GetUserByStatusQuery, IEnumerable<GetUserByStatusResult>>
    {
        private readonly IServiceManager _serviceManager;
        private readonly IMapper _mapper;

        public Handler(IServiceManager serviceManager, IMapper mapper)
        {
            _serviceManager = serviceManager;
            _mapper = mapper;
        }

        public async Task<IEnumerable<GetUserByStatusResult>> Handle(GetUserByStatusQuery request,
            CancellationToken cancellationToken)
        {
            var user = await _serviceManager.User.GetUserByStatus(request.IsActive);
            if (user == null)
                throw new NoUserFoundException();

            var result = _mapper.Map<IEnumerable<GetUserByStatusResult>>(user);
            return result;
        }
    }
}

17 Replies

Connor•2y ago

I’d need to see more code. This more depends on how you’re DbContext, relationships, models, and current EF query are set up.

Jimmacle•2y ago

if there's a relationship between a user and a department that would probably be modeled in EF and you can just select the name out as part of the query if you're not using EF, then a join in the same query instead of multiple queries

_vegabyte_OP•2y ago

This is my Departments Model

namespace VerticalSliceArch.Data
{
    public class Departments
    {
        public int Id { get; set; }
        public string DepartmentName { get; set; }
        public DateTime DateAdded { get; set; }
        public string AddedBy { get; set; }
        public bool IsActive { get; set; }
        public string Reason { get; set; }
    }
}

namespace VerticalSliceArch.Data
{
    public class Departments
    {
        public int Id { get; set; }
        public string DepartmentName { get; set; }
        public DateTime DateAdded { get; set; }
        public string AddedBy { get; set; }
        public bool IsActive { get; set; }
        public string Reason { get; set; }
    }
}

This is the User

public class Users
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public bool IsActive { get; set; }
        
        public Departments Department { get; set; }
        public int DepartmentId { get; set; }
        public DateTime DateAdded { get; set; }
        public string AddedBy { get; set; }
        public string ModifiedBy { get; set; }
        public string Reason { get; set; }
    }

public class Users
    {
        public int Id { get; set; }
        public string FullName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public bool IsActive { get; set; }
        
        public Departments Department { get; set; }
        public int DepartmentId { get; set; }
        public DateTime DateAdded { get; set; }
        public string AddedBy { get; set; }
        public string ModifiedBy { get; set; }
        public string Reason { get; set; }
    }

This the query

public async Task<IEnumerable<Users>> GetUserByStatus(bool status)
        {
            return await _context.User.Where(x => x.IsActive == status)
                .ToListAsync();
        }

public async Task<IEnumerable<Users>> GetUserByStatus(bool status)
        {
            return await _context.User.Where(x => x.IsActive == status)
                .ToListAsync();
        }

Jimmacle•2y ago

also, i don't recommend wrapping your dbcontext in a repository a dbcontext is already a repository

_vegabyte_OP•2y ago

All query should be on dbcontext?

Jimmacle•2y ago

it actually looks like you might have a repository in a service locator which is very sus to me yes, there's no reason to wrap a dbcontext in another abstraction if you used the context directly it would be trivial to include the department name in the query and map it directly to your DTO

_vegabyte_OP•2y ago

Yes. they are on a Service class

Jimmacle•2y ago

that sounds like multiple layers of "don't do that" why do you need a service locator when you already have DI?

_vegabyte_OP•2y ago

Any possible way? Okay, I will take note that. Thank you!

Jimmacle•2y ago

here's an example from one of my projects

    public async Task<Result<ProjectDetailsDto>> Handle(GetProjectDetailsQuery request, CancellationToken cancellationToken)
    {
        var details = await _db.Projects.AsNoTracking()
            .Include(p => p.Handler)
            .Include(p => p.Contact)
            .ThenInclude(p => p.Customer)
            .Where(p => p.Number == request.Number)
            .Select(p => new ProjectDetailsDto(
                p.Number,
                p.Status,
                p.Title,
                p.Description,
                p.BillingType,
                p.CreatedOn,
                p.StartedOn,
                p.ClosedOn,
                p.FlexProjectNumbers,
                p.OracleOrderNumbers,
                p.LabWork,
                p.QuotePriceDollars,
                p.QuotePriceOther,
                p.Handler,
                p.Contact,
                _db.Expenses.Where(x => x.ProjectNumber == p.Number && x.BillToCustomer).Sum(x => x.AmountUsd),
                _db.Expenses.Where(x => x.ProjectNumber == p.Number).Sum(x => x.AmountUsd)))
            .FirstOrDefaultAsync(cancellationToken);

        return details ?? Result<ProjectDetailsDto>.Fail("Project not found.");
    }

    public async Task<Result<ProjectDetailsDto>> Handle(GetProjectDetailsQuery request, CancellationToken cancellationToken)
    {
        var details = await _db.Projects.AsNoTracking()
            .Include(p => p.Handler)
            .Include(p => p.Contact)
            .ThenInclude(p => p.Customer)
            .Where(p => p.Number == request.Number)
            .Select(p => new ProjectDetailsDto(
                p.Number,
                p.Status,
                p.Title,
                p.Description,
                p.BillingType,
                p.CreatedOn,
                p.StartedOn,
                p.ClosedOn,
                p.FlexProjectNumbers,
                p.OracleOrderNumbers,
                p.LabWork,
                p.QuotePriceDollars,
                p.QuotePriceOther,
                p.Handler,
                p.Contact,
                _db.Expenses.Where(x => x.ProjectNumber == p.Number && x.BillToCustomer).Sum(x => x.AmountUsd),
                _db.Expenses.Where(x => x.ProjectNumber == p.Number).Sum(x => x.AmountUsd)))
            .FirstOrDefaultAsync(cancellationToken);

        return details ?? Result<ProjectDetailsDto>.Fail("Project not found.");
    }

i actually think those includes are unnecessary, just leftovers from a previous version (it should auto-include the navigation properties used in the Select call)

_vegabyte_OP•2y ago

Hmmm, I'll try to understand your code. Thank your providing from my reference. I will transfer all my query inside DbContext first

public async Task<IEnumerable<AllUserByStatusResult>> Handle(AllUserByStatusQuery request, CancellationToken cancellationToken)
        {
             var user = await _serviceManager.User.GetUserByStatus(request.IsActive);
             if (!user.Any())
                throw new NoUserFoundException();
             var results = _mapper.Map<IEnumerable<AllUserByStatusResult>>(user).ToList();
             
             foreach (var result in results)
             {
                 var department = await _serviceManager.Department.GetAllDepartmentsById(result.DepartmentId);
                 result.DepartmentName = department.DepartmentName;
             }

             return results;
        }

public async Task<IEnumerable<AllUserByStatusResult>> Handle(AllUserByStatusQuery request, CancellationToken cancellationToken)
        {
             var user = await _serviceManager.User.GetUserByStatus(request.IsActive);
             if (!user.Any())
                throw new NoUserFoundException();
             var results = _mapper.Map<IEnumerable<AllUserByStatusResult>>(user).ToList();
             
             foreach (var result in results)
             {
                 var department = await _serviceManager.Department.GetAllDepartmentsById(result.DepartmentId);
                 result.DepartmentName = department.DepartmentName;
             }

             return results;
        }

How about this one ?

Angius•2y ago

Protip: you don't need includes when you do a select

Jimmacle•2y ago

yeah i noticed the leftovers when i copied and pasted it here LUL

Saber•2y ago

bad. Tons of extra queries to the database

Jimmacle•2y ago

also: still using a service locator inside a DI container and wrapping the dbcontext in a repository just inject the dbcontext directly and then you can write efficient queries

_vegabyte_OP•2y ago

How can I check if the Department is Available?

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Querying Department Names using DepartmentID from User Queries