The easiest way to create business applications for the Desktop and the Cloud
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.
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.
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.
As with most features, there are always constraints.
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
Very useful in dealing with legacy databases...