CODEWITHSUNDEEP
c#linq
katit
katit

Reputation: 17905

How to write this LINQ query as a single query?

Consider this code:

var query = from groupRole in CurrentItem.MEMGroupRoles 
            select groupRole.MEMRole;
this.AvailableRoles = this.allRoles.Except(query.AsEnumerable()).ToList();

In this code I take allRoles except those roles that CurrentItem already have. 2 issues:

  1. It doesn't work because I compare on objects and those objects are different instances
  2. I don't like 2 lines and like to improve.

Here is pseudo-code on what I really need to do right now:

var queryIds = from groupRole in CurrentItem.MEMGroupRoles 
               select groupRole.MEMRole.RoleId;
this.AvailableRoles = this.allRoles.Except(where RoleId query.AsEnumerable()).ToList();

How do I write query like this?

EDIT:

explanation:

  1. allRoles contains list of MEMRole objects
  2. CurrentItem.MEMGroupRoles contains list of MEMGroupRole objects and each MEMGroupRole contains MEMRole inside

I want to SELECT all MEMRole objects that's inside allRoles EXCEPT those MEMRoles that burries inside CurrentItem. First code snippet would work, but I need to compare MEMRole to MEMRole by MEMRole.RoleId since it's a different instances of the same database entity.

Upvotes: 2

Views: 155

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Is this LINQ to SQL?

If so, use DataContext.Log property to see the actual SQL that is being passed to the database, which may help you diagnose the problem.

Upvotes: 0

mgronber
mgronber

Reputation: 3419

You could override Equals() and GetHashCode() if the role object is such that it would make sense to identify it with role id. If that is not the case, you could create a role comparer class that implements IEqualityComparer<>. Except() takes equality comparer as second parameter.

Here is a solution that creates a lookup for role ids and uses it to filter the roles. However, I do think that the alternatives above are better solutions for your problem.

var lookup = CurrentItem.MEMGroupRoles
        .ToLookup(groupRole => groupRole.MEMRole.RoleId);
this.AvailableRoles = this.allRoles
        .Where(role => !lookup.Contains(role.RoleId))
        .ToList();

Upvotes: 1

k.m
k.m

Reputation: 31444

Following the approach you suggested:

var ids = CurrentItem.MEMGroupRoles.Select(g => g.MMERole.RoleId);
this.AvailableRoles = this.allRoles.Where(r => ids.All(i => i != r.RoleId));

Alternatively (althought I wouldn't go that road), if you must have single query, you can append both roles collections (current and all), group them by RoleId and pick groups that only have single member:

this.AvailableRoles = CurrentItem.MEMGroupRoles
    .Select(g => g.MEMRole)
    .Concat(this.allRoles)
    .GroupBy(r => r.RoleId)
    .Where(g => g.Count() == 1)
    .Select(g => g.First());

This results in roles that weren't in CurrentItem.MEMGroupRoles collection. But once again, it's just ... for sport :)

Upvotes: 0

Related Questions