OK, I have a situation that I’m hoping the people here can help me figure out. First let me say that I have a working solution to the problem but feel as though a more elegant solution exists within LLBLGEN. Background: I have three tables in my db that represent Projects, Reviews and ReviewParticipants.

The pseudo data model looks something like this.

Project 
{
ProjectId   -PK
Name  
OwnerId --  FK from user Table
--Other columns
}

Review
{
ReviewId   -PK
ProjectId   FK to projectable
CreatorId  - FK to user table
AuthorId- FK to user table
ModeratorId- FK to user table
ReaderId- FK to user table
RecorderId- FK to user table
--Other columns
}

ReviewParticipants
{
ReviewId   –PK FK to Review Table
UserId   - FK to user table 
--Other columns
}

Problem: Users open application and see a bunch of projects and reviews . They really only want to see the projects that they are involved in. The definition of “Involved In” is this… they are the project owner, they have one of the defined roles on a review on that project (i.e. author, recorder, etc) or they are an invited participant to a review on a project. What I want to do is create a predicate the will only return their projects.

Current Solution: I currently have a SP that returns a distinct list of ProjectIDs based on a UserId. I then take the returned list and populate an ArrayList of the Ids and then create a Predicate that uses the “in ArrayList” as a filter. I just feel that the double hit to the DB is not the best solution.

The Current SP is this.

SELECT DISTINCT Project.ProjectId
FROM         Project INNER JOIN
                      Review ON Project.ProjectId = Review.ProjectId INNER JOIN
                      ReviewParticipants ON Review.ReviewId = ReviewParticipants.ReviewId
WHERE    (Project.OwnerId = @UserId) OR
                      (Review.ReaderId = @UserId) OR
                      (Review.RecorderId = @UserId) OR
                      (Review.AuthorId = @UserId) OR
                      (Review.ModeratorId =@UserId) OR
                      (Review.ReviewCreatorId = @UserId) OR
                      (ReviewParticipants.UserId = @UserId )

Im open to all suggestions on how best to build this.

Thanks for any ideas you guys have!

I would probably try something like this. I would assume you know the userId for the currently logged on person.

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ProjectEntity.Relations.ReviewUsingProjectId);
bucket.Relations.Add(ReviewEntity.Relations.ReviewParticipantsUsingReviewId);
bucket.PredicateExpression.Add(ReviewFields.CreatorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.AuthorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.ModeratorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.ReaderId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.RecorderId == userId);
bucket.PredicateExpression.AddWithOr(ReviewParticipantsFields.UserId == userId);

EntityCollection<ProjectEntity> projects = new EntityCollection<ProjectEntity>(new ProjectEntityFactory());
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(projects, bucket);
}

I believe this would give you the projects that you are receiving using the SP with just one call to the DB.

bclubb wrote:

I would probably try something like this. I would assume you know the userId for the currently logged on person.

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ProjectEntity.Relations.ReviewUsingProjectId);
bucket.Relations.Add(ReviewEntity.Relations.ReviewParticipantsUsingReviewId);
bucket.PredicateExpression.Add(ReviewFields.CreatorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.AuthorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.ModeratorId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.ReaderId == userId);
bucket.PredicateExpression.AddWithOr(ReviewFields.RecorderId == userId);
bucket.PredicateExpression.AddWithOr(ReviewParticipantsFields.UserId == userId);

EntityCollection<ProjectEntity> projects = new EntityCollection<ProjectEntity>(new ProjectEntityFactory());
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(projects, bucket);
}

I believe this would give you the projects that you are receiving using the SP with just one call to the DB.

Thanks for the suggestion. Sorry about the long time to respond. I will try this out tomorrow at work.

Chris