question

osyris-3187 avatar image
0 Votes"
osyris-3187 asked osyris-3187 commented

Using Join and Lazy Loading

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

dotnet-aspnet-core-mvcdotnet-aspnet-core-webapidotnet-aspnet-core-generaldotnet-aspnet-datadotnet-aspnet-spa
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Assuming this is EF Core, did you configure lazy loading? It's not real clear why you need lazy loading though...

It's very unusual, and nonstandard, to submit a ViewModel in an HTTP GET. More than likely, this is the reason for the 500 error. 500 errors are commonly due a improperly formatted client request. Follow standards and switch to a POST or use a standard querystring if you want to stay with HTTP GET.

Have you tried setting a break point to verify the action executes???

Lastly, seems like a lot of code to query user and roles. I would write a quick test to make sure the logic returns expected results.

0 Votes 0 ·

Thank you for the reply

When i do this it works perfetcly:


 [HttpGet("AllWorkers")]
         public async Task<IEnumerable<AllWorkersDto>> AllWorkers()
         {
             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();
    
 return await  DbData;
         }


But I also want to filter it from the Client side (Reactjs ) I am using a Rest API.
I have used IQueryable for lazy loading before so i can filter the database results in this example:

 [HttpGet("admin/All-Products")]
         public async Task<IEnumerable<Products>> AdminGetProducts([FromQuery] ProductSearchDto dto)
         {
             IQueryable<Products> products = _dbContext.Products;
    
             if (!string.IsNullOrWhiteSpace(dto.Name))
                 products = products.Where(x => x.Name.ToLower().Contains(dto.Name.ToLower()));
    
 // some more filters...
    
    
             return await products.ToListAsync();
         }

but combiding both seems more diffecult.




0 Votes 0 ·
ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered osyris-3187 commented

Hi @osyris-3187,

  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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much this really helped me.

So if i understand it correclty whenever a returning variable (in this case "var data")
returns more than one type of data, whe need to use "Task.FromResult" in order to get the "Results view" data
is that correct?

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

osyris-3187 avatar image
0 Votes"
osyris-3187 answered osyris-3187 commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @osyris-3187,

From your description, before filtering, you can get the data successfully, if that is the case, the issue relates the filter part. You can set a break point in the filter part and step by step to debug the code, to find which line of code will show the exception? It helps you narrow down the problem more easily.

0 Votes 0 ·

Thanks for your reply

if that is the case, the issue relates the filter part

That is what i thought as well.

I have set a breaking point

If you take a look at the full code on my first post I saw that by debugging "var DbData" contains all the correct data

 IQueryable<AllWorkersDto> data =  DbData.AsQueryable();

the "data" variable contains a blue colored" Non-public members" map with the number of enumarbles( wich is 2) and some other info and
there is a purple colored "result" map that does contain the correct data the same data as "var DbData"

132243-result-view.png

132175-non-public-members.png


0 Votes 0 ·
result-view.png (41.8 KiB)