How to return Joined database Tables in Rest api

Question

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

Sachin Singh · Answer

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/

Sachin Singh · Answer

instead of anonymous object use AllWorkerDTO and then return the result directly (userRole,Role)=> new AllWorkerDTO { // properties }

Jignesh Kumar · Answer

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 zosar · Answer

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(); }

How to return Joined database Tables in Rest api

Answers (4)