-1

How do i make that query in linq please advise 

select * 
from tblPermission 
where RoleId in (select roleid from tbluserrole where userid = @userID)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
sachin jain
  • 3
  • 3
  • Please check https://stackoverflow.com/questions/51339/how-can-you-handle-an-in-sub-query-with-linq-to-sql – sina_Islam Jun 29 '17 at 08:26
  • You definitely *don't* need to convert any SQL query to LINQ. LINQ isn't a replacement for SQL. It's a language on top of an **ORM** like Entity Framework. You need to create proper entities and relations so you *don't* have to copy queries into your code. That's the ORM's job. You need to write queries like `from user context.Users where UserID=145 select user` and read the permissions from the `Permissions` collection of the `Role` objects returned by the `User.Roles` collection – Panagiotis Kanavos Jun 29 '17 at 08:38
  • You should have a `User` entity with a `Roles` collection. A `Role` entity with a `Permissions` collection that returns `Permission` objects. If EF can't detect the relations due to the non-standard table names (using any kind of table prefix is *bad*), you can configure the relations yourself, in your context's configuration method. In the end, you should be able to write `dbContext.Users.Where(u=>u.ID=145).SelectMany(u=>u.Roles).SelectMany(r=>r.Permissions);` or `dbContext.Roles.Where(r=>r.userID=145).SelectMany(r=>r.Permissions);` – Panagiotis Kanavos Jun 29 '17 at 08:43

2 Answers2

0

When converting from SQL to LINQ, convert in the order of the LINQ phrases. If your SQL has table aliases, use them as the range variables. If a query contains a sub-query, translate it first in the same way. Translate IN into Contains.

var roles = from ur in tbluserrole where ur.userid == parmUserId select ur.RoleId;
var ans = from p in tblPermission
          where roles.Contains(p.RoleId)
          select p;
NetMage
  • 26,163
  • 3
  • 34
  • 55
-1

You can do this in 2 ways:

var roles = tbluserrole.Where(e => e.userid == userId).Select(e => e.roleid).ToList();
var prmsns = tblPermission.Where(e => roles.Contains(e.RoleId).ToList()

or

var prmsns = (from e in tblPermission 
    let roles = tbluserrole.Where(f => f.userid == userId).Select(f => 
f.roleid).ToList();
    where roles.Contains(e.RoleId)
    select e).ToList();

from

EDIT:

you can do this with inner join like this

var prmsns = (from e in tblPermission
    join f in tbluserrole on tblPermission.RoleId equals tbluserrole.roleid
    where f.userId == userId
    select e).ToList();
Marcin Ciesla
  • 84
  • 4
  • Converting a single INNER JOIN to two separate queries is a bad idea. First it won't work the same way - there's a limit to how many values you can pass to an `IN ()` statement. Second, it requires two roundtrips and resulting in at least double the time – Panagiotis Kanavos Jun 29 '17 at 08:44
  • Edited answer so you can use just join – Marcin Ciesla Jun 29 '17 at 08:52