Dec 3

Written by: Richard Waddell
12/3/2011 8:22 AM 

 

Many-to-Many Relationships

A typical validation task that involves a many-to-many relationship involves a User who can update a number of Entities and an Entity that can be updated by a number of Users. For instance a School Book Inventory system where the counts for books at a particular school can only be updated by an employee of that school. Each employee can update a number of counts and each count can be updated by a number of employees. The application must prevent unauthorized data base updates and prevent employees from seeing counts from other schools.

We’ll start with validation. Although this article touches on validation on both client and server, to really understand what’s going on I suggest you see this post by Prem Ramanathan and this article he wrote for Code magazine. And if you want to get into what you can do in the Save Pipeline, which is entered when you call this.ApplicationData.SaveChanges(), either through code or when the save button is executed, check out this article by Dan Seefeldt.

The Requirements

As I said, the example is based on maintenance of book inventory for a number of schools within a school district. The inventory records for all books are maintained in a single table and each school is responsible for maintaining the counts for the books at that school. The requirements:

• Don’t allow an employee of one school to update the counts from a different school
• Don’t allow invalid counts
• Don’t allow a particular book to be assigned to a particular school twice
• Don’t allow an employee of one school to see the counts from a different school
• Validate counts at the screen level

The Tables

We start by creating a Book entity.  Turn off ‘Display by Default’ for Id (and for Id on all other entities we create). Turn on ‘Include in Unique Index’ for Title.

Next we have a School entity. Turn on ‘Include in Unique Index’ for Name.

The Relationships

A School has many Books and a Book can be at many Schools, so we need a many-to-many relationship between School and Book. Create a SchoolBook table as shown below. Note the relationships. Each SchoolBook is related to one School and one Book. By including both School and Book in unique index we ensure there is only one SchoolBook record for any particular Book at a particular School.

For display purposes we add a Summary property which is computed:

namespace LightSwitchApplication
{
    public partial class SchoolBook
    {
        partial void Summary_Compute(ref string result)
        {
            result = string.Format("{0} / {1}",
                School != null ? School.Name : "",
                Book != null ? Book.Title : "");
        }
    }
}

The Screens

About the screen names – I’ve been working on a project with a lot of tables that required a lot of validation and it was helpful to create what I call a Debug Screen for each entity to easily test before and after, switch back and forth, etc. Turns out not so useful here but what’s the harm, and tempus is fugiting, so I’m sticking with the names.

We’ll create DebugBooks, DebugSchools, and DebugSchoolBooks List and Detail screens. Be sure to include all Additional Data for all three screens.

The Data

Now hit ctl-F5 to run the application and let’s see what we have.

We add some Books above and some Schools below:

SchoolBook Validation

Now I’ll just point out here that if you add a SchoolBook relationship through the Books screen you’ll be constrained to the selected Book; the Schools screen will constrain you on the selected School. The School Books screen constrains neither, as shown below:

And we confirm we can add more than one book to Riverdale High, but we can’t add the same book twice:

Adding Users

Now we’ll add a SchoolEmployee entity and some School Employees to maintain the Counts:

We have a User property as we are going to match to logged-in User. It’s a business rule that a School Employee can only be employed at one School, so we  make User the one and only field to ‘Include in Unique Index’.

Now let’s run the app and add a SchoolEmployee:

When you run under Visual Studio you are always logged in as TestUser, so that’s the User we link to our first School Employee.

Validation On The Server

Now we have an employee and books to count.  So where do we start the validation process? I think the best place to start is at the bottom; i.e. don’t allow invalid changes to the database. Validating the entity is the closest we can get to the storage level working within LightSwitch. if we go back to our list of requirements we find that the first one is to prevent an employee of one school from updating the counts for a different school.

We add the code to enforce this rule to the SchoolBooks_Validate event. If your Entity window is wide enough you’ll see a ‘Write Code’ tab. Click on the down arrow icon to the right and select SchoolBooks_Validate, which as you can see, is called when an item is validated on the server:

If the window is not wide enough you can still get to the dropdown as shown below:

And here’s the code:

