I need to do a query on a returned list of users from another query, by Id

Question

Currently, I have a service that grabs user information from the User table. The users can be created by admins or an employee and all of these employees have their own Id. So with that in mind, there is a column called CreatedBy which holds the id of this admin, or the user, that of which's name I have to return. So far I've pulled the user model but now I need to create the part where I pull the user's name with the user.Id in the CreatedBy

This is what I have pulling from my database tables Users and Company and the query parameters are just a name or company name

public async Task<List<ApplicationUser>> SearchUsers(UserSearchDto userSearchDto)
{
    userSearchDto.FirstName ??= string.Empty;
    userSearchDto.LastName ??= string.Empty;
    userSearchDto.CompanyName ??= string.Empty;

    return await _locationDbContext.Users
    .Include(nameof(Company))
    .Where(user => user.FirstName.Contains(userSearchDto.FirstName) 
    && user.LastName.Contains(userSearchDto.LastName) 
    && user.Company.Company_Name.Contains(userSearchDto.CompanyName))
    .ToListAsync();
}

So within this list that I am returning I'm trying to do another query to grab more user information based on the CreatedBy id's returned in the first service to bring back the name of those users with the id's in CreatedBy.

var userDtos = _mapper.Map<List<ApplicationUser>, List<UserDetailsDto>>(users);

foreach (UserDetailsDto user in userDtos)
{
    user.CreatedByName = await _userService
        .SearchUsers(userDtos.Where(user.Id == user.CreatedBy))
}

I feel like this is a possible solution so far but I'm not sure where or how I would pull that because this solution is giving me an error at the point where I use the ".Where" statement. Maybe if I could create another service that would return the user by Id instead and use the createdby Id to pull the name but nothing like that exists yet. The model I'd like to return is also a bit different from the model representing the Users table as ApplicationUser has the CreatedBy which is an Id but the returned model, userDetailsDto will have a name string property as well that I will try and assign here in automapper. If I can think of how I can assign the name by the Id.

CreateMap<ApplicationUser, UserDetailsDto>()
.ForMember(dest => dest.CompanyName,
           opts => opts.MapFrom(src => src.Company.Company_Name));

Answer 1

Ideally this is something that you should be able to resolve using navigation properties. If your User table uses CreatedBy to represent the CreatedBy User ID then you could adjust your mapping to facilitate a CreatedBy navigation property:

public class User
{
    public class UserId { get; set; }

    // ...

    public virtual User CreatedBy { get; set; }
}

Then in the mapping use a shadow property for the FK association: (in OnModelCreating or using an EntityTypeConfiguration)

EF Core

.HasOne(x => x.CreatedBy)
.WithMany()
.HasForeignHey("CreatedBy") // Property on Users table
.Required();

EF 6

.HasRequired(x => x.CreatedBy)
.WithMany()
.Map(x => x.MapKey("CreatedBy")) // Property on Users table

Alternatively if you want the CreatedBy FK accessible in the User table, map it as something like CreatedByUserId:

public class User
{
    public int UserId { get; set; }

    // ...
    [ForeignKey("CreatedBy"), Column("CreatedBy")]
    public int CreatedByUserId { get; set; }
    public virtual User CreatedBy { get; set; }
}

Now when you go to search for your users, you can project your CreatedBy user ID and Name in one go.

When it comes to optional search parameters you should keep the conditionals (if/else/ null checks etc ) outside of the Linq wherever possible. This helps compose more efficient queries rather than embedding conditional logic into the SQL.

public async Task<List<ApplicationUserViewModel>> SearchUsers(UserSearchDto userSearchDto)
{
    var query = _locationDbContext.Users.AsQueryable();

    if (!string.IsNullOrEmpty(userSearchDto.FirstName))
        query = query.Where(x => x.FirstName.Contains(userSearchDto.FirstName));
    if (!string.IsNullOrEmpty(userSearchDto.LastName))
        query = query.Where(x => x.LastName.Contains(userSearchDto.LastName));
    if (!string.IsNullOrEmpty(userSearchDto.CompanyName))
        query = query.Where(x => x.Company.Name.Contains(userSearchDto.CompanyName));

    return await query.ProjectTo<ApplicationUserViewModel>(_config)
        .ToListAsync();
}

Where _config reflects an automapper MapperConfiguration containing the details on how to map a User to your desired view model. If you're not leveraging Automapper you can accomplish this using Select to project the values. Other considerations there would be to consider using StartsWith instead of Contains, perhaps offering an option to perform a more expensive Contains search... Also adding things like minimum search length checks (I.e. 3+ characters) and pagination or result row limits (I.e. Take(50)) to avoid outrageous search requests from hammering your system. (I.e. searching for users with "e" in the first name)

That view model might have UserId, UserName, CompanyName, then things like CreatedByUserId, CreatedByName. To resolve the CreatedBy details you just reference u.CreatedBy.UserId and u.CreatedBy.Name either in the Automapper config or within your Select(u => new ApplicationUserViewModel { ... }) statement.