How do I get this result using LINQ?

Question

I am a beginner to LINQ and I have a query where I need result like this.

Id    | RoleName 
-------------------
1     | Member  
2     | Admin  
3     | Manager  
4     | Accountant

Id  | UserId| RoleId | ExpirationDate
-------------------------------------
1   | 1     |  1     | 1/1/2011  
2   | 1     |  4     | 1/1/2012  
3   | 2     |  2     | 1/1/2013  
4   | 3     |  3     | 1/1/2014  

I want result like below for any user.Say for user id 1 it should be as below.

RoleName   | IsInRole | ExpirationDate  
-----------------------------------
Member     | True     | 1/1/2011              
Admin      | False    |   
Manager    | False    |  
Accountant | True     | 1/1/2014

Answer 1

Tricky LINQ, in SQL you'd do something like:

select r.RollName, 
(case when ur.UserId is null then 'False' else 'True' end) as IsInRole,
ur.ExpirationDate
from tblRoles r
left outer join tblUserRoles ur
on r.Id = ur.RoleId
where ur.UserId = 1

Answer 2

Guessing at your table names:

from r in db.Roles
join ur in (from ur2 in db.UsersInRoles
            where ur2.UserId == userId
            select ur2) on r.RoleId equals ur.RoleId into g
from ur in g.DefaultIfEmpty()
select new {
  RoleName = r.Name,
  IsInRole = ur != null,
  ExpirationDate = ur == null ? null : ur.ExpirationDate };

Idea from HookedOnLinq

Answer 3

Jordan;

I did test this but it only returns values which are in both tables as below.

RoleName | IsInRole | ExpirationDate

Member | True | 1/1/2011
Accountant | True | 1/1/2014

Answer 4

var result = from r in roles
                     select new { RoleName = r.RoleName, 
                                  IsInRole = (from u in usersInRoles
                                        where u.UserId == userId && u.RoleId == r.Id select u).Count() > 0,
                                  ExpriationDate = usersInRoles.SingleOrDefault(u => u.UserId == userId && u.RoleId == r.Id) != null ? 
                                  usersInRoles.SingleOrDefault(u => u.UserId == userId && u.RoleId == r.Id).ExpirationDate : null
                     };

another way to do this, it just for sample.