Beginning LightSwitch Part 2: Feel the Love - Defining Data Relationships

NOTE: This is the Visual Studio 2010 version of the popular Beginning LightSwitch article series. For other versions see:

• Visual Studio 2013 & HTML Client: Part 2: Feel the Love - Defining Data Relationships
• Visual Studio 2012: Part 2: Feel the Love - Defining Data Relationships

Welcome to Part 2 of the Beginning LightSwitch series! In the last post we learned about tables, or entities, in LightSwitch and how to use the Data Designer to define them. If you missed it: Beginning LightSwitch Part 1: What’s in a Table? Describing Your Data

In this article I want to focus on data relationships as we build upon the data model we started in part 1. Relationships define how information in one table corresponds to information in another table in a database. Or more generically, relationships define how an entity corresponds to another entity in (or across) a data source. You can think of relationships between entities like relationships between things in everyday life. For instance, the relationship between a school and its students is one that exists in the real world. Similarly, a real-world relationship exists between students and the classes that those students attend. In a data model, you may have one entity that contains students and another that contains classes they are attending. When you tie the two entities together, you create a relationship.

Building a Better Address Book

In the example we started in part 1, we’re building an address book application that manages contacts. In our current data model, we’re only storing one phone number, one email address and one address for our contact.

However, in real life contacts typically have more than one email, phone number, and address information. In order to model this in the database we are building through LightSwitch, we need to define additional tables and relate them to the contact table in a one-to-many relationship. A one-to-many relationship is probably the most common type of relationship you can define. In our case, this means that one contact can have many email addresses. One contact can have many phone numbers. One contact can also have many physical addresses.

Let’s start with email address. If we want to collect multiple email addresses for a contact we have a couple options. One option is to add a fixed number of email properties to our contact in the form of Email1, Email2, Email3. This means that we would never be able to collect more than 3 email addresses for any given contact. Depending on the type of application you are building this may be just fine. But if you start adding too many properties with the same meaning (in this case email) to your table, then it’s time to think of a different strategy. Instead we should create an EmailAddress table and define a one-to-many relationship.

From the Data Designer, click the “New Table” button and define an entity called EmailAddress with two required properties: Email (of type Email Address) and EmailType (of type String).

For EmailType we’ll create an Choice List, which you learned about in Part 1 of the series. This property will capture the type of email, whether that’s Personal or Work. I’ve also chosen to make the maximum length only 25 characters – we won’t ever need all 255.

 

Why don’t we make the maximum length 8 characters, which is the length of the longest value in the choice list? Because if we need to add a value to the choice list later that is a little longer than 8 characters, then we won’t have to change the data model. Disk space is cheap these days so it’s better to err on the side of longer max lengths so that all your data can fit into the underlying table. This avoids having to change the data model too often.

Defining Relationships in LightSwitch

Now that we have our EmailAddress entity it’s time to define the relationship. Click on the “Relationship…” button at the top of the Data Designer and this will open up the “Add New Relationship” dialog window. In the “To” column select Contact to set up the one-to-many relationship. The multiplicity is set to Many to One by default so we don’t need to change it. Multiplicity defines the type of relationship you want to create. In LightSwitch, you can also specify the multiplicity as One to Zero or One which means that only a maximum of one related entity would be allowed.

For more information on defining different types of relationships see: How to: Define Data Relationships

For information on how to model a many-to-many relationship in LightSwitch see: How to Create a Many-to-Many Relationship

You can also specify what happens to the email addresses when a contact is deleted. By default, this is set to “Restricted”. This means that a user would not be allowed to delete a Contact if they had any Email Addresses. Leaving the setting makes sense if we were working with Customers who had many Orders, for instance, but not in this case. We want LightSwitch to automatically delete any Email Addresses when we delete the Contact, so set the “On Delete Behavior” equal to “Cascade delete”.

The description at the bottom of the dialog is there to help you understand what you are doing when setting up the relationship. Once you click OK you will see the relationship in the Data Designer which will show all direct relationships to the entity you are working with. Notice that a Contact property is now added to the EmailAddress entity. This is called a navigation property and represents the Contact to which the EmailAddress belongs.

Double-click on the Contact entity to open it and you will notice a navigation property to all the EmailAddresses for that Contact. Navigation properties are used by LightSwitch on screens to navigate through your data, so it’s important to model them correctly.

Now that we’ve got the EmailAddress table defined and a relationship set up, we need to delete the Email property we had previously defined on the Contact itself. Select the Email property and hit the Delete key. Alternatively you can right-click and choose Delete on the menu. Do the same to delete the Address1, Address2, City, State, ZIP and Phone properties as well.

Next, let’s add a PhoneNumber table in the same way we added EmailAddress. You might wonder why we need to create a new table separate from the EmailAddress. This is because an EmailAddress and a PhoneNumber are different aspects of a Contact and have no relation to each other except through the Contact itself. Therefore, we need to create a new table.

Click the “Add Table” button and define the PhoneNumber entity with two required properties: Phone (of type Phone Number) and PhoneType (of type String). PhoneType will have a Choice List of “Cell”, “Fax”, “Home”, and Work”.

Next add the relationship to Contact exactly the same way as before. While the PhoneNumber entity is displayed, click the “Relationship…” button at the top of the Data Designer and specify the relationship to the Contact table.

