How to populate my class object with linq to EF?

Question

my class,

  public class User
    {
        public string Username
        {
            get;
            set;
        }
        public string Password
        {
            get;
            set;
        }
        public string [] Roles
        {
            get;
            set;
        }
    }

my query is,

var innerJoinQuery =
                            (from u
                            in db.Users
                            join ur in db.UserRoles
                                on u.UserID equals ur.UserID
                            join r in db.Roles on ur.RoleID equals r.RoleID
                            select new {
                                Username=u.Username,
                                Password=u.Password,
                                RoleName = r.RoleName 
                            }).ToList();

data it is getting in above query is like,

username   password  RoleName
john        123       user
john        123       admin
john        123       super user
David       12345     super user
petter      123456    user

I want to get this data in my userclass object using Linq,

it should be like,

username   password  RoleName
john        123       { "user", "admin", "super user" }
David       12345     { "super user" }
petter      123456    { "user"}

I already tried many examples from net i did not get my answer.

hopes for your solution.

Answer 1

You can try with the following which uses a sub-query to select all of the role names:

var innerJoinQuery = (
    from u in db.Users
    select new User
    {
        Username = u.Username,
        Password = u.Password,
        Roles = (from ur in db.UserRoles
                 join r in db.Roles on ur.RoleID equals r.RoleID
                 where ur.UserID == u.UserID
                 select r.RoleName).ToArray()
    }
).ToList();

Alternatively, you can use group...by...into:

var innerJoinQuery = (
    from u in db.Users
    join ur in db.UserRoles on u.UserID equals ur.UserID 
    join r in db.Roles on ur.RoleID equals r.RoleID
    group r by u into g
    select new User
    {
        Username = g.Key.Username,
        Password = g.Key.Password,
        Roles = g.Select(c => c.RoleName).ToArray()
    }
).ToList();

Assuming your ORM supports this, it may generate a better query versus aggregating the keys and then sub-selecting from the aggregated groups.

If the ORM cannot translate ToArray/ToList (which Linq-To-SQL can't), you can either

1. change the type of User.Roles to be IEnumerable<string> instead of string[] and then remove the ToArray calls above, or
2. select into an anonymous type first, materialize the query (ToList) and then re-project the results into your User type

#2 looks like the following:

var innerJoinQuery = (
    from u in db.Users
    select new
    {
        u.Username,
        u.Password,
        Roles = (from ur in db.UserRoles
                 join r in db.Roles on ur.RoleID equals r.RoleID
                 where ur.UserID == u.UserID
                 select r.RoleName)
    }
).ToList();

var users = innerJoinQuery.Select(c => new User { 
     UserName = c.UserName, 
     Password = c.Password,
     Roles = c.Roles.ToArray() 
}); 

Or with the group...by variant:

var innerJoinQuery= (
    from u in db.Users
    join ur in db.UserRoles on u.UserID equals ur.UserID 
    join r in db.Roles on ur.RoleID equals r.RoleID
    group r by u into g
    select new
    {
        g.Key.Username,
        g.Key.Password,
        Roles = g.Select(c => c.RoleName)
    }
).ToList();

var users = innerJoinQuery.Select(c => new User { 
     UserName = c.UserName, 
     Password = c.Password,
     Roles = c.Roles.ToArray() 
}); 

Note that all of these solutions have the upside that they perform the grouping and/or sub-querying at the database, whereas the other answer does it in-memory