How to return Joined database Tables in Rest api

osyris zosar

5y817Learn iOS Programming

I want to return data from 3 Tables to the client side:.
I cannont seems to upload the code

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Lazy loading means loading the related data on demand.
Lazy loading is enabled by default in Entity Framework, whatever we do we do for eager loading.
means
var res= context.Users; // here Role for each user is null
foreach(var r in res)
{
r.Role // this is called lazy loading

}
what you are trying is called deferred execution where we extend the query to store different results in different variables.
At the same time, you want, filtering should be on the server (By SQL server engine) and not in-memory so you are using AsQueryable.
so, you are going correct, however, I don't know what actually you want to achieve.
you should check this article

https://tech-mint.com/programming-languages/csharp/ienumerable-iqueryable-how-query-works/
Jignesh Kumar5y ago
Hi Osyris,

Please use try this one,
[HttpGet("AllWorkers")]
public async Task> AllWorkers([FromQuery]AllWorkersFilterDto dto)
{
    var DbData = _dbContext.UserRoles.Join(
        _dbContext.Roles,
        userRoles => userRoles.RoleId,
        Role => Role.Id,
        (userRoles, Role) => new
        {
            userId = userRoles.UserId,
            rolename = Role.Name,
            roledescription = Role.Description,
        }).Join(_dbContext.Users,
        Roles => Roles.userId,
        Users => Users.Id,
        (Roles, Users) => new AllWorkersDto
        {
            id = Users.Id.ToString(),
            Name = Users.FirstName,
            LastName = Users.LastName,
            Email = Users.Email,
            Role = Roles.rolename,
        });

    if (!string.IsNullOrWhiteSpace(dto.Name))
        data = data.Where(x => x.Name.ToLower().Contains(dto.Name.ToLower())).ToListAsync();

    if (!string.IsNullOrWhiteSpace(dto.LastName))
        data = data.Where(x => x.LastName.ToLower().Contains(dto.LastName.ToLower())).ToListAsync();

    if (!string.IsNullOrWhiteSpace(dto.LastName))
        data = data.Where(x => x.LastName.ToLower().Contains(dto.LastName.ToLower())).ToListAsync();

    if (!string.IsNullOrWhiteSpace(dto.Email))
        data = data.Where(x => x.Email.ToLower().Contains(dto.Email.ToLower())).ToListAsync();

    if (!string.IsNullOrWhiteSpace(dto.Role))
        data = data.Where(x => x.Role.ToLower().Contains(dto.Role.ToLower())).ToListAsync();

    return await data;
}
osyris zosar5y ago
Thank you, Sachin Singh

I have another question: how can i use lazy loading to filter that same custom List
I have tryed to do this:
[HttpGet("AllWorkers")]
        public async Task> AllWorkers([FromQuery]AllWorkersFilterDto dto)
        {
            var DbData = _dbContext.UserRoles.Join(
                _dbContext.Roles,
                userRoles => userRoles.RoleId,
                Role => Role.Id,
                (userRoles, Role) => new
                {
                    userId = userRoles.UserId,
                    rolename = Role.Name,
                    roledescription = Role.Description,
                }).Join(_dbContext.Users,
                Roles => Roles.userId,
                Users => Users.Id,
                (Roles, Users) => new AllWorkersDto
                {
                    id = Users.Id.ToString(),
                    Name = Users.FirstName,
                    LastName = Users.LastName,
                    Email = Users.Email,
                    Role = Roles.rolename,
                }).ToListAsync();

            IQueryable data = DbData.Result.AsQueryable();

            if (!string.IsNullOrWhiteSpace(dto.Name))
                data = data.Where(x => x.Name.ToLower().Contains(dto.Name.ToLower()));

            if (!string.IsNullOrWhiteSpace(dto.LastName))
                data = data.Where(x => x.LastName.ToLower().Contains(dto.LastName.ToLower()));

            if (!string.IsNullOrWhiteSpace(dto.LastName))
                data = data.Where(x => x.LastName.ToLower().Contains(dto.LastName.ToLower()));

            if (!string.IsNullOrWhiteSpace(dto.Email))
                data = data.Where(x => x.Email.ToLower().Contains(dto.Email.ToLower()));

            if (!string.IsNullOrWhiteSpace(dto.Role))
                data = data.Where(x => x.Role.ToLower().Contains(dto.Role.ToLower()));

            return await data.ToListAsync();
        }
Sachin Singh5y ago
instead of anonymous object use AllWorkerDTO and then return the result directly
(userRole,Role)=> new AllWorkerDTO
{

// properties
}