How to Create a Many-to-Many Relationship (Andy Kung)

How to Create a Many-to-Many Relationship (Andy Kung)

Rate This
  • Comments 36

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.

image

Start with data

We will start by adding a Movie table and a Genre table, each with some relevant fields.

Movie

  • Title (String, required)
  • ReleaseDate (Date, required)
  • Length (Integer, required)
  • Storyline (String, required)

image

Genre

  • Name (String, required)

image

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.

image

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.

image

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.

image

image

We should now have the 3 tables set up as follow.

image

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

End Sub

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.

Create a Screen

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.

image

Screen designer now appears. You should have a screen like this:
image

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.

image

“List-Box Mover” UI

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.

image

Select Query, Genres, and click OK.

image

You should now see the newly created Genres in the members list of the screen designer.
image

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:

clip_image017

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.

image

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.

image

Set the horizontal and vertical alignment of the Button Group to Left and Center in the Properties.

image

Since we want to have 2 buttons stacked vertically in the middle column, create 2 groups within the Button Group using Rows Layout.

image

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 “>>”.

image

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

            End If

        End Sub

 

        Private Sub RemoveGenre_Execute()

            MovieGenres.DeleteSelected()

        End Sub

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.

image

Hope that helps!

-andy

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • Hi Alex,

    It sounds like your relations aren't set up correctly. Can you make sure you have a Many-to-One relationship defined from MovieGenre to the Genre? Once you do that then you will be able to compute the Summary field correctly.

  • Thank you Andy,

    But, what can we do if what we want is to see all the rows about the movie when we select a movie?. In other words, we want to work with both "primary" tables.

    Thanks.

  • A better example is when we select a Genre, we want to see al the movies in that genre.

    Thank you very much.

  • Does anybody know how to create a query which returns all movies for a selected genre?

    Thanks.

  • Umm... Everything seems to work fine until I try to SAVE the record. I get two validation errors:

    1) Movie: The referenced Movie is either not set or no longer exists.

    2) Genre: The referenced Genre is either not set or no longer exists.

    :(

  • If I try Tom C code, I get an error when tyring to save the record. However, if I just add the lines:

                      MovieGenre mg = this.MovieGenreCollection.AddNew();

                      mg.Movie = this.MovieProperty;

                      mg.Genre = this.GenreCollection.SelectedItem;

    The code work as expected, but I am able to add repeated rows.

  • Andy - thanks for this blog post.  I am using this control in an application at work.  I do have a question, though.  My many to many control has roughly 600 items in it.  Lightswitch sets the list length to 45 and then paginates the rest.  I can not find a way to change the default 45 for the control.  I'd like it to be about 15 or 20 items tall and not 45.  Do you know if this is possible?

    Thanks!

    JDM

  • @Alex. Just making sure, you need to first add a property called "Summary" in the MovieGenre table. Select it and check "Is Computed" checkbox in Properties. Then click on "Edit Method" link below "Is Computed" checkbox and that will bring you to the code editor. Could you confirm that? Thanks!

  • @cte. The list in the example is directly bound to the Genre table. Removing an item in the list will remove the data from the Genre table, which is not what you want. So the answer is, unfortunately, no.

  • @JDM. You can set it on the data property. To specify the number of items in a page, select a collection proptery (ex. MovieGenres in this tutorial) and you will find a "No. of items to display per page" in Properties.

  • Andy, much thanks. Been puzzled since many-to-many was rejected in relationship dialog (LightSwitch 1.0)and have been scouring books and forums for an answer.

  • I am using this with 3 tables Tires, Compatible Tractors, and Tractors.  I want Tractors in list of Tractors to be copied to the Compatible Tractors table.  I think I have the relationships set up correctly. I have the UI set up so I looks like Andy's but when I push the add Tractor button the compatible Tractors list just as a 0 and not the name of the tractor.

    Can someone let me know what I could be missing.

    thanks

  • Hi Andy

    Many thanks for the very useful tutorial.

    A question: How can I enable multi-select of items to be added or removed (using shift/Ctrl-Select)?

    Best regards, Mark.

  • Added Tom C code and it works like a charm! Many thanks Tom C.

  • Just add this line to delete the test for the mgExits instanceafter the test is completed (see comment to the right of the added line below).

           partial void AddGenre_Execute()

           {

    // Write your code here.

               if (Genres.SelectedItem != null)

               {

                   MovieGenre mgExists = new MovieGenre();

                   foreach (MovieGenre mgSearch in this.MovieGenres)

                   {

                       if (mgSearch.Genre.Name == this.Genres.SelectedItem.Name)

                       {

                           mgExists = mgSearch;

                       }

                   }

                   if (mgExists == null || mgExists.Genre == null)

                   {

                       mgExists.Delete(); // Add this line to delete instance after test

                       MovieGenre mg = this.MovieGenres.AddNew();

                       mg.Movie = this.MovieProperty;

                       mg.Genre = this.Genres.SelectedItem;

                   }

               }

Page 2 of 3 (36 items) 123