User Defined Relationships within Attached Database Data Sources (Michael Simons)

The first release of Visual Studio LightSwitch (LightSwitch V1) allows users to define relationships between tables within the intrinsic/built-in data source (ApplicationData).  When attaching to existing data sources, LightSwitch will import the relationships defined within the data source.  In addition LightSwitch allows users to define relationships between tables of different data sources, these are called virtual relationships.  LightSwitch V1 does not however allow users to define relationships within any attached data sources.  This can be an issue when attaching to certain database data sources because it is common for the logical relationships to not be defined within the database schema as referential integrity constraints.

When you define relationships between your entities, LightSwitch can provide a much better experience when creating screens. Instead of manually defining queries to pull related records of data, LightSwitch can do that automatically for you based on the relationship.  In addition, if you do need to define custom queries, having the relationships defined often makes it easier to filter the data as desired.

Within Visual Studio 11 Beta, a feature has been added to LightSwitch that allows users to define relationships within attached database data sources.  These relationships behave just as if they were modeled within the data source.

Example

Let’s take a look at how to define a relationship within an attached database data source within LightSwitch.  For this example, I have defined the following data schema in a SQL database.  Notice the SalesOrder contains a Customer foreign key but there is no relationship defined between SalesOrder and Customer.

Because the database does not define this relationship, LightSwitch will not import it when attached to.  To define this relationship, you can use the Add Relationship functionality within LightSwitch.

From the Add New Relationship dialog, you must first pick which tables you want to relate.

If two tables are picked within the same attached database data source, a field mapping section will appear within the Add New Relationship dialog.  You must pick the fields the relationship is defined on.  This may look familiar as it is the same experience for defining a virtual relationship.  Validation messages at the bottom of the dialog will guide you in defining the relationship correctly.

In addition to defining the field mappings, you will also be able to define the multiplicity and navigation property names.

Once you press OK on the Add New Relationship dialog, the relationship will be added and will appear within the table designer.

One thing I find useful is to uncheck the Display by Default property of all foreign key properties that are part of these user defined relationships because the associated reference property is going to be displayed by default and that is what the end user is going to want to see.

Once the relationship is defined, it can be used just as if the relationship was defined within the data source.  To illustrate this, you can define a screen that shows all SalesOrders for Customers who live in a particular state.  To do this you would first need to create an Editable Grid screen for the SalesOrders EntitySet.

Next click on the Edit Query link of the SalesOrders screen member and define the query to look like the following.

Once this is done, you should navigate back to the screen designer and drag the State parameter onto the screen.  Also for demonstration purposes, you can add the Customer.State property to the screen.

When this application is run, you should see something similar to the following.

When I enter in a state parameter value, you can see how the filter previously defined on the relationship works.

Detailed Behavior

As noted earlier, any relationships defined within an attached database data source can de used just as if the relationship was defined within the database schema.

1. You can define screens that display data across the relationship.
2. You can define queries that filter and sort data across the relationship.
3. You can define screens that eagerly load data across the relationship. This can improve the performance of loading certain types of screens.
4. When inserting, updating or deleting data you don’t need to worry about persistence ordering because LightSwitch will handle all of this for you.  For example suppose a ChangeSet is constructed that contains a new Customer instance and a new SalesOrder instance for the Customer. If the Customer table has an automatically assigned identity within the database, then Lightswitch will ensure the customer is inserted first so its identity is established. Once this is done the Customer foreign key held by the SalesOrder’s will be updated by LightSwitch so that it correctly references the newly inserted Customer.

As with most features, there are always constraints.

1. Relationships cannot be defined between tables of attached OData, SharePoint, and Custom RIA data sources.  They can only be defined within the intrinsic data source as well as attached database data sources.  If you are working with a custom RIA data source, then you own/wrote the data source and can define the relationship yourself within your custom data source implementation.  If you are working with a credible OData or SharePoint data source, then it should have already defined the logical relationships that exist and there should be no need to define any other relationships.
2. Relationships must be defined on the primary key of the table on the primary side of the relationship.  You will not be able to define relationships that are based on shared/common values.
3. The multiplicity of the relationship must match the nullability of the foreign key properties.  If the foreign key property is required then the relationship must be a ‘One’ on the foreign key side of the relationship.  Likewise if the foreign key property is nullable, then the relationship must be a ‘Zero or One’ on the foreign key side of the relationship.
4. In some databases, nullable columns are not used to indicate an optional foreign key. Instead, alternative values, such as empty string or 0 are used. This is commonly referred to as a sentinel value.  In these situations, you will be forced to model the associations as required (1-Many instead of 0..1-Many) and updates to data source may be blocked by the built in validation logic if the sentinel value does not reference a valid row in the destination table.

Conclusion

With LightSwitch in Visual Studio 11 you can now define relationships in external database data sources.  Hopefully you will find this feature useful and that it will allow you to more easily build rich applications with LightSwitch.

-Michael Simons, Visual Studio Team