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

Reputation: 26972

EF Complex Query Join

The query shown below is very straight forward, it'll simply pull up tasks for a specified customer. What I'd now like to be able to do is take a UserId that is passed into this function and validate that the user has permission to view the task.

            var dbData = await db.Tasks
                .Where(a => a.CustomerId == customerId)
                .OrderBy(a => a.CreatedDateTime).ToListAsync();

There is a property in the Tasks table for OrganizationId. A User can belong to n+1 Organizations via a UserOrganizations table. What is the best way to take the known UserId and validate the the Task.OrganizationId is one of the User's?

Upvotes: 1

Views: 326

Answers (2)

AD.Net
AD.Net

Reputation: 13399

var dbData = await db.Tasks
                .Where(a => a.CustomerId == customerId
                         && a.Organization.Users
                                        .Any(u=>u.UserId == customerId)))
                .OrderBy(a => a.CreatedDateTime).ToListAsync();

This is given the foreign keys are setup and relationships are navigable through the entities.

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120937

If the relations are not already properties on the Tasks class, you can write your join in query-syntax. Something along these lines:

var dbData = await (from t in db.Tasks
                    join uo in UserOrganizations on t.OrganizationId equals uo.OrganizationId
                    join u in Users on uo.UserId equals u.UserId
                    where t.CustomerId == customerId && u.UserId == theUserId
                    order by t.CreatedDateTime
                    select t).ToListAsync();

Depending on how your data classes where generated, you might already have navigation properties on the Tasks class, allowing you to do:

var dbData = await db.Tasks
            .Where(a => a.CustomerId == customerId && a.Organization.UserOrganizations.Any(uo => uo.UserId == theUserId)
            .OrderBy(a => a.CreatedDateTime).ToListAsync();

Upvotes: 1

Related Questions