2

I have five tables all with a primary key of ID

  • User
  • User_Role_Relation with Foreign Keys User_ID and Role_ID
  • Role
  • Role_Right_Relation with Foreign Keys Role_ID and Right_ID
  • Right

I am currently getting the Rights for the selected User with the following query in a stored procedure

  SELECT DISTINCT 
      tbl_Right.ID, tbl_Right.Name    
  FROM 
      tbl_User_Role_Relation 
  INNER JOIN 
      tbl_Role_Right_Relation ON tbl_User_Role_Relation.Role_ID =  tbl_Role_Right_Relation.Role_ID 
  INNER JOIN 
      tbl_Right ON tbl_Role_Right_Relation.Right_ID = tbl_Right.ID
  WHERE 
      tbl_User_Role_Relation.User_ID = @User_ID

I am trying to covert this to LINQ to Entity with this code

var query = from r in context.Rights
            from rrr in r.Role_Right_Relation
            from rl in rrr.Role
            from urr in rl.User_Role_Relation
            where urr.User_ID == userid
            select r;

but I get the following error

An expression of type 'Models.Role' is not allowed in a subsequent from clause in a query expression with source type 'System.Linq.IQueryable' Type inference failed in the call to 'SelectMany'

Any advise would be helpful.

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chad Myers
  • 106
  • 1
  • 7

3 Answers3

2

First of all that linq query is doing a cross join, not inner join like your sql. You should check out this

Second of all, you would probably be best correctly defining the relationships between entities in the edmx and you probably won't have to join at all, instead you can use navigation properites to access parent/children and filter on those properties directly

The idea of the entity framework is you don't have to flatten the hierarchy

Community
  • 1
  • 1
Martin Booth
  • 8,485
  • 31
  • 31
0

Edit 3

If you posted your code verbatim, then you are missing a line

from u in urr.Users

So:

var query = from r in context.Rights
from rrr in r.Role_Right_Relation
from rl in rrr.Role
from urr in rl.User_Role_Relation
from u in urr.Users
where u.User_ID == userid
select r;

Or at least there's a typo and the where should read:

where urr.User_ID == userid

resulting in:

var query = from r in context.Rights
from rrr in r.Role_Right_Relation
from rl in rrr.Role
from urr in rl.User_Role_Relation
where urr.User_ID == userid
select r;
user122211
  • 493
  • 3
  • 6
  • what I posted had a typo. I have corrected the post but I am still getting the error. I get the error on the line with: from rl in rrr.Role – Chad Myers Jun 16 '11 at 06:10
0

If you use method syntax you should be able to do something like:

var user = context.Users.FirstOrDefault(u => u.Id == userId);
var rights =user.Roles.SelectMany(role => role.Rights).Distinct();

Be sure to check user is not null before getting rights

NinjaNye
  • 7,046
  • 1
  • 32
  • 46