How to: Define Data Relationships

  • Article

You can create, modify, or delete relationships between data by using the data designer in LightSwitch. Relationships help you 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

link to video 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. However, you cannot use the data designer to add a relationship between entities from the same external data source. 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

  1. In Solution Explorer, double-click one of the tables that you want to use in the relationship.

  2. In the Data Designer, on the command bar, click Relationship.

  3. In the Add New Relationship dialog box, in the Name row, select the two tables to which you want to apply a relationship, for example, Customers and Orders.

  4. In the Multiplicity row, specify how you want these tables to be related. For more information about multiplicity and the kinds of relationships, see Multiplicity Settings.

  5. 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. For more information, see Delete Behavior Settings.

  6. In the Navigation Property row, provide a name for every 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 these objects to construct LINQ expressions in code.

  7. Click OK.

Adding a Relationship Between Tables or Entities from Separate Data Sources

You can use the LightSwitch data designer add a relationship between tables or entities from separate data sources. For example, you can add a relationship between a table in the LightSwitch internal database and an entity from a SharePoint list.

To add a relationship between two tables or entities from separate data sources

  1. In Solution Explorer, double-click one of the tables or entities that you want to use in the relationship.

  2. In the Data Designer, on the command bar, click Relationship.

  3. In theAdd New Relationship dialog box, in the Name row, select the tables or entities to which you want to apply a relationship, for example, Customers and Orders.

  4. In the Multiplicity row, specify how you want these table or entities to be related. For more information about multiplicity and the kinds of relationships, see Multiplicity Settings.

    Note

    Notice that the On Delete Behavior row is not enabled for this kind of relationship.

  5. In the Navigation Property row, provide a name for every 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 these objects to construct LINQ expressions in code.

  6. In the data grid that appears near the bottom of the Add New Relationship dialog box, select a property from one table or entity and a related property in the other table or entity. These properties are where the tables or entities link together. For example, select the Id property of a Customer entity and the CustomerID property of an Order entity.

  7. Click OK.

Modifying a Relationship

You can use the LightSwitch data designer to modify or delete a relationship.

To modify an existing relationship

  1. In Solution Explorer, double-click one of the entities or tables that you are using in the relationship.

  2. In the Data Designer, select the navigation property that represents the relationship to another entity or table. For example, a Customer entity might have a property named Orders that is used as a navigation property in the Customer-Orders relationship.

  3. In the Properties window, click Edit relationship properties.

    The Edit Relationship dialog box opens.

    To modify the relationship, see the appropriate section in this document:

    • Adding a Relationship Between Entities From the Same Data Source.

    • Adding a Relationship Between Entities From the Separate Data Sources.

  4. Click OK.

To delete a relationship

  1. In Solution Explorer, double-click one of the entities or tables that you are using in the relationship.

  2. In the Data Designer, right-click the navigation property that represents the relationship to another entity or table and then click Delete.

Multiplicity Settings

The following table describes the combinations of multiplicity settings that are available 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 cannot specify a one-to-one, zero or one-to-zero or one, and many-to-many relationship by using the LightSwitch data designer.

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 is part of a relationship. Some options may not be 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" cannot be deleted if there are related "Table2" instances.

For example, select Restricted to specify that a customer cannot be deleted if there are related orders in the database.

Disassociate

When "Table1" is deleted, set the reference to "Table1" on related "Table2" instances to null.

See Also

Other Resources

Data: The Information Behind Your Application