通过SecurityData.UserRegistrations表在当前用户上创关系

LightSwitch: Creating a Relationship on current User through SecurityData.UserRegistrations Table

     通常我们不关心登录用户是因为我们通过角色和权限能控制他们所能做的。但如果用户是某集团成员,这是一个现成的将用户表和他所在的组在一个一对多的关系中。在这个例子中,团体SalesTeams由SalesPerson组成。所有销售组成一个团体，团体的组员可以访问该团体。

     如果需要，当一个SalesPerson被创建，要做的一个用户也同时被创建，因为一个SalesPerson和SalesTeam是一个多对一的关系，因此User也是一样，本文向您展示如何创建UserRegistration然后两种方法来确定销售SalesPerson 和当前登录用户有关通过SalesPerson和SalesTeam的关系

通过增加SalesTeam实体开始:

       

SalesTeam 由 SalesPersons组成

   

我们将一个SalesPerson和一个User建立关系，让我们确保我们同意所指的user,Under Solution Explorer / Properties / Access Control select ‘User Forms authentication’ and check ‘Granted for debug’ next toSecurityAdministration,

      

按F运行此程序，展开Administrator菜单，点击“Users”.       

 

你可以看到没有任何用户信息被定义，在右下角我们可以看到一个特殊的用户TestUser在Visual Studio下运行，如果这是一个已发布的程序，在启动的时候会让你输入用户名和密码，相反我们为了方便开发而自动登录，其结果是,在部署应用程序当前用户,可以通过Application.Current.User.Name,将永远是其中一个用户,你可以在Administrator/Users上面所示的页面。在Visual Studio下，它会永远TestUser,这并不一定是你在上面看到的用户。

   首先我们需要一个属性将SalesPerson和User绑定，正如我所说，User.Name is actuallyUserRegistration.UserName，因此我们将增加一个UserName属性到SalesPerson,我们可以用UserName属性为Person的实际名字，

  当我们新增一个SalesPerson之前，需要有SalesTeam,因此我们创建一个编辑SalesTeam的屏幕，

   

添加一些SaleTeam,保存之后，我们添加一个编辑SalesPerson的屏幕，当我们新增SalePerson的时候我们可以选择SaleTeam,我们将 SalesPersons.UserName映射到UserRegistration.UserName，在我们运行的时候，我们可以通过将Application.User.Name toSalesPerson.UserName匹配，这实际上给了我们有能力创建用户和其他表之间的关系;在这种情况下,SalesTeam多对一的关系。您也可以创建一对多关系；比如一个场景:一个销售人员有一个个性化的委员会在每笔销售团队需要一个一对多关系SalesPerson andCommissions。

  当我们添加一个SalesPerson

      

如果需要，我们可以创建一个UserRegistration，我们不想处理密码，因此我们可以给他一个默认值，不过当用户第一次登陆的时候要改变它；

namespace LightSwitchApplication

{

    public partial class ApplicationDataService

    {

        partial void SalesPersons_Inserting(SalesPerson entity)

        {

            var reg = (from regs in this.DataWorkspace.SecurityData.UserRegistrations

                       where regs.UserName == entity.UserName

                       select regs).FirstOrDefault();

            if (reg == null)

            {

                var newUser = this.DataWorkspace.SecurityData.UserRegistrations.AddNew();

                newUser.UserName = entity.UserName;

                newUser.FullName = entity.Name;

                newUser.Password = "changeme/123";

                this.DataWorkspace.SecurityData.SaveChanges();

            }

        }

    }

}

 

如果我们打开Administrator/Users我们可以看到一个新建的User,

 

如果我们从Visual Studio启动，登陆的用户总是TestUser,因此我们将创建一个SalesPerson到RedTeam用他的UserName,

  

现在我们需要测试。这一点属于SalesTeam的是,你可以访问任何SalesTeam的访问。因此我们将创建一个Sales实体与一个多对一关系到SalesTeam。

  

