Linq To Entities - Get User Info With All Roles

Question

I am new to Linq and am testing it out with a new MVC site. I am still working my way through the syntax and capabilities, and I have run into a problem that I have not been able to solve. Any assistance would be appreciated.

Situation:

1 - I have an SQL database setup to use the default ASPNET Membership Provider. Standard naming conventions remain (aspnet_Users, aspnet_Roles, aspnet_UsersInRoleetc.)

2 - I have an added table called Users which provides extended user info. FK is the aspnet_User UserId (one to one).

3 - I have an added table called Customers which provides customer (ordering) info. FK is the UserId from the Users table (one to one).

4 - I have added a tabled called CustomerAddress which provides customer address info. FK is the CustomerId from the Customers table (one to many).

5 - I have set up a model to handle the presentation of this information to the admin

Issues:

1 - I would like to include with this presentation model a list of all roles available within the application and whether the user is currently assigned to that role.

2 - I would like to include with this presentation model a list of all addresses associated with the user

3 - I cannot figure out how to get this information back to my presentation model using a Linq query

Here is my Presentation Model (note that I currently have a property for the Roles but I haven't been able to figure out how to get this information. AND...I'm not sure what the type should be?

namespace Lambuth.PresentationModels.ManageUsers
{
public class UserDetail
{
[Editable(false)]
[Key, ScaffoldColumn(false)]
[Display(Name="User ID")]
public Guid UserId { get; set; }

[Required]
[Editable(false)]
[StringLength(16)]
[Display(Name="User Name")]
public string UserName { get; set; }

[Editable(false)]
[ScaffoldColumn(false)]
[StringLength(16)]
[Display(Name = "Lowered User Name")]
public string LoweredUserName { get; set; }

[Required]
[Editable(false)]
[StringLength(256)]
[Display(Name = "Email")]
public string Email { get; set; }

[Editable(false)]
[ScaffoldColumn(false)]
[StringLength(256)]
[Display(Name = "Lowered Email")]
public string LoweredEmail { get; set; }

[Editable(false)]
[StringLength(16)]
[Display(Name = "Site Alias")]
public string SiteAlias { get; set; }

[Required]
[Editable(false)]
[Display(Name = "Is Approved")]
public bool IsApproved { get; set; }

[Required]
[Editable(false)]
[Display(Name = "Is Locked Out")]
public bool IsLockedOut { get; set; }

[Editable(false)]
[Display(Name = "Join Date")]
public DateTime JoinDate { get; set; }

[Editable(false)]
[Display(Name = "Full Name")]
public string FullName
{
  get
  {
    AccountInfoHelpers help = new AccountInfoHelpers();
    return help.GetFullName(this.Salutation, this.FirstName, this.LastName);
  }
  set {/*no implmentation*/}
}

[Editable(false)]
[StringLength(20)]
[Display(Name = "Salutation")]
public string Salutation { get; set; }

[Editable(false)]
[StringLength(50)]
[Display(Name = "First Name")]
public string FirstName { get; set; }

[Editable(false)]
[StringLength(50)]
[Display(Name = "Last Name")]
public string LastName { get; set; }

[Editable(false)]
[Display(Name = "Last Login Date")]
public DateTime LastLoginDate { get; set; }

[Editable(false)]
[Display(Name = "Assigned Roles")]
public virtual ICollection<UserRole> AssignedRoles { get; set; }

} }

And here is the Linq query I currently have:

using (LambuthEntities context = new LambuthEntities())
  {
    var user = (from u in context.aspnet_Users
                join p in context.aspnet_Membership on u.UserId equals p.UserId
                join ux in context.Users on u.UserId equals ux.user_id
                join c in context.Customers on ux.user_id equals c.user_id
                where (ux.user_id == userId)  //userId passed in as param

                select new UserDetail
                {
                  UserId = ux.user_id,
                  UserName = u.UserName,
                  SiteAlias = ux.site_alias,
                  Email = p.Email,
                  Salutation = c.salutation,
                  FirstName = c.first_name,
                  LastName = c.last_name,
                  LastLoginDate = p.LastLoginDate,
                  IsApproved = p.IsApproved,
                  IsLockedOut = p.IsLockedOut
                }).Single();

    return user;
  }

Am I on the right track here? Any advice would be appreciated.

Thank You.

Your `.Single()` should be something like `.Single(a => a.UserId == 200`). Otherwise you're returning a query with more than one user and the call to `.Single()` will throw an exception. — Kittoes0124, Sep 19 '12 at 03:07
Hi Kittoes - Thanks for the reply. I added a note that indicates that the userId is passed in as a parameter. It's only returning the record for that specific GUID. This works fine...but I would like to return all the roles and addresses associated with the specific user. — flewis, Sep 19 '12 at 03:13
As far as combining business logic and ASP.Net membership this looks OK, see http://stackoverflow.com/a/12450829/861716. — Gert Arnold, Sep 19 '12 at 09:29
Thanks Gert. But that does not answer my questions. I already have the Membership tables built into my entity model. This is more of a specific Linq question: How do I get a collection of system roles, whether the user is in that role, and a collection of customer addresses back in a Linq query. — flewis, Sep 19 '12 at 20:22

score 0 · Answer 1 · edited May 23 '17 at 10:29

I would do the Linq statement a little different. While LinqToSql can be used to generate SQL statements and has the ability to look like SQL statements (as you see in your example), I tend to use them from the generated objects point of view.

What I mean by that thisis, instead of handling the joins between tables myself I take advantage of the work the LinqToSql generator already did and use their objects. The business objects already have properties that link to the related objects.

Here is how I would do your linq statement:

var user = (from u in context.aspnet_Users
            where (ux.user_id == userId)  //userId passed in as param
            select new UserDetail
            {
              UserId = u.user_id,
              UserName = u.UserName,
              SiteAlias = u.User.site_alias,
              Email = u.aspnet_Membership.Email,
              Salutation = u.User.Customer.salutation,
              FirstName = u.User.Customer.first_name,
              LastName = u.User.Customer.last_name,
              LastLoginDate = u.aspnet_Membership.LastLoginDate,
              IsApproved = u.aspnet_Membership.IsApproved,
              IsLockedOut = u.aspnet_Membership.IsLockedOut,
              AssignedRoles = u.aspnet_Roles.Select(r => r.RoleName).ToList()
            }).Single();

I am not sure what the UserRole class looks like so I just have a collection of strings right now. Also, not having the specific SQL scheme that you have (properties that you have set) I was going off of your description on the relationships of the objects.

Also, when dealing with LinqToSQL statements that pull data from multiple tables you want to make sure that you are making as few database calls as possible. If you do not you can run into a SELECT n+1 problem. One way to help deal with this issue is DataLoadOptions in LinqToSql.

Thanks SBurris. I know this is years late, but I never saw your reply. Thanks for taking the time, and my apologies for never seeing this. Cheers. — flewis, Feb 20 '17 at 17:00

Linq To Entities - Get User Info With All Roles

1 Answers1