Geeks With Blogs

Joe Mayo

Common questions when learning LINQ to SQL revolve around many-to-many relationships. This post will describe a scenario that needs queries across many-to-many relationships, show the data and the LINQ to SQL representation of that data, explain the query, and finally output the results.

As you know, the design for many-to-many involves a join table. The following figure illustrates a many-to-many relationship between Tags and Tasks with a TasksTags join table:

 LINQ to SQL many-to-many

A scenario for working with this data is to group all tags under the tasks they're associated with. Suppose the tags table has the following data:

2 Twitter
3 C#
6 Visual Studio 2010

The Tasks table has the following data:

1 Support Streaming
2 Update Tutorial
3 Finish Chapter

and the the TasksTags table has the following information:

1 1
1 2
2 3
3 4
3 6

The trick is to start the query from the join table, TasksTags, and then do LINQ to SQL joins to the Tasks table and then the Tags table, followed with a group by.  Here's the entire query, including grouping:

var ctx = new ToDoDataContext();

var tagsPerTask =
    from taskTag in ctx.TasksTags
    join task in ctx.Tasks
        on taskTag.TaskID equals task.TaskID
    join tag in ctx.Tags
        on taskTag.TagID equals tag.TagID
    group taskTag by taskTag.Task into taskTagGroup
    select new
        Task = taskTagGroup.Key,
        Tags = taskTagGroup.Select(task => task.Tag)

 After the joins, the group by clause saves into a continuation variable, taskTagGroup.  You can use taskTagGroup for custom projections on the data, making it easier to get to organize the results. In the projection, Key is Task because the Task was selected in the group by.  Remember to group by the object, rather than it's key so you can access all of the grouped objects later. Tags is populated through another LINQ to SQL query that only selects the Tags related to each task. Here's how you can get to the results with a Console application:

foreach (var taskGroup in tagsPerTask)
    Console.WriteLine("Task: " + taskGroup.Task.Name);

    foreach (var tag in taskGroup.Tags)
        Console.WriteLine(" Tag: " + tag.Name);

The first foreach will iterate through tasks, because we want to group by tasks, using the Task property created in the custom projection in the LINQ to SQL query above.  Because we have the whole task, it's possible to access all it's members.  The nested foreach loop will iterate through the Tags property of each Task group. The Tags was populated in the custom projection of the LINQ to SQL query above. Here's the output:

 Task: Support Streaming
    Tag: LINQ
    Tag: Twitter
Task: Update Tutorial
    Tag: C#
Task: Finish Chapter
    Tag: MVC
    Tag: Visual Studio 2010

By starting at the join table, and pulling all the information together with LINQ joins before doing the group by, you can successfully organize the data into groups.


Posted on Saturday, January 9, 2010 5:58 PM C# , LINQ | Back to top

Copyright © Joe Mayo | Powered by: