CODEWITHSUNDEEP
c#linq
Kaan Taze
Kaan Taze

Reputation: 1136

Select All Columns and return a given type in EF Core, .NET Core

[Solved]

return await _context.AspNetUsers.Where( x => x.AspNetUserRoles.Count() == 0).ToListAsync();

does the job, returns the AspNetUsers object as I wanted. Mustafa Arslan's answer also work for only userId

[Original Problem]

I have two tables AspNetUsers and AspNetUserRoles.

AspNetUsers table has many columns but AspNetUserRoles has two: userId and roleId;

I want to query that every user without a role and call it from my controller.

Query

This gives the results that I want

SELECT *
FROM AspNetUsers
LEFT JOIN AspNetUserRoles ON AspNetUsers.Id = AspNetUserRoles.UserId
WHERE AspNetUserRoles.RoleId IS NULL

Here is my method in controller who suppose to return the list of AspNetUsers but Join gives and error saying that:

The type arguments for method 'Queryable.Join<TOuter, TInner, TKey, TResult>(IQueryable<TOuter>, IEnumerable<TInner>, Expression<Func<TOuter, TKey>>, Expression<Func<TInner, TKey>>, Expression<Func<TOuter, TInner, TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Controller

[HttpGet]
[Route("Group")]
public async Task<ActionResult<IEnumerable<AspNetUsers[]>>> GetAspNetUsersWithGroups()
{
    return await _context.AspNetUsers.Join(_context.AspNetUserRoles,
        users => users.Id,
        roles => roles.userId,
        (AspNetUsers) => new { Id = roles.Id });  // This is where I think the error is
}

What's the correct way to write this?

Upvotes: 1

Views: 1206

Answers (1)

Mustafa Arslan
Mustafa Arslan

Reputation: 794

I think you want to list role ids.

    [HttpGet]
    [Route("Group")]
    public async Task<ActionResult<IEnumerable<string>>> GetAspNetUsersWithGroups()
    {
        return await _context.Users.Join(_context.UserRoles,
            users => users.Id,
            roles => roles.UserId,
            (user, userRole) => userRole.RoleId).ToListAsync(); 
    }

Upvotes: 1

Related Questions