May 5

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