Last but not least, we need to create an Address table to store multiple physical addresses for a Contact. Click the Add Table button and define the Address entity with the following properties AddressType, Address1, Address2, City, State, ZIP. Set the AddressType and Address1 properties as required. We will also specify a Choice List for the the AddressType property with values “Home”, “Work”, and “Other”.

Now set up the relationship for Address exactly as before. While the Address entity is displayed, click the “Relationship…” button and specify the relationship to the Contact table. Again we’ll choose “Cascade delete” so that any Addresses are deleted automatically if a Contact is deleted. This makes sense in the case of Contact because if the user deletes the contact from the system all their information should be automatically deleted. Keep in mind, however, that you may not want this behavior in other applications. For instance if you are building an order entry application you would want to restrict deletion of Customers if they had any Orders in the system in order to keep the Order history intact.

Now when we open the Contact entity in the Data Designer you can see all the direct relationships.

Testing the Address Book

Now that we have the data model designed, let’s quickly test it out by creating a screen. At the top of the Data Designer click the “Screen…” button to open the Add New Screen dialog. We’ll talk more about screens in a future post but for now just select the List and Details screen. Then drop down the Screen Data and select Contacts. Once you do this, you will see checkboxes for the additional related entities we created. Select all of them and click OK.

To build and launch the application hit F5. Now you can enter information using this screen. Click the “+” button on the top of the list box on the left to add new contacts.

Notice that LightSwitch read all the one-to-many relationships we set up in our data model and created a tabbed section of grids below the Contact details for Email Addresses, Phone Numbers and Addresses just like we would expect. The grids are editable by default so you can just type the related data directly into the rows.

Because we defined the relationships properly in our data model, LightSwitch was able to create a very usable screen for entering our data into all the tables in our database without much work at all. In the next post we’ll dive deeper into the Screen Templates and how to customize the layout of screens. Until next time!

Enjoy!

Go to next article –> Part 3: Screen Templates, Which One Do I Choose?

Comments

• Anonymous
  December 08, 2011
  I like you presentations - best ever.

• Anonymous
  December 14, 2011
  You have helped me so much w/your tutorials!   Thank you very much.

• Anonymous
  December 15, 2011
  Thanks guys!

• Anonymous
  February 01, 2012
  I wanted you to know how much time you have saved me. Words cannot begin to express my gratitude to you for providing such informative tutorial. LIKE+++

• Anonymous
  April 12, 2012
  Best of Tutorials, clear and easy ! Thanks Beth, "Yo the woman" .....

• Anonymous
  May 24, 2012
  Nice tutorials. Detailed examples. Thanks.

• Anonymous
  February 12, 2014
  I'm completely puzzled by this (and an app I'm working on). There doesn't seem to be a way to define the FIELDS that define the relationship. I have an employee table with an ID and a related table and nowhere can I define which field in the related table relates to the EmployeeId when I define a relationship. What am I missing? Regards mark

• Anonymous
  August 18, 2014
  I'm very thankful to you Marry me! :D

• Anonymous
  August 18, 2014
  @abdo Well I'm getting married next week so you're a little bit late ;) Thanks for the feedback!

• Anonymous
  August 28, 2014
  Hi,   I am pulling in my tables from SQL Server. When i create a relationship between a master/detail, and the column in the detail that relates to the primary key in the master, and this detail column is NOT a primary key, why I cannot see this detail column name   in the LightSwitch detail table anymore?  So i can no longer see it in any of my screens? BUT, if i delete the relationship on my SQL Server tables, and create the relationship in LightSwitch, that detail column shows up in LightSwitch and I can see it on the screens? thanks

• Anonymous
  August 29, 2014
  After looking at this more, it seems to come down to : 1 - Creating a foreign key on SSMS and pulling it in to LS will hide the foreign key column. BUT cascading delete will work. 2 - Creating a foreign key (relationship) in LS will show the associated column. BUT cascading has to done in the LS code programmatically. I need the SSMS cascading AND to see all table fields for every table in LS. Is this not possible? thanks, and Beth, if you really got married, congratulations!!

• Anonymous
  August 30, 2014
  Michael Washington help me understand this better. When I create an EF model from SQL Server, I get the option "Include foreign key columns in the model" when selecting the tables I want to use.  You don't get that in LightSwitch, but the foreign key column can be updated. In my case, when I initialized the fields of the detail (the  'ld' prefix) screen I was calling with "BeforeShown", I had to set this detail screens (newExerciseMeasure) copy of the master with this line " newExerciseMeasure.setMbsaDailyLog(screen.MbsaDailyLog);" myapp.showAddEditMbsaDailyLogDetail(null, global_MemberNo, null, null, null, null, null, {            beforeShown: function (addNewScreen) {                     var newExerciseMeasure = new myapp.MbsaDailyLogDetail;                          newExerciseMeasure.ldCreatedBy = global_MemberNo;                newExerciseMeasure.ldUpdatedBy = global_MemberNo;                newExerciseMeasure.ldRecordNo = 0;                newExerciseMeasure.ldSourceType = 0;                newExerciseMeasure.ldSourceNo = 0;                newExerciseMeasure.ldDateCreated = new Date();                newExerciseMeasure.ldDateUpdated = new Date();                newExerciseMeasure.ldUOMNo = 0;                newExerciseMeasure.ldValue = "";                newExerciseMeasure.ldNotes = "";                newExerciseMeasure.ldStatus = "";                newExerciseMeasure.setMbsaDailyLog(screen.MbsaDailyLog);                addNewScreen.MbsaDailyLogDetail = newExerciseMeasure;                                    }        });