为Sales创建一个编辑屏幕并添加数据，

 

 

现在你可以看到我已经为两个Teams新增了Sales,显而易见的原因，Team成员将不允许访问这个屏幕，因此我们讲新增另一个编辑屏幕，并通过登陆用户的SalesTeam过滤，我们可以通过2种方式这么做，我们可以继续并创建一个屏幕然后修改这个查询，但是我们将继续创建一个查询并基于此查询生生成一个屏幕

Right-Click the Sales Data Source and select Add Query

 

有几种方式来编写查询语句来限制Sales到只有那些属于相同SalesTeam登录的SalesPerson/User。
■“预处理的查询之前运行查询我们修改
■一个全局变量,我们可以添加一个过滤器。

The Pre-Process Query

添加一个预处理查询，

 

在这个查询中，我们发现SalesPerson和登陆的用户关联，这为我们提供了SalesTeam,从而意味着一个只选择Sales链接到同一SalesTeam作为SalesPerson/ User.:

partial void UserSalesTeamSales_PreprocessQuery(ref IQueryable query)

{

    SalesPerson person = (from persons in this.DataWorkspace.ApplicationData.SalesPersons

                            where persons.UserName == Application.Current.User.Name

                            select persons).FirstOrDefault();

    int salesTeamId = person == null ? -1 : person.SalesTeam.Id;

    query = from theSales in query

            where theSales.SalesTeam.Id == salesTeamId

            select theSales;

}

 

现在我们创建一个屏幕，

 

The first thing I see is that when logged-in as TestUser I can only seeRed Team Sales:

 

If I go to the Editable Sales Persons Grid I find that TestUser should be restricted to Red Team sales. So far so good:

 

If I change the Sales Team to Blue Team, clickSave, go back to Editable User Sales Team Sales Grid and clickRefresh:

 

I find that TestUser now is restricted to Blue Team Sales.

The Global Variable Query Parameter

这个可怕的事关于创建一个全局变量是,你必须lsml修改一个文件,它可以禁用设计师和导致奇怪的错误消息,如果处理不当,所以我们必须谨慎行事。首先,切换到文件视图:
 

We’re going to edit Common/ ApplicationDefinition.lsml. You may want to back up the file first.

You may get a Catastrophic failure when you try to edit this file. I discovered that if I first opened this one under data, then closed it I was able to edit the one under Common. If you look at properties you’ll find they both map to the same file, the one under data

  

Insert the following GlobalValueContainerDefinition element after the initialModelFragment element tag as shown below:

 

<ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model"

               xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

  <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo">

    <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32">

      <GlobalValueDefinition.Attributes>

        <DisplayName Value="User SalesTeam ID" />

        <Description Value ="Gets the logged on User's SalesTeam ID." />

      </GlobalValueDefinition.Attributes>

    </GlobalValueDefinition>

  </GlobalValueContainerDefinition>

 

Now you have to provide the code behind the variable. Create a new class in theCommon folder named GlobalSalesTeamInfo. When created there will be a bunch of using statements at the top that show errors. Replace everything with this:

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.LightSwitch;

namespace LightSwitchApplication

{

    public class GlobalSalesTeamInfo

    {

        public static int UserSalesTeamId()

        {

            SalesPerson person = (from persons in Application.Current.CreateDataWorkspace().ApplicationData.SalesPersons

                                  where persons.UserName == Application.Current.User.Name

                                  select persons).FirstOrDefault();

            return person != null ? person.SalesTeam.Id : -1;

        }

    }

}

 

Now we need to switch to logical view so we can modify the query to use this new variable. But when we do the designer will throw a hissy and tell you that you must reload. Once upon a time you could right-click inSolution Explorer and reload from there. Now, if you’re lucky, BindingToUsersTable Designer will be in a tab at the top. Click on that tab and you’ll get a page with aReload button. If you can’t get to that tab the only other way I know is to reload the solution. In other words, you need for the screen designer to be open in a tab when you edit the .lsml file. That way, when you need to reload, the tab will be there to select. In my experience once you change the .lsml file the Solution Explorer is hosed as far as the logical view, so you can’t get to the designer. Actually I didn’t think of theView menu. Anyway, don’t panic when you start getting all the incomprehensible messages. At best you have to exit and restart. At worst you have to exit, restore the .lsml file, and restart.

