Using Join and Lazy Loading

Question

I have merged 3 tables together , now I would like to filter that new table using lazy loading
so i can filter the data with search inputs

C# code:

[HttpGet("AllWorkers")]
        public async Task<IEnumerable<AllWorkersDto>> 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().Result;

            IQueryable<AllWorkersDto> data = DbData.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.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();
        }

it is returning a 500 error, can someone help me with this

Answer 1

Hi @osyris ,

 return await data.ToListAsync();  

The issue might be related the above code, I create a sample to test your code, when using the ToListAsync() method, it will show the following exception:

The source 'IQueryable' doesn't implement ''. Only sources that implement 'IAsyncEnumerable' can be used for Entity Framework asynchronous operations.

Then, if I change the ToListAsync() method to ToList() method, everything works well on my side.

You can refer the following code and try to use return await Task.FromResult(data.ToList());.

    public async Task<IEnumerable<AllWorkersFilterDto>> GetData()  
    {   
        //  
        var DbData = _userManager.Users.Select(c => new AllWorkersFilterDto  
        {  
            Id = c.Id.ToString(),  
            Name = c.UserName,  
            LastName = c.UserName,  
            Email = c.Email,  
            Role = "User",  
        }).ToListAsync().Result;  

        var data = DbData.AsQueryable();  
        var model = new { Name = "m H", GPA = "A" };  

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

        //the following is used to check whether the data contains value or not.  
        var result = data.ToList();   
        var count = result.Count;  

        return await Task.FromResult(data.ToList());  
    }  

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Answer 2

you don't check if dto is null, which it probably is this being a get and its an object. does the class AllWorkersFilter have the [FromQuery(<parameter Name>)] for each property?

also why the sync call from an async method? pretty poor coding.

Answer 3

The AllWorkersFilter is exaclty the same as the Query string parameters:

public class AllWorkersFilterDto
    {
        public string Name { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Role { get; set; }
    }

I have checked if its null or whitespace that worked with my last example as well.

 if (!string.IsNullOrWhiteSpace(dto.Name))

but even when the filter would not filter well it would still show all the results and not give a 500 error

I am trying to understand how I can combine multiple Database tables and at the same time filter them
It would help me out if someone give me an example on how to code this properly.