CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
Prescott
Prescott

Reputation: 7412

Return one column from a many-to-many join table in EF6

I need to return a single column from a join table in a many to many relationship in EF6

User { Id, Name}
Role { Id, Role}
UserToRole { UserId, RoleId}

This is a simplified example, but I need to get a list of RoleId's (Role.Id) from User.

Ideally I'd just do something like 

context.UserToRole.Where(x => x.UserId == id).Select(r => r.RoleId).ToList();

But EF doesn't seem to provide me with that join table as an object to query against.

I know I could pull down all of the roles as objects, but in my actual system that is a wide table and I'd like to avoid the overhead of pulling across the wire all that data and putting it into memory.

Upvotes: 1

Views: 104

Answers (3)

John Castleman
John Castleman

Reputation: 1561

The relations should already be there: usually, if you define your many-to-many relations as collections both ways, EF will generate the join table for you, which means that you just have to be clever to craft your LINQ queries in terms of just the tables you can see (which can admittedly sometimes be tricky). (And I take it from your comment that "EF doesn't seem to provide you the join table" that this is indeed the case.)

So, given the example you gave, it would be something like this:

context.Users.Where(u => u.Id == id).Single().Roles.Select(r => r.Id);

with the caveat that this will throw if no User is found with Id==id or if more than one is found (and therefore Id is not a Key). However, this is often exactly what you want with such lookups.

Upvotes: 0

ocuenca
ocuenca

Reputation: 39386

If you need only the RolesId from an User you can execute this query:

 int userId=1;
 var roleIds = db.Users.Where(u => u.Id == userId).SelectMany(u => u.Roles.Select(a=>a.Id));

This going to generate an sql query like this:

 {SELECT [Extent1].[Role_Id] AS [Role_Id]
  FROM [dbo].[UserRoles] AS [Extent1]
  WHERE [Extent1].[User_Id] = @p__linq__0}

As you can see, is the same query what you looking for.

Upvotes: 5

Ravi M Patel
Ravi M Patel

Reputation: 3055

Update

You may try context.Roles.SqlQuery("Select * from Roles where id in (select roleid from usertoroles where userid = @userid") to avoid loading users.

If your problem is to get all roles assigned to a specific user, define your entities as shown below:

    public class User
    {
        public int Id { get; set; }
        public string UserName { get; set; }

        //Navigation Property
        public List<Role> Roles { get; set; }
    }

    public class Role
    {
        public int Id { get; set; }
        public string Name { get; set; }

        //Navigation Property
        public List<User> Users { get; set; }
    }

Unless you want to control the many to many table name and column names (which is very simple), you're all set.

Create User

User k = new User()
         {
             UserName = "Kishan",
             Roles = new List<Role>()
             {
                 new Role() { Name = "Supremo" }
             }
         }
context.Users.Add(u);
context.SaveChanges();
User r = new User()
         {
             UserName = "Rama",
             Roles = new List<Role>()
             {
                 context.Roles.Single(r => r.Name == "Supremo")
             }
         }
context.SaveChanges();

Find roles for a user

User u = context.Users.Single(u => u.UserName == "Kishan");
List<int> userRoleIdList = u.Roles.Select(r => r.Id).ToList();

Upvotes: 1

Related Questions