Back in logical view, open the UserSalesTeamSales query:

We can now filter on User Sales Team ID. (LightSwitch insists on breaking the name up into words).

 

Get rid of the pre-process query:

And sure enough I get the same results when I switch TestUser’s team membership.The advantage of the Global Variable approach is that it’s simpler to add the filter to each query that needs it, as you can see above, than it is to write a custom pre-process query for every query that needs to filter on SalesTeam membership as you can see in the now commented-out code immediately above

Testing Other Users

To really test, we need to deploy the application so we can log in as different Users. When you do, you’ll find that all your Users and data have disappeared, so we’ll have to create new SalesTeams, SalesPersons, and Sales

We’ll log in as a Blue Team Member:

 

And we only see Blue Team Sales:

 

Now we log in as a Red Team Member:

 

And we’re restricted to Red Team Sales:

So there you have it. In a less restricted application you’d want to make the Entity associated with User a little more generic, such as Person, so they could participate in a variety of scenarios but always be identifiable as an individual User. Which means a more succinct example would be a Global Variable that identifies the User/Person. Here I use the example of SalesPerson. I’ve added the UserSalesPersonID GlobalValueDefinition to ApplicationDefinition.lsml.

 

<ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model"

               xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml">

  <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo">

    <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32">

      <GlobalValueDefinition.Attributes>

        <DisplayName Value="User SalesTeam ID" />

        <Description Value ="Gets the logged on User's SalesTeam ID." />

      </GlobalValueDefinition.Attributes>

    </GlobalValueDefinition>

    <GlobalValueDefinition Name="UserSalesPersonId" ReturnType=":Int32">

      <GlobalValueDefinition.Attributes>

        <DisplayName Value="User SalesPerson ID" />

        <Description Value ="Gets the logged on User's SalesPerson ID." />

      </GlobalValueDefinition.Attributes>

    </GlobalValueDefinition>

  </GlobalValueContainerDefinition>

 

 