namespace LightSwitchApplication
{
    public partial class ApplicationDataService
    {
        partial void SchoolBooks_Validate(SchoolBook entity, EntitySetValidationResultsBuilder results)
        {
            var LoggedInSchoolEmployee = (from emp in DataWorkspace.ApplicationData.SchoolEmployees
                                          where emp.User == Application.Current.User.Name
                                          select emp).FirstOrDefault();
            if (LoggedInSchoolEmployee == null)
            {
                results.AddEntityError("Denied");
                return;
            }
            if (entity.School.Id != LoggedInSchoolEmployee.School.Id)
            {
                results.AddEntityError(string.Format("Employee {0} belongs to {1}, book belongs to {2}",
                                                        LoggedInSchoolEmployee.Name,
                                                        LoggedInSchoolEmployee.School.Name,
                                                        entity.School.Name));
            }
        }
    }
}

The error message when the logged-in user is not an employee has almost no information, The message when the user is an employee of a different school goes to the other extreme. You can also write it like this if you don’t need the Employee’s information.

partial void SchoolBooks_Validate(SchoolBook entity, EntitySetValidationResultsBuilder results)
{
    int? LoggedInSchoolEmployeeSchoolId = (from emp in DataWorkspace.ApplicationData.SchoolEmployees
                                    where emp.User == Application.Current.User.Name
                                    select emp).FirstOrDefault().School.Id;
    if (LoggedInSchoolEmployeeSchoolId.HasValue == false)
    {
        results.AddEntityError("Denied");
        return;
    }
    if (entity.School.Id != LoggedInSchoolEmployeeSchoolId)
    {
        results.AddEntityError(string.Format("Employee {0} belongs to {1}, book belongs to {2}",
                                                "Unknown", "Unknown",
                                                entity.School.Name));
        return;
    }
}

Let’s test. To start with I changed the SchoolEmployee User to ‘TestUser2’ to test the case where the logged-in User is not a SchoolEmployee. Here’s what I  get after trying to change a count and clicking ‘Save’:

If I change Gabe's User back to ‘TestUser’ and click ‘Save’ again I get this message:

Validation On The Client

So far so good. Now let’s handle our second requirement, which is to prevent invalid counts. We start by preventing negative numbers:

To make it a little more interesting I added the admittedly contrived requirement that the sum of Count New, Count Used Good, and Count Used Poor must equal Count Total. We handle this by adding Validation to the SchoolBook CountTotal property:

Notice that where the SchoolBooks_Validate method is a member of ApplicationDataService, and runs on the server, CountTotal_Validate is a member of the SchoolBook class and runs on the client (but, as it turns out, not just on the client).

namespace LightSwitchApplication
{
    public partial class SchoolBook
    {
        ...
        partial void CountTotal_Validate(EntityValidationResultsBuilder results)
        {
            if (CountUsedGood + CountUsedPoor + CountNew != CountTotal)
                results.AddPropertyError("Individual counts must add up to count total");
        }
    }
}

So instead of seeing a message after clicking Save, one appears immediately when the input changes:

More Validation On The Server, No Extra Charge

Entity property validation also runs on the server, so that’s all the code you have to write. To prove this, I added a button to deliberately add a SchoolBook with invalid count total.

namespace LightSwitchApplication
{
    public partial class DebugBooks
    {
        partial void CreateInvalidSchoolBook_Execute()
        {
            var newSchoolBook = this.DataWorkspace.ApplicationData.SchoolBooks.AddNew();
            newSchoolBook.CountTotal = 50;
            newSchoolBook.CountUsedGood = 60;
            newSchoolBook.School = (from school in this.DataWorkspace.ApplicationData.Schools
                                    select school).FirstOrDefault();
            newSchoolBook.Book = (from book in this.DataWorkspace.ApplicationData.Books
                                  select book).FirstOrDefault();
            this.DataWorkspace.ApplicationData.SaveChanges();
        }
    }
}

And here’s the result, complete with custom error message provided by CountTotal_Validate:

So we were able to satisfy two of our requirements in one piece of code. We prevented invalid counts from making it to the database and we validated the counts at the screen level at the time of entry.

Filtering on Logged-In User

Now we move on to filtering on User, because the last requirement is to prevent an employee from seeing counts from a different school. There are a couple of ways to go about it. The first is to add a query to the SchoolBooks table.

