Create many-to-many relationships in LINQ for SQL

• Download source - 312 Kb
• Download sample database - 240 Kb

Introduction

Microsoft is working very hard on Visual Studio codename "Orcas", and recently released LINQ CTP (May) and ADO.NET vNext. This article is based on the LINQ May 2006 CTP.

Prerequisites

In order to run the samples and follow the article, you must have:

• Visual Studio 2005
• MS SQL Server 2005
• LINQ May CTP

Start new project

After you have installed LINQ, you will have LINQ related templates in VS2005. So create a new project as follows:

1. Click Start | Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005 menu command.
2. Click the Tools | Options menu command.
3. In Microsoft Visual Studio, click the File | New | Project… menu command.
4. In the New Project dialog, in Project types, click Visual C# | LINQ Preview.
5. In Templates, click LINQ Console Application.
6. Provide a name for the new project by entering LINQRelations in the Name field.
7. Click OK.
8. At the warning dialog, click OK.

Well… now we have a new Windows Forms application with an empty form. Let's generate the code for the database objects.

Using Code Generation to Create the Object Model

1. Attach the database files (from LINQ_Databases.zip) to SQL Server with name MovieCollection.
2. Generating the database table relationships can be tedious and is prone to error. Until Visual Studio is extended to support LINQ, you can run a code generation tool, SQLMetal, manually. Click the Start | Programs | Microsoft Visual Studio 2005 | Visual Studio Tools | Visual Studio 2005 Command Prompt menu item.
3. Execute the following command to change the directory to the project location (change the path to your project's path): cd "D:\Projects\NET\LINQRelations\LINQRelations".
4. Generate the entire MovieCollection class hierarchy, annotated with primary key and foreign key designations, by entering the following command: "C:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:.\SQLExpress /database:" MovieCollection " /pluralize /code:MovieCollection.cs.
5. In Microsoft Visual Studio, in the Solution Explorer, click the LINQRelations | Add | Existing Item menu command.
6. Locate the new MovieCollection.cs file, then click Add.
7. In Solution Explorer, double-click MovieCollection.cs.
8. Compile the project so these objects will be shown in the next steps.

Now, we have generated entities and they are ready to be queried.

List entities using LINQ

1. In Visual Studio 2005 (LINQRelations opened), open the Data Sources window (form menu Data | Show Data Sources).
2. Right-click within the Data Sources window and select Add New Data Source…
3. From the wizard, select the Object for Data source Type, and click Next.
4. Select Movie as shown on the picture below, and click Next | Finish to close the wizard.

   

5. Open Form1.
6. Drag Movie from the Data Sources window and drop on the form.
7. For the form Load event, we have to write some code to load data from the server.

   private void Form1_Load(object sender, EventArgs e)
   {
       MovieCollection colllection = 
         new MovieCollection(@"Data Source=.\SQLEXPRESS;Database" + 
                             @" = MovieCollection;Integrated Security=True;");
       Table<Movie>  movies = colllection.Movies;
   
       movieBindingSource.DataSource = movies;
   }

Execute the project to confirm results. (I added a movie so the table won't be empty). So far so good. Microsoft confirmed that one-to-one and one-to-many relations are supported in the SQL Metal tool (the tool we used to generate code in MovieCollection.cs). What if we are anxious and we would like to support it?

Add many-to-many relations

In this database, we have two entity tables (Movie and Category) and a junction table, MovieCategory, for many-to-many support.

We have to write some code to support it, but it's worth the effort.

First we need access to the DataContext instance

We have to get all objects from one instance. In this way, data consistency is guaranteed. (For more info, see DLinq Hands on Lab for C#/VB.NET form LINQ CTP). In the Program.cs file, write:

private static MovieCollection dataContext = null;
internal static MovieCollection DataContext
{
    get
    {
        if (null == dataContext)
        {
            dataContext = 
              new MovieCollection(@"Data Source=.\SQLEXPRESS;" + 
                                  @"Database = MovieCollection;" + 
                                  @"Integrated Security=True;");
        }

        return dataContext;
    }
}

We will use this Singleton implementation to access the database only from one point. To ensure this, we have to change the Form_Load event to:

private void Form1_Load(object sender, EventArgs e)
{
    movieBindingSource.DataSource = Program.DataContext.Movies;
}

Extend the Movie class

The next thing to do is to extend the Movie class to return all Categories. I prefer to use partial classes in .NET 2.0, so we can differentiate from tool generated code in case we have to re-generate it. In Visual Studio 2005, add a new file called MovieCollectionExtend.cs. Do not forget to surround classes in MovieCollection.cs in the namespace LINQRelations. Now, let's put in action the magic of partial classes. Fill MovieCollectionExtend.cs with the following content:

public partial class Movie
{
    protected EntitySet<Category> categories;
    /// <summary>

    /// Return assigned categories to current movie

    /// </summary>

    public EntitySet<Category>  Categories
    {
        get
        {
            categories = new EntitySet<Category>(onAddCategory, onRemoveCategory);

            var cats = from c in Program.DataContext.Categories
                       join mc in Program.DataContext.MovieCategories 
                               on c.CategoryID equals mc.CategoryID 
                       where mc.MovieID == this.MovieID
                       select c;

            //add to result 

            foreach (var c in cats)
                categories.Add(c);

            return categories;
        }
    }

    protected void onAddCategory(Category arg)
    {
        //add entry to MovieCategories table


            MovieCategory mc = new MovieCategory();
            mc.MovieID = this.MovieID;
            mc.CategoryID = arg.CategoryID;

            Program.DataContext.MovieCategories.Add(mc);
    }
    protected void onRemoveCategory(Category arg)
    {
    }
}

Believe it or not, this is all we need to extract all the categories to a certain movie.

What we've just did?

Let's explain the code step by step. We created a new property for the Movie class called Categories. Inside the setter, we do create a new instance for the Categories collection that will be filled and which will be returned on request. In the constructor are defined two delegates – one that is executed when a new element is added to collection, and one when an element is deleted.

categories = new EntitySet Category (onAddCategory, onRemoveCategory);

The next statement performs a LINQ query against the objects. Unfortunately, we must have access to the DataContext instance at that time.

var cats = from c in Program.DataContext.Categories
           join mc in Program.DataContext.MovieCategories 
                   on c.CategoryID equals mc.CategoryID 
           where mc.MovieID == this.MovieID
           select c;

This query is translated to SQL as:

SELECT [t0].[CategoryID], [t0].[CategoryName]
FROM [Category] AS [t0], [MovieCategory] AS [t1]
WHERE ([t1].[MovieID] = 1) AND ([t0].[CategoryID] = [t1].[CategoryID])

The next two lines are easy: fill the private collection and return it as the result.

//add to result 

foreach (var c in cats)
    categories.Add(c);

return categories;

The delegate onAddCategory is responsible to add a new row in the junction table MovieCategory when assigning a new category to the movie, and onRemoveCategory to delete a row. And this is it.

Are you sure this is all?

Let's create another DataGridView to ensure we achieved the goal.

1. Add a new DataGridView control on the form and call it categoriesDataGridView.
2. In the Form_load event, add a line to auto-generate columns:

   categoriesDataGridView.AutoGenerateColumns = true;

3. Create new event handler on the movieBindingSource for the CurrentChanged event.
4. Write the following code for it:

   private void movieBindingSource_CurrentChanged(object sender, EventArgs e)
   {
       categoriesDataGridView.DataSource = 
         (movieBindingSource.Current as Movie).Categories;
   }

History

• Version 1 - Just released.