How to Create Reference Tables in LightSwitch Beta 2

Prologue:

My previous article was about how to pass parameters into screens in a LightSwitch application. Here we shall discuss how to make reference tables i.e. how to make a relationship among LightSwitch internal tables.


What is Data Relationship in LightSwitch Beta 2?

A relationship among Data helps you to create screens in which the related data work together.

For example, a screen that shows both Employees and their Projects. We can create, modify, or delete relationships between data by using the data designer in LightSwitch.

Multiplicity Settings in Relationship:

Multiplicity settings are used to specify how you want these tables to be related.

Follow the No: # to create the LightSwitch desktop application.

The Entity Designer:

Let us create a Table.

This is a new screen for Beta 2 which is not available in Beta 1. Select the rounded option to create a new table.

After clicking on the "Create new table" option, the Entity Designer will be displayed to create the Entity required.

For our demo application we need to create three tables to make the relationship among them.

Employee Table:

Note that, we have not created the Designation and Project fields in the Employee table.

Designation Table:

Project Table:

So we have created the three tables. Let us make the relationship among them.

To add the Relationship among tables just open the table to which we need to create a references. In our case, open the Employee table to make the reference of Designation and Project tables.

As shown in the above figure, click on the Relationship button at the top of the Entity designer.

Adding "Designation" Reference to Employee Table:

After clicking on the Relationship button, we will have the dialog window as like the following one.

Here,

1. The Employee Table will be selected in the "From" column. We need to select the table which is referenced by the Employee table. Here, for our demo application we have selected "Designation table.
2. Select the Multiplicity property. We have selected "Many" for Employee table and "One" to Designation table. For more details refer the Multiplicity Settings table given above.
3. Select the Deletion behaviour. We have selected "Restricted".

Adding "Project" Reference to Employee Table:

In this above figure, the only change here is the On Delete Behaviour. We have selected "Casecade Delete" for the Project relationship with Employee. If you delete the Project, the LightSwitch will delete all the employees who are committed with the Project.

The above figure shows us the relationship among three tables.

Quick Note:

We cannot specify a one-to-one, zero or one-to-zero or one, and many-to-many relationship by using the LightSwitch data designer.

Adding the Screens:

As we have three tables, we need to create three screens.

Follow the No: # to create a List and Details screen for the Designation table.

To create List and Details screen for the table Employee, follow the No: # specified.

As shown in the figure, create a List and Details screen for the Project table.

That's It. Without a single line of code we have created an application to store the employee information.

Press F5 to see the application in action.

Application in Action:

Add some records to the database using those three screens.

After adding the Designation and Project records, just add three records to Employee table. Here the Designation property will have the collection of Designation as we have referenced to the Designation table.

Here the Project collection is displayed for the Project property in the Employee screen as we have referenced to the Project table.

As shown in the figure, go to the Designations List Details screen and Delete an Item as shown in the above figure.

As it has the "Restricted" On Deletion Behavior it won't allow you to delete.

The above figure shows the validation error as the result of reference table.

Now delete the Project1 entry in the Project table. It will delete the two employees in the Project1.

As we have selected the "Casecade Delete" for On Deletion Behavior setting in the relationship among Employee table and Project table the employee records were deleted when deleted the Project1.

Summary:

In this article, we have seen how to create and use the reference tables in a LightSwitch application.

Thanks for spending your precious time here. Please provide your valuable feedbacks and comments, which enable me to give a better article the next time.

Thanks.