Click here to Skip to main content
Click here to Skip to main content

Create many-to-many relationships in LINQ for SQL

, 1 Sep 2006
Rate this:
Please Sign up or sign in to vote.
Creating many-to-many relationships in LINQ for SQL.

Sample Image

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.

VS 2005 LINQ template

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.

    Add new data source

  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.

database design

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Galin Iliev [Galcho]
Software Developer (Senior)
Bulgaria Bulgaria
Galin has entered in area of software development in end of 20th centutry with Pascal and assembler. His career went through C++, Visual Basic, ASP, InstallShiled, Wise and etc...
For last four years he has been involved in developing all kind of software in .NET.
He holds MCSD, MCAD, MCSD.NET, MCPD and MCT certificates.

Comments and Discussions

 
GeneralThis code is dangerous! Pinmemberclrhungry4-Jul-08 6:40 
GeneralRe: This code is dangerous! PinmemberGalin Iliev31-Jul-08 0:47 
GeneralIdentity Columns PinmemberMember 366062930-Jun-08 3:31 
GeneralVery good PinmemberJean-Mi11-Sep-06 15:59 
GeneralRe: Very good PinmemberGalcho12-Sep-06 1:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 1 Sep 2006
Article Copyright 2006 by Galin Iliev [Galcho]
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid