How do I query many-to-many mapping in EF4?

Question

I have a pretty simple problem with a not-so-obvious solution. I have a relational mapping in my database between Users and Roles, and each user can be mapped to one or more roles. So the mapping is like so:

User < 1:n > UserRole < n:1 > Role

In my generated EF4 POCOs, User and Role each have an ICollection of the other:

public class User 
{
    //Bunch of other properties/methods

    public virtual ICollection<Role> Roles
}

public class Role
{
    //Bunch of other properties/methods

    public virtual ICollection<User> Users
}

Now, I've implemented the IoC, UoW, and repository patterns illustrated in this article, which uses an ObjectSet to fetch/persist the data via repositories.

My question is, how do I implement this:

public bool UserIsInRole(int userId, int roleId)

I have tried the following:

public bool UserIsInRole(int userId, int roleId)
{
    Role role = _roleRepository.Single(r => r.Id == roleId);
    return _userRepository.SingleOrDefault(u => u.Roles.Contains(role)) != null;
}

But it fails with:

Unable to create a constant value of type 'Data.Models.Role'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Plus, it's not a very elegant implementation as it's having to hit the database twice.

I was looking for something like this:

return _userRepository.SingleOrDefault(u => u.Roles.Where(r => r.Id = roleId));

But ICollection doesn't support LINQ.

How can I do this, and ideally, how can I do it with one LINQ expression and one trip to the database?

Or, am I going about this completely wrong?

Thanks in advance.

Solved: Thanks to all who posted. All gave an acceptable answer. I accepted the one that was the most elegant.

score 3 · Accepted Answer · answered Mar 08 '11 at 22:14

3

There is a more concise way to do it:

public bool UserIsInRole(int userId, int roleId)
{
    return _userRepository.
            Any(u => u.Id == userId && 
                     u.Roles.Any(r => r.Id == roleId));
}

answered Mar 08 '11 at 22:14

Yakimych

17,612
7
52
69

msarchet · Answer 2 · 2011-03-08T15:04:47.970

1

If I understand correctly you are trying to select all of the users in a specific role.

If that is the case then:

public bool UserIsInRole(int userId, int roleId){
  var user = (from u in _userRepository where u.Id == userId select u).SingleOrDefult();
  if (user != null)
  {
     return (from r in user.Roles where r.Id = roleId select r).Any();
  }
  //you may not want to do this if you couldn't find the passed in user.
  return false;
}

You still have to hit the database twice but both of the queries should be pretty small.

Since you say, in comments, that you already have the user that you want to use the above method should still work. I was about to write something to explain how you could do it using the repository pattern that is mentioned in the article but at the surface it isn't function any differently than using a context would, at least for querying.

Since you are passing in a the User.Id as userId and not a complete User you still need to query for the appropriate user.

Now we can shorten the query some with

return _userReposity.Where(u => u.Id == userId)
                    .SelectMany(u => u.Roles)
                    .Where(r => r.id == roleId)
                    .SingleOrDefault() != null;

Or Alternatively

return (from u in _userRepository
        from r in u.Roles
        Where u.Id == userId && r.Id = roleId
        Select r).Any();

edited Mar 08 '11 at 15:04

answered Mar 08 '11 at 05:13

msarchet

15,104
2
43
66

Not exactly. See the method signature -- I have both one userId and one roleId, and as the method name suggests, I need to return a bool indicating whether that user is in that role. Also, I'm not working with an ObjectContext -- I'm working with an ObjectSet. That article I linked will explain the pattern I'm using better than I can. – Jerad Rose Mar 08 '11 at 05:27
@Jerad Rose so do you not have a true Many to Many relationship then since a true many to many would have the information going both ways, look up users from roles and roles from users. – msarchet Mar 08 '11 at 14:28
@msarchet - Sorry, yeah, it has many-to-many. It can go both ways, but in this particular method, I'm implementing the RoleProvider.IsUserInRole method. So I already have the user, I just need to determine if they are in a given role. I will likely have another method to give me all roles for a given user, but that query should be easier (won't have to filter on role -- just return all related roles). – Jerad Rose Mar 08 '11 at 14:41
@msarchet - Thanks for your help. I can do a `Where` call, but the response is an `IEnumerable`, which doesn't support the LINQ methods. I've opened up [a new question](http://stackoverflow.com/questions/5241377/implementing-repository-for-ef4-using-ddd-and-ioc) to make sure I'm not missing something. – Jerad Rose Mar 09 '11 at 04:41
@msarchet - Never mind, this works. My repository implements a method AsQueryable which returns an IQuerable I can use to do this. Thanks again for your help. – Jerad Rose Mar 09 '11 at 05:49
@Jerad, you can perform linq on a `IEnumerable` check out the http://msdn.microsoft.com/en-us/library/9eekhta0.aspx – msarchet Mar 09 '11 at 05:49
@msarchet - Yeah, I know. It was a silly mistake on my part -- I left out my `using System.Linq`. I think .NET puts it in there by default, but ReSharper keeps removing it if I'm not using it. Thanks again. – Jerad Rose Mar 09 '11 at 06:07

score 1 · Answer 3 · answered Mar 08 '11 at 07:36

1

Try this:

var result = _userRepository
              .Where(u => u.Id == userId)
              .SelectMany(u => u.Roles)
              .Where(r => r.Id == roleId)
              .SingleOrDefault();
return result != null;

answered Mar 08 '11 at 07:36

Ladislav Mrnka

360,892
59
660
670

How do I query many-to-many mapping in EF4?

3 Answers3

Linked