We name the query LoggedInEmployeeSchoolBooks and handle the filtering in the preprocess query by looking up the Logged-In Employee and then passing only SchoolBooks  belonging to the same school.

partial void LoggedInEmployeeSchoolBooks_PreprocessQuery(ref IQueryable<SchoolBook> query)
{
    var LoggedInEmployee = (from entity in DataWorkspace.ApplicationData.SchoolEmployees
                                    where entity.User == Application.Current.User.Name
                                    select entity).FirstOrDefault();
    query = LoggedInEmployee != null ?
        from entity in query
        where entity.School.Id == LoggedInEmployee.School.Id
        select entity : null;
}

And add a screen based on the new query:

If we put Gabe at Tom Landry Middle School:

He only sees books from Tom Landry:

Universal Filtering

Now let’s say you had an application where prevention of unauthorized data access is critical and/or there are a lot of screens to maintain. The pre-process query works fine, but you have to remember to use it as the basis for every screen and every dropdown that presents data that should be filtered on the user. It would be nice if every screen automatically filtered SchoolBook on logged-in Employee. This can be easily done by editing the SchoolBooks_All_PreprocessQuery .

As you would expect, it’s the same query:

partial void SchoolBooks_All_PreprocessQuery(ref IQueryable<SchoolBook> query)
{
    var LoggedInEmployee = (from entity in DataWorkspace.ApplicationData.SchoolEmployees
                            where entity.User == Application.Current.User.Name
                            select entity).FirstOrDefault();
    query = LoggedInEmployee != null ?
        from entity in query
        where entity.School.Id == LoggedInEmployee.School.Id
        select entity : null;
}

Now when we run the app we see that the unfiltered screen is now filtered.

Opening Holes In The Filter

It’s nice to be able to know all screens will filter on logged-in user without having to take any steps to do so. But you might have to make exceptions. Below I add a DistrictAccess  Permission which we can use for that purpose:

partial void SchoolBooks_All_PreprocessQuery(ref IQueryable<SchoolBook> query)
{
    if (Application.Current.User.HasPermission(Permissions.DistrictAccess))
        return;
    var LoggedInEmployee = (from entity in DataWorkspace.ApplicationData.SchoolEmployees
                            where entity.User == Application.Current.User.Name
                            select entity).FirstOrDefault();
    query = LoggedInEmployee != null ?
        from entity in query
        where entity.School.Id == LoggedInEmployee.School.Id
        select entity : null;
}

Now when logged in as TestUser, who has DistrictAccess Permission, we see all SchoolBooks.

This way we apply the tightest possible security and then add exceptions as opposed to remembering to add security screen by screen, which might be an overstatement, because all you have to do is to remember to base the screen off the proper query, but still. Of course we can still screw it up, but it takes more effort. Or maybe not. Now we have to be careful about the permissions of the screen user if the relationships don’t fit into the tidy little scenario shown here.

Another alternative is to leave in the most restrictive filtering so that all screens that access that DataSource will filter on logged-in user with no exceptions. Then, to make exceptions, create a separate DataSource based on a WCF RIA Service that accesses the same table. You can apply different filtering, or no filtering at all, as required by the screens you will add that access that DataSource. Which puts you back to the position of making sure each screen accesses the correct DataSource No matter what, you have to be careful, but you do have options on how to divide up the responsibility.

Of course DistrictAccess doesn’t allow update of counts where the user is not an employee of the same school:

If you wanted DistrictAccess to allow counts to be updated just exit immediately as we did in the pre-process query by adding this as the first statement in SchoolBooks_Validate();

if (Application.User.HasPermission(Permissions.DistrictAccess))
    return;

Testing can get a bit tedious as you add more permissions because you can only ever log in as TestUser unless you publish the application and run it outside of Visual Studio.

Of course in a real-world app there would probably be only one employee table containing both school and district employees and there would be some sort of audit on the counts against employee, but that’s outside the illustrational scope of this example.

So that’s a glimpse into validation and filtering at different levels mostly based on the logged-in user belonging to the same group as the data.

I encourage you again to see this post by Prem Ramanathan and this article he wrote for Code magazine. And to find out more about the Save Pipeline check out this article by Dan Seefeldt.

The LightSwitch project is available here.