LINQ多级子查询(在LightSwitch中) [英] LINQ multilevel subqueries (in LightSwitch)

问题描述

我正在努力使用LINQ从数据库中获取某些信息.

系统所需的初始数据库结构如下所示:

请查看图片: http://social.msdn .microsoft.com/Forums/zh-CN/linqprojectgeneral/thread/1f665aa3-ac69-4e81-91ae-5c9ee0bfe6b1 [
现在，例如，当风险经理打开系统时，我知道他是哪个角色的一部分.但是，由于他仍然需要从顶层(即项目)开始钻取，因此我需要查看tblRiskManagers表，找到允许他查看的相关项目OD详细记录，然后到达顶层以了解他只是允许查看他所属的项目x.

用户看到的内容取决于表之间的关系…

请有人紧急协助吗?

我一直在寻找答案，但有些不满意，但无法解决任何问题.在工作时，将在几个小时内粘贴现有代码.目前是凌晨2点，我的大脑有点炸...

(哦，我一直在使用LightSwitch构建用户界面并链接到SQL 2008 R2数据库.)

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;
                

            }

推荐答案

在进行了一些范围蠕动和必须实施的其他更改之后，我还解决了这个问题.

有关根据业务需求的代码以及 NEW 数据库结构的详细视图，请参见以下链接:

http://social.msdn.microsoft.com/论坛/en-ZA/linqprojectgeneral/thread/1f665aa3-ac69-4e81-91ae-5c9ee0bfe6b1 [

After some scope creep and other changes that had to be implemented, I also sorted out this issue.

For a detailed view of the code as well as the NEW DB structure according to business needs, please see the following link:

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

(Scroll to the "Answers" part I inserted....)

这篇关于LINQ多级子查询(在LightSwitch中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！