CODEWITHSUNDEEP
c#entity-frameworklinq-to-sql
RSolberg
RSolberg

Reputation: 26972

EF Query - SQL "IN" - Same Table

I need to get a list of Users whom have a relationship with an Organization, but only if the current user has a relationship with the same Organization.

In other words, if the current user does not have a relationship with the Organization in question, no results are returned. If there is a relationship, results returned. This is the query I have right now, but it is only retrieving one user being the current user.

The query below will obviously get all Active UserOrganization records where the OrganizationId matches the value passed in to the query. So my question is, how do I know take my current user (UserId) and make sure that they exist within this result set before returning records they should not be able to see?

var dbUsers = db.UserOrganizations
                    .Where(u => u.OrganizationId == organizationId)
                    .WhereIf(active, u => u.IsActive)

Upvotes: 0

Views: 62

Answers (2)

AD.Net
AD.Net

Reputation: 13399

var dbUsers = db.Organizations
                    .Where(o => o.OrganizationId == organizationId
                     && o.Users.Any(u=>u.UserId == currentUserId && u.IsActive))
                    .SelectMany(o=>o.Users)

If you can't see the Users through an Organization you can do something like this:

var dbUsers = db.Organizations
                    .Where(o => o.OrganizationId == organizationId
&& o.UserOrganizations.Any(u=>u.User.UserId == currentUserId && u.User.IsActive))
                    .SelectMany(o=>o.UserOrganizations)
                    .Select(uo=>uo.User)

Upvotes: 2

DavidG
DavidG

Reputation: 118937

Simplest way is just to check if the current user has that relationship and decide if you want to continue or not. This saves having to do a potentially expensive database operation:

var hasRelationship = db.UserOrganisations.Any(uo => 
                          uo.UserId == currentUserId && 
                          uo.OrganizationId == organizationId);

if(hasRelationship) 
{
    //Interesting stuff here
}
else 
{
    //No access
}

Upvotes: 0

Related Questions