LINq query throws exception

Question

I am running the following query

var allroles = from l in metaData.Role select l.RoleId;
var personroles = from k in metaData.PersonRole
                  where k.PersonId == new Guid(Session["user_id"].ToString())
                  select k.RoleId;
Dictionary<Guid, string> allroleswithnames = 
    (from l in metaData.Role
     select new { l.RoleId, l.Description })
    .ToDictionary(u => u.RoleId, u => u.Description);
var avl_roles = from j in allroles.Except(personroles)
                select new
                {
                    RoleId = j,
                    Description = allroleswithnames[new Guid(j.ToString())]
                };
clist_avl_roles.DataSource = avl_roles;
clist_avl_roles.DataBind();

The code at code for avl_roles throwing error

Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Actually there are multiple rows for roleid with same person id. How do I rewrite the query to handle this situation?

Answer 1

var personId = new Guid(Session["user_id"].ToString());
var personRoles = metaData.PersonRole
                          .Where(pr => pr.PersonId == personId)
                          .Select(pr => pr.RoleId);    

var avl_roles = from r in metaData.Role
                where !personRoles.Contains(r.RoleId)
                select new { r.RoleId, r.Description };

Or in single query

var avl_roles = from r in metaData.Role
                join pr in metaData.PersonRole.Where(x => x.PersonId == personId)
                     on r.RoleId equals pr.RoleId into g
                where !g.Any()
                select new { r.RoleId, r.Description };