asp.net mvc better way of getting relational data

Question

I am building an asp.net mvc site that allows users (with the role of manager) to add/manage other users

To do this I've added a relational table called ManagerUsers to the database that contains a manager id and a user id. When a manager invites a user, the data is stored in the ManagerUsers table.

When a manger views the users I am doing the following:

using (var context = new ApplicationDbContext())
{
    Guid myId = Guid.Parse(User.Identity.GetUserId());
    var userIds = context.ManagersUsers.Where(u => u.ManagerId == myId).Select(u => u.UserId.ToString()).ToList();

    var userProfiles = context.Users.Where(t => userIds.Contains(t.Id)).ToList();

    return View(userProfiles);
}

This works ok but seems kind of slow and long-winded. Does anyone know a better way of doing it?

EDIT: based on some replies I think I need to clarify what I'm asking. What I want to know is whether there is a better way to get a list of users that are under my management than getting a list of users Ids from the ManagerUsers table and then finding them from all of the users in the Users table? Maybe there is a better way of storing this data to make it faster for retrieval?

Why not abstract it into a method call? List<UserProfile> GetUsersForManager(Guid managerId)? Then you can reuse it. — mason
– mason, Commented Nov 6, 2015 at 14:35
Thanks, yes, certainly will do that. I guess I am asking more whether there is a better/faster/standardised way of dealing with relational databases like this? — user2424495
– user2424495, Commented Nov 6, 2015 at 14:38
@Drew at this point no but I wouldn't rule it out in the future - what are you thinking? — user2424495
– user2424495, Commented Nov 6, 2015 at 14:54
If you are using Entity Framework as your ORM, you can actually set the relationships using the 'virtual' property on your model. — Drew
– Drew, Commented Nov 6, 2015 at 14:59

Drew · Accepted Answer · 2015-11-06 15:13:32Z

1

This tutorial shows examples of defining relationships with Entity Framework and the virtual property:

https://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-an-entity-framework-data-model-for-an-asp-net-mvc-application

It would look something like this:

    public virtual <ApplicationUser> User { get; set; }

This will actually create a table relating the two models. From here you should be able to get the Users using ManagerUser.Users or something to this effect. I would also follow mason's example and implement a Repository pattern.

answered Nov 6, 2015 at 15:13

Drew

2,6318 gold badges44 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mason · Accepted Answer · 2015-11-06 15:25:45Z

1

You shouldn't tightly couple your data access code to your MVC layer. That makes it difficult to change data layers, and it makes it difficult to test MVC without hitting a real database. You're far better off creating a separate layer that allows them to be loosely coupled.

interface IMembershipService
{
    List<UserProfile> GetUsersForManager(Guid managerId);
}

class SqlServerMembershipService : IMembershipService
{
    private readonly string ConnectionString;

    public SqlServerMembershipService(string connectionString)
    {
        //Any initialization of the repository goes here
        ConnectionString = connectionString;
    }

    public List<UserProfile> GetUsersForManager(Guid managerId)
    {
        using (var context = new ApplicationDbContext(connectionString))
        {
            var userIds = context.ManagersUsers.Where(u => u.ManagerId == myId).Select(u => u.UserId.ToString()).ToList();

            var userProfiles = context.Users.Where(t => userIds.Contains(t.Id)).ToList();

           return View(userProfiles);
        }
    }
}

Your MVC controller looks like this:

class UsersController
{
    private readonly IMembershipService MembershipService;

    public UsersController(IMembershipService membershipService)
    {
        MembershipService = membershipService;
    }

    public ActionResult Index()
    {
        Guid myId = Guid.Parse(User.Identity.GetUserId());
        var profiles = MembershipService.GetUsersForManager(myId);
        return View(profiles);
    }
}

See how UsersController now has no idea about SqlServerMembershipService? All it knows is that it's going to receive some class via its constructor that will handle retrieving data for it. How it gets that class it up to you. You could tightly couple it by saying IMembershipService MembershipService = new SqlServerMembershipService but it's better to use Dependency Injection to do that for you.

edited Nov 6, 2015 at 15:25

answered Nov 6, 2015 at 14:52

mason

32.8k12 gold badges83 silver badges127 bronze badges

2 Comments

Ed Spencer Over a year ago

You don't need to implement a repository patten in order to abstract away your datastore logic. Please see programmers.stackexchange.com/a/220126 for explanations on why you might want to avoid the repository pattern when using Entity Framework

mason Over a year ago

@EdSpencer Great point, I had seen that page before. I just got into a bad habit of calling my abstraction a "Repository" when it's really a "Service".

user2424495 · Accepted Answer · 2015-11-24 21:16:28Z

0

Just in case anyone cares, here is what I did in the end:

public class ApplicationManager : ApplicationUser
{
    public virtual List<ApplicationUser> Users { get; set; }

    public ApplicationManager()
    {
        Users = new List<ApplicationUser>();
    }
}

public class ApplicationUser : IdentityUser
{
    public virtual ApplicationManager Manager { get; set; }
    ...           
}

This adds two fields to the AspNetUsers table - Manager_Id and Discriminator (which states whether the user is an ApplcationManager or ApplicationUser).

answered Nov 24, 2015 at 21:16

user2424495

5921 gold badge8 silver badges24 bronze badges

Collectives™ on Stack Overflow

asp.net mvc better way of getting relational data

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related