How do I return a generic list with QueryOver

Question

I'm trying to do a simple select with queryover and get a generic list.

I want to get from the table only username, email and firstname and omit the rest.

This is my code:

public IList<Users> GetAllByRole(string role)
{
    var users= this.Session.QueryOver<Users>()
        .where(f => f.role == role)
        .select(f => f.username)
        .select(f => f.email)
        .select(f => f.firstname)
        .list<Users>();

    return users;
}

Error:

The value \"x\" is not of type \"Model.Poco.Entities.Users\" and cannot be used in this generic collection.\r\nParameter name: value

SQL Query would be something like this

SELECT [username]
  ,[email]
  ,[firstname]
FROM [MyDB].[dbo].[MyTable]
WHERE [role] = 'admin'

I also tried something like this

IList<Users> users = this.Session.QueryOver<Users>()
            .Where(p => p.role == role)
            .SelectList(list => list
                    .Select(p => p.username)
                    .Select(p => p.email)
                    .Select(p => p.firstname)
            )
            .List<Users>();
return users;

Error:

The value \"System.Object[]\" is not of type \"Poco.Entities.Users\" and cannot be used in this generic collection.\r\nParameter name: value

Answer 1

We need to add 1) aliases to each column and use 2) transformer:

// Users u - will serve as an alias source below
Users u = null;
IList<Users> users = this.Session.QueryOver<Users>()
        .Where(f => f.role == role)
        .SelectList(list => list        // here we set the alias 
                .Select(p => p.username) .WithAlias(() => u.username)
                .Select(p => p.email)    .WithAlias(() => u.email)
                .Select(p => p.firstname).WithAlias(() => u.firstname)
        )
        // here we Transform result with the aliases into Users
        .TransformUsing(Transformers.AliasToBean<Users>())
        .List<Users>();