The easiest way to create modern business applications for the Cloud and Office 365
Note: This article has been updated for Beta 2 on 3/17/2011
Many business applications require many-to-many relationships. For example, an author can write many books, and a book can be written by many authors. To model this relationship in LightSwitch, you need to create a mapping table for the 2 objects (e.g. AuthorToBook mapping table for Author and Book).
In this post, we will create a movie database application. This application requires a many-to-many relationship for the movie and genre entities. Meaning, each movie can have many genres, and each genre can have many movies. In addition, we will build a “list-box mover” UI to interact with the relationship. To give you an idea of what we’re building, here is a sneak peek of the screen you will end up with by the end of the post.
We will start by adding a Movie table and a Genre table, each with some relevant fields.
Next, we need to create a mapping table between Movie and Genre. Let’s create a new table called MovieGenre, but don’t specify any additional fields.
Then, click “+ Relationship” button in the command bar to open the “Add New Relationship” dialog. We will now set up the many-to-many relationship.
Since it is a mapping table, each entry only contains a reference to a movie and a genre. Using the dialog, we want to make sure “one Movie can have many MovieGenre,” and “one Genre can have many MovieGenre.”
We should now have the 3 tables set up as follow.
We can also add a summary field to the mapping table so it has a meaningful string representation by default.
Private Sub Summary_Compute(ByRef result As String)
result = Genre.Name
For more details of how to customize an entity’s summary field, please see Getting the Most out of LightSwitch Summary Properties by Beth Massi.
Now that we have the data set up, let’s create a screen via the “Add New Screen dialog.” We will use “New Data Screen” template and make sure Movie is selected under “Screen Data” and both Movie Details and Movie MovieGenres are checked. This ensures the screen will include data about the movie as well as its related genres. Click OK.
Screen designer now appears. You should have a screen like this:
If we run the application (F5) at this point, you will see a screen that lets you enter basic movie information as well as a grid for its genres.
Let’s customize the screen to make it more user-friendly by creating a, for lack of a better term, “list-box mover” UI. It has essentially 2 lists. One shows the genres associated with the movie, and the other shows all the possible genres a user can choose from. User can then use an “Add <<” and “Remove >>” button to move things around.
Our screen already has the list of genres associated with the movie (currently showing as a grid). We still need a list of all possible genres. To do this, we will add a screen query that returns all genres. Go back to the screen designer. Click “Add Data Item” button in the command bar.
Select Query, Genres, and click OK.
You should now see the newly created Genres in the members list of the screen designer.
For the “list-box mover” UI, we essentially want 3 columns: movie genre list, button group, and all-genre list. I find it useful to draw out what I want to build in blocks first. In this case:
We will use Columns Layout instead of Rows Layout for the children group. Use the “+ Add” button to add Genres below Movie Genres. Change both collections to use List control instead of Data Grid control. Remove all the commands associated with both Lists.
Now we have 2 columns for genres. We need a middle column for the buttons. Create a group in the middle using Rows Layout called Button Group.
Set the horizontal and vertical alignment of the Button Group to Left and Center in the Properties.
Since we want to have 2 buttons stacked vertically in the middle column, create 2 groups within the Button Group using Rows Layout.
Right click on Group 1 to add a button. Rename the generated method to AddGenre. Similarly, add a button to Group 2 with a RemoveGenre method. We can change the display name of the buttons to be “<<” and “>>”.
Finally, we can write some code for AddGenre and RemoveGenre. Double click on the buttons to edit screen code:
Private Sub AddGenre_Execute()
If (Genres.SelectedItem IsNot Nothing) Then
Dim mg As MovieGenre = MovieGenres.AddNew()
mg.Movie = Me.MovieProperty
mg.Genre = Genres.SelectedItem
Private Sub RemoveGenre_Execute()
That’s it! Now run the application and play with the “list-box mover” UI. In this example, I pre-populated the genre list using another screen.
Hope that helps!
I really like movies, thanks for the valued app!
Thanks for the detailed steps, Andy. Much appreciated!
Fantastic example. I was just starting the process of thinking through how to do this in Lightswitch. You really saved me a bunch of time.
Suggestion: Add some code to the AddGenre_Execute() method to prevent adding duplicates...
partial void AddGenre_Execute()
if (GenreCollection.SelectedItem != null)
MovieGenre mgExists = new MovieGenre();
foreach (MovieGenre mgSearch in this.MovieGenreCollection)
if (mgSearch.Genre.Name == this.GenreCollection.SelectedItem.Name)
mgExists = mgSearch;
if (mgExists == null || mgExists.Genre == null)
MovieGenre mg = this.MovieGenreCollection.AddNew();
mg.Movie = this.MovieProperty;
mg.Genre = this.GenreCollection.SelectedItem;
I'm curious if anyone has used LightSwitch accessing Azure Table Storage? It seems like it should fit and would be a GREAT help in building support tools, etc.
Learnig and with your help, what can I say !!!
great thank you
Thanks for the great example Andy. I tried to modify this slightly by calling 'RemoveSelected' or 'DeleteSelected' on the GenreCollection in the AddGenre method so that Genres are taken out of the listbox of available genres after they have been added to the MovieGenreCollection of the current Movie. However, I get a 'Value can not be null' validation error. Any ideas ??
@Paul. RemoveSelected is for breaking a parent-child relationship on a child item. For example, you remove an order for a customer (but do not delete the order itself). DeleteSelected will actually delete the data. So when you call DeleteSelected on GenreCollection, it is actually trying to delete the selected Genre from the database. I suspect that's why you're seeing the validation error.
What you want is essentially hiding (in the UI) the genres that are already picked, which isn't supported in the list control. You would need to build a special custom control for the many-to-many relationship.
If you simply want to avoid picking the same genre multiple times, Thom C offered a good suggestion in the comments.
Andy, really great teaching, easy to understand and follow.
Why did you keep the ID field in the m to m table? Why not create a compound key?
To avoid adding duplicates:
Private Sub AddGenre_CanExecute(ByRef result As Boolean)
If Genres.SelectedItem IsNot Nothing Then
If Me.MoveGenres.Where(Function(mg) mg.Genre Is Genres.SelectedItem).Any Then
result = False
Andy, what a nice post! Many thanks!
Just excellent. Articles like this give me hope I can learn the screen designer.
Andy, thanks for this posting. It was very useful.
I've just tried the steps you mention in this post and I can't seem to get the Summary_Compute working - I read Beth's post about it, but when I try result = Genre.Name; I get "Cannot resolve symbol Genre" and also "Method 'Summary_Compute' is never used" as error messages before I can even build the application.
Have I overlooked something simple?