LightSwitch: Creating a Relationship on current User through SecurityData.UserRegistrations Table May5 Written by:Richard Waddell 5/5/2011 5:40 PM   Often we don’t care who the logged-in User is because we can control what they can do through Roles and Permissions. But if the User is a member of some group, it would be handy to place the Users table in a many-to-one relationship with that group. In this example the groups are SalesTeams made up of SalesPersons. All Sales made by a particular team are accessible only by members of that team. To make that happen a new User is created, if necessary, when a SalesPerson is created. Since a SalesPerson is in a many-to-one relationship with SalesTeam, so is the User. This article shows you how to create the UserRegistration and then two ways to identify the SalesPerson associated with the logged-in User and thereby the relationship with SalesTeam and Sales. Start by adding a SalesTeam entity: A SalesTeam is composed of SalesPersons: We’re going to associate each SalesPerson with a User. Let’s make sure we’re agreed on what I mean by ‘User’. Under Solution Explorer / Properties / Access Control select ‘User Forms authentication’ and check ‘Granted for debug’ next to SecurityAdministration. Press F5 to run the application, expand the Administration menu, and click on ‘Users’. As you can see, there are no Users defined. In the lower-right corner we see that we are logged in as the special user TestUser by virtue of running under Visual Studio. If this were a deployed application it would prompt for user and password on startup. Instead we are automatically logged-in for convenience during development.  The upshot is that in a deployed application the current User, which can be determined through Application.Current.User.Name, will always be one of the Users you see on the Administration/Users page shown above. Under Visual Studio it will always be TestUser, which isn’t necessarily one of the Users you see above. First of all, we need a property to bind User to SalesPerson. As I said, User.Name is actually UserRegistration.UserName, so we’ll add a UserName property to SalesPerson. We can use the Name property for the person’s actual name. Before we can add SalesPersons we need SalesTeams. Add a SalesTeam Editable Grid Screen: Add some SalesTeams: Save them and create a SalesPerson Editable Grid Screen where we find we can select the Sales Team when we create a Sales Person: The point of SalesPersons.UserName is to map to UserRegistration.UserName so at run time we can tell which SalesPerson we are dealing with by matching Application.User.Name to SalesPerson.UserName. This in effect gives us the ability to create relationships between User and other tables; In this case a many-to-one relationship to SalesTeam. You could also create one-to-many relationships, such as a scenario where a SalesPerson got an individualized commission on each Sale made by the team requiring a one-to-many relationship between SalesPerson and Commissions. When we create a new SalesPerson… …if necessary we also create a new UserRegistration. We don’t want to deal with passwords, so we set it to some default value the user should change the first time they log on: namespace LightSwitchApplication { public partial class ApplicationDataService { partial void SalesPersons_Inserting(SalesPerson entity) { var reg = (from regs in this.DataWorkspace.SecurityData.UserRegistrations where regs.UserName == entity.UserName select regs).FirstOrDefault(); if (reg == null) { var newUser = this.DataWorkspace.SecurityData.UserRegistrations.AddNew(); newUser.UserName = entity.UserName; newUser.FullName = entity.Name; newUser.Password = "changeme/123"; this.DataWorkspace.SecurityData.SaveChanges(); } } } } If we now go to Administration/Users we find the new User there. When launched from Visual Studio, the logged-on user is always TestUser, so we’ll create add a SalesPerson to Red Team with that UserName. Now we need something to test. The point of belonging to a SalesTeam is that you have access to whatever the SalesTeam has access to. So we’ll create a Sale entity with a many-to-one relationship to SalesTeam. Create an Editable Grid Screen for Sales and add some: Now you can see I’ve added Sales for both teams. For obvious reasons, team members would not be allowed access to this screen. So we’ll add another Editable Grid Screen and filter it by the SalesTeam of the logged-in User. There’s a couple of ways we could do this. We could go ahead and create the screen and then modify the query, but I’m going to go ahead and create the query and then generate a screen based on the query. Right-Click the Sales Data Source and select Add Query. And there are a couple of ways to write the query to restrict Sales to only those belonging to the same SalesTeam as the logged-on SalesPerson / User. A ‘pre-process’ query that runs before the query we’re modifying A Global Variable that we can add as a filter. The Pre-Process Query To add the pre-process query, In the query we find the SalesPerson associated with the logged on User. This gives us the SalesTeam and thereby a means to select only Sales linked to the same SalesTeam as the SalesPerson / User.: partial void UserSalesTeamSales_PreprocessQuery(ref IQueryable query) { SalesPerson person = (from persons in this.DataWorkspace.ApplicationData.SalesPersons where persons.UserName == Application.Current.User.Name select persons).FirstOrDefault(); int salesTeamId = person == null ? -1 : person.SalesTeam.Id; query = from theSales in query where theSales.SalesTeam.Id == salesTeamId select theSales; } Now we create the screen: The first thing I see is that when logged-in as TestUser I can only see Red Team Sales: If I go to the Editable Sales Persons Grid I find that TestUser should be restricted to Red Team sales. So far so good: If I change the Sales Team to Blue Team, click Save, go back to Editable User Sales Team Sales Grid and click Refresh: I find that TestUser now is restricted to Blue Team Sales. The Global Variable Query Parameter The scary thing about creating a Global Variable is that you have to modify an lsml file, which can disable the designer and lead to strange error messages if mishandled, so we have to proceed with care. First, switch to file view: We’re going to edit Common/ ApplicationDefinition.lsml. You may want to back up the file first. You may get a Catastrophic failure when you try to edit this file. I discovered that if I first opened this one under data, then closed it I was able to edit the one under Common. If you look at properties you’ll find they both map to the same file, the one under data. Insert the following GlobalValueContainerDefinition element after the initial ModelFragment element tag as shown below: <ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo"> <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesTeam ID" /> <Description Value ="Gets the logged on User's SalesTeam ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> </GlobalValueContainerDefinition> Now you have to provide the code behind the variable. Create a new class in the Common folder named GlobalSalesTeamInfo. When created there will be a bunch of using statements at the top that show errors. Replace everything with this: using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.LightSwitch; namespace LightSwitchApplication { public class GlobalSalesTeamInfo { public static int UserSalesTeamId() { SalesPerson person = (from persons in Application.Current.CreateDataWorkspace().ApplicationData.SalesPersons where persons.UserName == Application.Current.User.Name select persons).FirstOrDefault(); return person != null ? person.SalesTeam.Id : -1; } } } Now we need to switch to logical view so we can modify the query to use this new variable. But when we do the designer will throw a hissy and tell you that you must reload. Once upon a time you could right-click in Solution Explorer and reload from there. Now, if you’re lucky, BindingToUsersTable Designer will be in a tab at the top. Click on that tab and you’ll get a page with a Reload button. If you can’t get to that tab the only other way I know is to reload the solution. In other words, you need for the screen designer to be open in a tab when you edit the .lsml file. That way, when you need to reload, the tab will be there to select. In my experience once you change the .lsml file the Solution Explorer is hosed as far as the logical view, so you can’t get to the designer. Actually I didn’t think of the View menu. Anyway, don’t panic when you start getting all the incomprehensible messages. At best you have to exit and restart. At worst you have to exit, restore the .lsml file, and restart. Back in logical view, open the UserSalesTeamSales query: We can now filter on User Sales Team ID. (LightSwitch insists on breaking the name up into words). Get rid of the pre-process query: //partial void UserSalesTeamSales_PreprocessQuery(ref IQueryable query) //{ // SalesPerson person = (from persons in this.DataWorkspace.ApplicationData.SalesPersons // where persons.UserName == Application.Current.User.Name // select persons).FirstOrDefault(); // int salesTeamId = person == null ? -1 : person.SalesTeam.Id; // query = from theSales in query // where theSales.SalesTeam.Id == salesTeamId // select theSales; //} And sure enough I get the same results when I switch TestUser’s team membership. The advantage of the Global Variable approach is that it’s simpler to add the filter to each query that needs it, as you can see above, than it is to write a custom pre-process query for every query that needs to filter on SalesTeam membership as you can see in the now commented-out code immediately above. Testing Other Users To really test, we need to deploy the application so we can log in as different Users. When you do, you’ll find that all your Users and data have disappeared, so we’ll have to create new SalesTeams, SalesPersons, and Sales. We’ll log in as a Blue Team Member: And we only see Blue Team Sales: Now we log in as a Red Team Member: And we’re restricted to Red Team Sales: So there you have it. In a less restricted application you’d want to make the Entity associated with User a little more generic, such as Person, so they could participate in a variety of scenarios but always be identifiable as an individual User. Which means a more succinct example would be a Global Variable that identifies the User/Person. Here I use the example of SalesPerson. I’ve added the UserSalesPersonID GlobalValueDefinition to ApplicationDefinition.lsml. <ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo"> <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesTeam ID" /> <Description Value ="Gets the logged on User's SalesTeam ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> <GlobalValueDefinition Name="UserSalesPersonId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesPerson ID" /> <Description Value ="Gets the logged on User's SalesPerson ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> </GlobalValueContainerDefinition> Visual Studio was particularly cranky about these changes. I had to reload the solution twice, once because I made a mistake and it wouldn’t recognize that I had fixed it. The second time I don’t know why. The change was what you see above and I could reload in the designer, but Logical View would not come up. After I reloaded the solution, there it was. So don’t assume you’ve made a mistake when Logical View won’t come up, even if you hit the designer reload button. Here’s the code behind UserSalesPersonId. I’ve kept UserSalesTeamId because it’s still what I really need, but you can see that you could spin off Global Variables for every relationship that the logged-on user is involved from the one core method LoggedOnPerson() .

