Is there some way with dapper object in object to load in sfdatagrid?

Question

I am using Dapper and when i execute query and load to SfDataGrid only show results from first table.

Model:

public class Users
{
    public int id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public Roles role { get; set; }
}

public class Roles
{
    public int id { get; set; }
    public string name { get; set; }
}

So when i do like

sfDataGrid1.DataSource = results; 

Its only show user id, name, email without role name.

Query:

select a.*, b.name from users a inner join roles b on users.roleID = roles.id.

Tables users: id, name, email, roleID.

Table roles: id, name.

EDIT:

public List<Users> GetUsers() { 
    using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("db2021"))) { var sql = @"SELECT A.*, B.* FROM users A INNER JOIN Roles B on B.id = A.roleID"; 
    var result = connection.Query<Users>(sql, (a, b) => { a.role = b; return a; }).ToList(); return result; } 
    }

List<Users> users = new List<Users>(); 
users = GetUsers(); 
sfDataGrid1.DataSource = users;

Answer 1

Supposing you have a class Roles like this one

public class Roles
{
    public int id {get;set;}
    public string name {get;set;}

}

Then your query should be

public List<Users> GetUsers() { 
    using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("db2021"))) { 
        var sql = @"SELECT A.*, B.* FROM users A INNER JOIN Roles B on B.id = A.roleID"; 
        var result = connection.Query<Users,Roles,Users>(sql, (a, b) => 
        { 
             a.role = b; 
             return a; 
        }).ToList(); 
        return result; 
   } 
}


List<Users> users = GetUsers(); 
sfDataGrid1.DataSource = users;

In other words you need to tell Dapper what is the class that represent the B.* data and what is the return value from the lambda.

If you instead want a single class that has both the user and just the role name data then you need to define this particular class as

public class UsersViewModel
{
    public int id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public int roleid {get;set;}
    public string rolename {get;set;}
}

And the query should change to retrieve the data in this way

public List<Users> GetUsers() 
{ 
    using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(....))
    { 
        var sql = @"SELECT A.*, B.name as rolename 
                    FROM users A INNER JOIN Roles B 
                                 ON B.id = A.roleID"; 
        var result = connection.Query<UsersViewModel>(sql).ToList(); 
        return result; 
   } 
}

Notice how this has no more a one-to-one mapping to the database fields hence we have a ViewModel kind of class and we name it appropriately to avoid confusion.

Also with dapper it is important to have the same name for properties and columns extracted by the query; so the name column in the role table appears as rolename in the query. This will allow dapper to build the UserViewModel class instances from the query's resultset.