CODEWITHSUNDEEP
.netasp.net-mvclinq
Mathew
Mathew

Reputation: 41

How do I get this result using LINQ?

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

Upvotes: 2

Views: 178

Answers (4)

Gramero
Gramero

Reputation: 1885

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.

Upvotes: 0

jordanbtucker
jordanbtucker

Reputation: 6078

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

Upvotes: 2

Mathew
Mathew

Reputation:

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

Upvotes: 0

Gordon Bell
Gordon Bell

Reputation: 13633

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

Upvotes: 0

Related Questions