LINQ multilevel subqueries (in LightSwitch)

See more:

C#

SQL

LINQ

LightSwitch

I am struggling to get certain information from my database by using LINQ.

The initial database structure that is required for the system looks as follows:

Please see for image: http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/1f665aa3-ac69-4e81-91ae-5c9ee0bfe6b1[^]

What I need to get is to retrieve the Project name or specific project detail per OD. This however depends on the user that logs on to the system. As per the diagram above, users will be part of Sponsers, Owners, OD Leaders or Risk Managers.

In other words, if the user that logs on is a Risk Manager for a certain OD, I need to use his username and retrieve only the project details and specific projects that his user is mapped to.

How the system currently works: When a new project is added, existing or new project sponsers and owners are assigned to the project. If new delegates are added, they are automatically added to the user authentication and assigned the sponser or owner “roles” respectively. Similarly, when respective OD details are then added to this project, new or existing OD leaders and risk managers are assigned to the detail, and again the user authentication and rights are assigned etc…

Now, for example when a risk manager opens the system, I know what role his is part of. But because he still needs to drill through from top level, being the Project, I need to look at the tblRiskManagers table, find the related project OD detail records he is allowed to see, and then get to the top to know that he is only allowed to see project x which he is part of.

What a user can see is dependent on the relationships between the tables…

PLEASE can someone assist as matter of urgency?

I have searched for answers and got something down, but cant get anything to work. Will paste existing code in couple of hours when at work. Currently its 2AM in morning and my brain is bit fried...

(Oh, I have been using LightSwitch to build my user interface and linking to the SQL 2008 R2 database.)

Please note: This has been done on the "All_PreprocessQuery" in the dataservice class of Lightswitch for my tblProjects object...


            if (this.Application.User.IsInRole(Permissions.ManageAdmin))
            {
                // use full query, no limitation
            }
            else 
            if (this.Application.User.HasPermission(Permissions.ProjectOwners))
            {   
                query = from q in query
                    .Where(x => x.tblProjOwnerMaps == x.tblProjOwnerMaps
                        .Where(y => y.tblProcessOwner.UserName == this.Application.User.Name))
                        select q;

            }
            else if (this.Application.User.HasPermission(Permissions.ProjectSponsers))
            {
                query = from q in query
                    .Where(x => x.tblProjSponserMaps == x.tblProjSponserMaps
                        .Where(y => y.tblProjectSponser.UserName == this.Application.User.Name))
                        select q;

            }
            else if (this.Application.User.HasPermission(Permissions.OperatingDivisionLeader))
            {          
                query = from q in query
                            .Where(x => x.ProjectDetail == this.ProjectDetails
                                .Where(y => y.OperatingDivision.ODLeaderODMap == this.OperatingDivisions
                                    .Where(z => z.ODLeaderODMap == this.ODLeaderODMaps
                                        .Where(a => a.ODLeader.UserName == this.Application.User.Name)).AsQueryable()))
                        select q;


            }
            else if (this.Application.User.HasPermission(Permissions.ProjectODManager))
            {
                query = from q in query
                            .Where(x => x.ProjectDetail == x.ProjectDetail
                                .Where(y => y.ProjRiskManMap == y.ProjRiskManMap
                                    .Where(a => a.ODManager.UserName == this.Application.User.Name)))
                        select q;
                

            }