How to: Define Data Relationships in LightSwitch
For the latest documentation on Visual Studio 2017, see Visual Studio 2017 Documentation.
You can create, modify, or delete relationships between data by using the data designer in LightSwitch. By using relationships, you can create screens in which the related data work together (for example, a screen that shows both customers and their orders).
This document contains the following sections:
Adding a relationship between tables or entities from the same data source
Adding a relationship between tables or entities from separate data sources
Modifying a relationship
Multiplicity settings
Deletion behavior settings
For a related video demonstration, see How Do I: Define My Data in a LightSwitch Application?.
Adding a Relationship Between Tables or Entities From the Same Data Source
You can use the LightSwitch data designer to add a relationship between two tables in the LightSwitch internal database. In some cases, you can also use the data designer to add a relationship between entities from the same external data source. Otherwise, you must create those relationships on the server and then refresh the data connection in LightSwitch. For more information about how to refresh a connection to a data source, see How to: Connect to Data.
To add a relationship between tables in the LightSwitch internal database
In Solution Explorer, open the shortcut menu for one of the tables that you want to use in the relationship, and then choose Open.
Note
For applications that have been upgraded to Visual Studio 2012 Update 2, on the Perspective bar, choose the Server tab.
In the Data Designer, on the command bar, choose the Relationship button.
In the Add New Relationship dialog box, in the Name row, choose the two tables to which you want to apply a relationship (for example, Customers and Orders).
In the Multiplicity row, specify how you want these tables to be related.
See Multiplicity Settings.
In the On Delete Behavior row, specify what you want your application to do if a user tries to delete a record in one table but data still exists in the related record in the other table (for example, if a user tries to delete a customer but orders placed by the customer still exist).
See Deletion Behavior Settings.
In the Navigation Property row, enter a name for each navigation property, and then choose the OK button.
Navigation properties help users navigate between areas of a screen that display related data. Navigation properties also appear as objects in IntelliSense, and you can use them to construct LINQ expressions in code.
To add a relationship between tables in an attached database
In Solution Explorer, open the shortcut menu for one of the tables that you want to use in the relationship, and then choose Open.
Note
For applications that have been upgraded to Visual Studio 2012 Update 2, on the Perspective bar, choose the Server tab.
In the Data Designer, on the command bar, choose the Relationship button.
The Add New Relationship dialog box opens.
In the To column of the Name row, choose the table to which you want to apply a relationship.
The table must contain a primary key that will be used on the primary side of the relationship.
In the Multiplicity row, specify how you want these tables to be related.
See Multiplicity Settings.
In the data grid that appears near the bottom of the Add New Relationship dialog box, choose a property from the Foreign side of the relationship.
This property must match the data type of the Primary key property.
Note
If the table contains a composite primary key, all foreign key data types must match.
Note
For a zero or one-to-many relationship, the Foreign key property can’t be a required field. For a one-to-many relationship, the Foreign key property must be a required field. For a zero or one-to-one relationship, the primary key values must be used on both sides of the relationship.
Choose the OK button.
Adding a Relationship Between Tables or Entities from Separate Data Sources
You can use the LightSwitch data designer to add a relationship between tables or entities from separate data sources. For example, you can add a relationship between a table in the internal database for LightSwitch and an entity from a SharePoint list.
To add a relationship between two tables or entities from separate data sources
In Solution Explorer, open the shortcut menu for one of the tables that you want to use in the relationship, and then choose Open.
Note
For applications that have been upgraded to Visual Studio 2012 Update 2, on the Perspective bar, choose the Server tab.
In the Data Designer, on the command bar, choose the Relationship button.
The Add New Relationship dialog box opens.
In the Name row, choose the tables or entities to which you want to apply a relationship (for example, Customers and Orders).
In the Multiplicity row, specify how you want these table or entities to be related.
See Multiplicity Settings.
Note
Notice that the On Delete Behavior row isn't enabled for this kind of relationship.
In the Navigation Property row, provide a name for each navigation property.
Navigation properties help users navigate between areas of a screen that display related data. Navigation properties also appear as objects in IntelliSense, and you can use them to construct LINQ expressions in code.
In the data grid that appears near the bottom of the Add New Relationship dialog box, choose a property from one table or entity and a related property in the other table or entity, and then choose the OK button.
These properties are where the tables or entities link together. For example, you could choose the Id property of a Customer entity and the CustomerID property of an Order entity.
Modifying a Relationship
You can use the data designer in LightSwitch to modify or delete a relationship.
To modify an existing relationship
In Solution Explorer, open the shortcut menu for one of the tables that you're using in the relationship, and then choose Open.
Note
For applications that have been upgraded to Visual Studio 2012 Update 2, on the Perspective bar, choose the Server tab.
In the Data Designer, choose the navigation property that represents the relationship to another entity or table.
For example, a Customer entity might have a property that's named Orders and that's used as a navigation property in the Customer-Orders relationship.
In the Properties window, choose the Edit relationship properties link.
The Edit Relationship dialog box opens.
To modify the relationship, see the appropriate section later in this topic:
Adding a Relationship Between Entities From the Same Data Source.
Adding a Relationship Between Entities From the Separate Data Sources.
Choose the OK button to close the Edit Relationship dialog box.
To delete a relationship
In Solution Explorer, open the shortcut menu for one of the tables that you're using in the relationship, and then choose Open.
Note
For applications that have been upgraded to Visual Studio 2012 Update 2, on the Perspective bar, choose the Server tab.
In the Data Designer, open the shortcut menu for the connecting line that represents the relationship to another entity or table, and then choose Delete Relationship(s).
Multiplicity Settings
The following table describes the combinations of multiplicity settings that you can choose in LightSwitch.
From value | To value | Description |
---|---|---|
One | Zero or one | A "From" can have one or no "To", but a "To" must have a "From". |
One | Many | A "From" can have many "To" instances, but a "To" must have a "From". |
Zero or one | One | A "From" must have a "To", but a "To" can have one or no "From". |
Zero or one | Many | A "From" can have many "To" instances, and a "To" can have one or no "From". |
Many | One | A "From" must have a "To", but a "To" can have many "From" instances. |
Many | Zero or one | A "From" can have one or no "To", and a "To" can have many "From" instances. |
Note
You can't specify a one-to-one, zero or one-to-zero or one, and many-to-many relationship by using the data designer in LightSwitch.
Deletion Behavior Settings
The following table describes the options that you can set to govern what happens when a user tries to delete a record that's part of a relationship. Some options aren't available for certain kinds of relationships.
On Delete Behavior Value | Description |
---|---|
Cascade delete | When a "Table1" is deleted, delete all related "Table2" instances. |
Restricted | "Table1" can't be deleted if related "Table2" instances exist. For example, choose Restricted to specify that a customer can't be deleted if the database contains related orders. |
Disassociate | When "Table1" is deleted, set the reference to "Table1" on related "Table2" instances to null . |