I'm trying to retrieve all users with roles via Dapper - here is the method and query:

public async Task<List<User>> GetUserWithRolesAsync()
{
    using var dbConnection = _context.CreateConnection();

    var sql = @"select u.Id, u.FirstName, u.LastName, r.Id, r.Name
                from User u 
                inner join UserRole ur on ur.UserId = u.Id
                inner join Role r on r.Id = ur.RoleId";

    // this line throws exception;
    var users = await dbConnection.QueryAsync<User, Role, User>(sql, (user, role) =>
        {
            user.Role.Add(role);
            return user;
        }, splitOn: "Id");

    var result = users.GroupBy(p => p.Id).Select(g =>
        {
            var groupedUser = g.First();
            groupedUser.Role = g.Select(p => p.Role.Single()).ToList();
            return groupedUsers;
        });

    return result.ToList();
}

This is the error I get:

Incorrect syntax near the keyword 'User'

How can I fix it?

In case of need entity details, here are the table definitions:

public class User : SqlEntityBase
{
    public string FirstName { get; set; }...
    public List<Role> Role { get; set; }
}

public class Role : SqlEntityBase
{
    public string Name { get; set; }
    public List<User> User { get; set; }
}

public class UserRole : SqlEntityBase
{
    public int UserId { get; set; }
    public int RoleId { get; set; }
    public User User { get; set; }
    public Role Role { get; set; }
}

CodePudding user response：

Assuming you're using SQL Server as your database - User is a reserved T-SQL keyword. You should try to avoid using those reserved keywords for your own db objects - use more descriptive, more appropriate names.

If you can't change it - then you must put that table name into square brackets (or double quotes) in your SQL:

var sql = @"select u.Id, u.FirstName, u.LastName, r.Id, r.Name
            from [User] u 
            inner join UserRole ur on ur.UserId = u.Id
            inner join Role r on r.Id = ur.RoleId";