namespace LightSwitchApplication

{

    public class GlobalSalesTeamInfo

    {

        public static int UserSalesPersonId()

        {

            SalesPerson person = LoggedOnPerson();

            return person != null ? person.Id : -1;

        }

        public static int UserSalesTeamId()

        {

            SalesPerson person = LoggedOnPerson();

            return person != null ? person.SalesTeam.Id : -1;

        }

        private static SalesPerson LoggedOnPerson()

        {

            return (from persons in Application.Current.CreateDataWorkspace().ApplicationData.SalesPersons

                              where persons.UserName == Application.Current.User.Name

                              select persons).FirstOrDefault();

        }

    }

}

 

LightSwitch: Creating a Relationship on current User through SecurityData.UserRegistrations Table May5 Written by:Richard Waddell 5/5/2011 5:40 PM   Often we don’t care who the logged-in User is because we can control what they can do through Roles and Permissions. But if the User is a member of some group, it would be handy to place the Users table in a many-to-one relationship with that group. In this example the groups are SalesTeams made up of SalesPersons. All Sales made by a particular team are accessible only by members of that team. To make that happen a new User is created, if necessary, when a SalesPerson is created. Since a SalesPerson is in a many-to-one relationship with SalesTeam, so is the User. This article shows you how to create the UserRegistration and then two ways to identify the SalesPerson associated with the logged-in User and thereby the relationship with SalesTeam and Sales. Start by adding a SalesTeam entity: A SalesTeam is composed of SalesPersons: We’re going to associate each SalesPerson with a User. Let’s make sure we’re agreed on what I mean by ‘User’. Under Solution Explorer / Properties / Access Control select ‘User Forms authentication’ and check ‘Granted for debug’ next to SecurityAdministration. Press F5 to run the application, expand the Administration menu, and click on ‘Users’. As you can see, there are no Users defined. In the lower-right corner we see that we are logged in as the special user TestUser by virtue of running under Visual Studio. If this were a deployed application it would prompt for user and password on startup. Instead we are automatically logged-in for convenience during development.  The upshot is that in a deployed application the current User, which can be determined through Application.Current.User.Name, will always be one of the Users you see on the Administration/Users page shown above. Under Visual Studio it will always be TestUser, which isn’t necessarily one of the Users you see above. First of all, we need a property to bind User to SalesPerson. As I said, User.Name is actually UserRegistration.UserName, so we’ll add a UserName property to SalesPerson. We can use the Name property for the person’s actual name. Before we can add SalesPersons we need SalesTeams. Add a SalesTeam Editable Grid Screen: Add some SalesTeams: Save them and create a SalesPerson Editable Grid Screen where we find we can select the Sales Team when we create a Sales Person: The point of SalesPersons.UserName is to map to UserRegistration.UserName so at run time we can tell which SalesPerson we are dealing with by matching Application.User.Name to SalesPerson.UserName. This in effect gives us the ability to create relationships between User and other tables; In this case a many-to-one relationship to SalesTeam. You could also create one-to-many relationships, such as a scenario where a SalesPerson got an individualized commission on each Sale made by the team requiring a one-to-many relationship between SalesPerson and Commissions. When we create a new SalesPerson… …if necessary we also create a new UserRegistration. We don’t want to deal with passwords, so we set it to some default value the user should change the first time they log on: namespace LightSwitchApplication { public partial class ApplicationDataService { partial void SalesPersons_Inserting(SalesPerson entity) { var reg = (from regs in this.DataWorkspace.SecurityData.UserRegistrations where regs.UserName == entity.UserName select regs).FirstOrDefault(); if (reg == null) { var newUser = this.DataWorkspace.SecurityData.UserRegistrations.AddNew(); newUser.UserName = entity.UserName; newUser.FullName = entity.Name; newUser.Password = "changeme/123"; this.DataWorkspace.SecurityData.SaveChanges(); } } } } If we now go to Administration/Users we find the new User there. When launched from Visual Studio, the logged-on user is always TestUser, so we’ll create add a SalesPerson to Red Team with that UserName. Now we need something to test. The point of belonging to a SalesTeam is that you have access to whatever the SalesTeam has access to. So we’ll create a Sale entity with a many-to-one relationship to SalesTeam. Create an Editable Grid Screen for Sales and add some: Now you can see I’ve added Sales for both teams. For obvious reasons, team members would not be allowed access to this screen. So we’ll add another Editable Grid Screen and filter it by the SalesTeam of the logged-in User. There’s a couple of ways we could do this. We could go ahead and create the screen and then modify the query, but I’m going to go ahead and create the query and then generate a screen based on the query. Right-Click the Sales Data Source and select Add Query. And there are a couple of ways to write the query to restrict Sales to only those belonging to the same SalesTeam as the logged-on SalesPerson / User. A ‘pre-process’ query that runs before the query we’re modifying A Global Variable that we can add as a filter. The Pre-Process Query To add the pre-process query, In the query we find the SalesPerson associated with the logged on User. This gives us the SalesTeam and thereby a means to select only Sales linked to the same SalesTeam as the SalesPerson / User.: partial void UserSalesTeamSales_PreprocessQuery(ref IQueryable query) { SalesPerson person = (from persons in this.DataWorkspace.ApplicationData.SalesPersons where persons.UserName == Application.Current.User.Name select persons).FirstOrDefault(); int salesTeamId = person == null ? -1 : person.SalesTeam.Id; query = from theSales in query where theSales.SalesTeam.Id == salesTeamId select theSales; } Now we create the screen: The first thing I see is that when logged-in as TestUser I can only see Red Team Sales: If I go to the Editable Sales Persons Grid I find that TestUser should be restricted to Red Team sales. So far so good: If I change the Sales Team to Blue Team, click Save, go back to Editable User Sales Team Sales Grid and click Refresh: I find that TestUser now is restricted to Blue Team Sales. The Global Variable Query Parameter The scary thing about creating a Global Variable is that you have to modify an lsml file, which can disable the designer and lead to strange error messages if mishandled, so we have to proceed with care. First, switch to file view: We’re going to edit Common/ ApplicationDefinition.lsml. You may want to back up the file first. You may get a Catastrophic failure when you try to edit this file. I discovered that if I first opened this one under data, then closed it I was able to edit the one under Common. If you look at properties you’ll find they both map to the same file, the one under data. Insert the following GlobalValueContainerDefinition element after the initial ModelFragment element tag as shown below: <ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo"> <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesTeam ID" /> <Description Value ="Gets the logged on User's SalesTeam ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> </GlobalValueContainerDefinition> Now you have to provide the code behind the variable. Create a new class in the Common folder named GlobalSalesTeamInfo. When created there will be a bunch of using statements at the top that show errors. Replace everything with this: using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.LightSwitch; namespace LightSwitchApplication { public class GlobalSalesTeamInfo { public static int UserSalesTeamId() { SalesPerson person = (from persons in Application.Current.CreateDataWorkspace().ApplicationData.SalesPersons where persons.UserName == Application.Current.User.Name select persons).FirstOrDefault(); return person != null ? person.SalesTeam.Id : -1; } } } Now we need to switch to logical view so we can modify the query to use this new variable. But when we do the designer will throw a hissy and tell you that you must reload. Once upon a time you could right-click in Solution Explorer and reload from there. Now, if you’re lucky, BindingToUsersTable Designer will be in a tab at the top. Click on that tab and you’ll get a page with a Reload button. If you can’t get to that tab the only other way I know is to reload the solution. In other words, you need for the screen designer to be open in a tab when you edit the .lsml file. That way, when you need to reload, the tab will be there to select. In my experience once you change the .lsml file the Solution Explorer is hosed as far as the logical view, so you can’t get to the designer. Actually I didn’t think of the View menu. Anyway, don’t panic when you start getting all the incomprehensible messages. At best you have to exit and restart. At worst you have to exit, restore the .lsml file, and restart. Back in logical view, open the UserSalesTeamSales query: We can now filter on User Sales Team ID. (LightSwitch insists on breaking the name up into words). Get rid of the pre-process query: //partial void UserSalesTeamSales_PreprocessQuery(ref IQueryable query) //{ // SalesPerson person = (from persons in this.DataWorkspace.ApplicationData.SalesPersons // where persons.UserName == Application.Current.User.Name // select persons).FirstOrDefault(); // int salesTeamId = person == null ? -1 : person.SalesTeam.Id; // query = from theSales in query // where theSales.SalesTeam.Id == salesTeamId // select theSales; //} And sure enough I get the same results when I switch TestUser’s team membership. The advantage of the Global Variable approach is that it’s simpler to add the filter to each query that needs it, as you can see above, than it is to write a custom pre-process query for every query that needs to filter on SalesTeam membership as you can see in the now commented-out code immediately above. Testing Other Users To really test, we need to deploy the application so we can log in as different Users. When you do, you’ll find that all your Users and data have disappeared, so we’ll have to create new SalesTeams, SalesPersons, and Sales. We’ll log in as a Blue Team Member: And we only see Blue Team Sales: Now we log in as a Red Team Member: And we’re restricted to Red Team Sales: So there you have it. In a less restricted application you’d want to make the Entity associated with User a little more generic, such as Person, so they could participate in a variety of scenarios but always be identifiable as an individual User. Which means a more succinct example would be a Global Variable that identifies the User/Person. Here I use the example of SalesPerson. I’ve added the UserSalesPersonID GlobalValueDefinition to ApplicationDefinition.lsml. <ModelFragment xmlns="http://schemas.microsoft.com/LightSwitch/2010/xaml/model" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"> <GlobalValueContainerDefinition Name="GlobalSalesTeamInfo"> <GlobalValueDefinition Name="UserSalesTeamId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesTeam ID" /> <Description Value ="Gets the logged on User's SalesTeam ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> <GlobalValueDefinition Name="UserSalesPersonId" ReturnType=":Int32"> <GlobalValueDefinition.Attributes> <DisplayName Value="User SalesPerson ID" /> <Description Value ="Gets the logged on User's SalesPerson ID." /> </GlobalValueDefinition.Attributes> </GlobalValueDefinition> </GlobalValueContainerDefinition> Visual Studio was particularly cranky about these changes. I had to reload the solution twice, once because I made a mistake and it wouldn’t recognize that I had fixed it. The second time I don’t know why. The change was what you see above and I could reload in the designer, but Logical View would not come up. After I reloaded the solution, there it was. So don’t assume you’ve made a mistake when Logical View won’t come up, even if you hit the designer reload button. Here’s the code behind UserSalesPersonId. I’ve kept UserSalesTeamId because it’s still what I really need, but you can see that you could spin off Global Variables for every relationship that the logged-on user is involved from the one core method LoggedOnPerson() . namespace LightSwitchApplication { public class GlobalSalesTeamInfo { public static int UserSalesPersonId() { SalesPerson person = LoggedOnPerson(); return person != null ? person.Id : -1; } public static int UserSalesTeamId() { SalesPerson person = LoggedOnPerson(); return person != null ? person.SalesTeam.Id : -1; } private static SalesPerson LoggedOnPerson() { return (from persons in Application.Current.CreateDataWorkspace().ApplicationData.SalesPersons where persons.UserName == Application.Current.User.Name select persons).FirstOrDefault(); } } } The LightSwitch project is available at http://lightswitchhelpwebsite.com/Downloads.aspx