DLINQ + Solving the Many to Many problem

A while back, Scott Guthrie posted in the comments, that LINQ didn't support, or had trouble with Many to Many relationships.  I'm not sure if the code-base has changed, or I haven't considered a full scope, but I've managed to get it to work, and it's slick!  I'll jump straight to the magic here. 

I've created a Web Project, and added an SQL Database.  I'm keeping this example simple, so for this example, we have Products, and Categories.  Products can be a member of multiple categories, so lets get started!

I'm using the standard many to many method of using a interim table between the two tables, using a 1 to many on each side.  Here's my LINQ diagram to illustrate:

It's important to note, that for this to work, your interim table MUST have an identity field defined.  In this case I just called it RowID

I've loaded up my tables with some example data to test with, you can create your own, or follow my example.

Ok, from this set of data, we can see that most things only have one category, but both Beer and Meat Cake are both a beverage, and a food!  Right on!  Ok, so now to test the functionality, lets jump into a class

First we need to try a simple select that's meaningful.  In this case, lets select all the products that are beverages, and all the products that are food. :)

Food:

Public Class TestMany
    Public Shared Function Testing()
        Dim db As New ManyToManyDataContext
        Dim query = From p In db.ProductCategories _
                    Where p.Category.CategoryName = "Food" _
                    Select p.Product.ProductName



    End Function
End Class

The important thing to note here, is we START at the linking table between the two

Beverage:

Public Class TestMany
    Public Shared Function Testing()
        Dim db As New ManyToManyDataContext
        Dim query = From p In db.ProductCategories _
                    Where p.Category.CategoryName = "Beverage" _
                    Select p.Product.ProductName



    End Function
End Class

Now - you're saying, but what if I want the products that are JUST in the food AND beverage category.  Here's where it gets a little tricky, but yes my good friend, it can be done!

Dim db As New ManyToManyDataContext
        Dim prod1 = (From c In db.ProductCategories _
                     Where (c.Category.CategoryName = "Beverage") _
                     Select c.Product.ProductName, c.Category.CategoryName)

        Dim prod2 = (From c In db.ProductCategories _
                     Where (c.Category.CategoryName = "Food") _
                     Select c.Product.ProductName, c.Category.CategoryName)

        Dim result = (From c In prod1 Join d In prod2 On c.CategoryName Equals d.CategoryName _
                      Select c.ProductName)

And there you have it.  LINQ's inline ability to query a result set, and JOIN right in code, creates a listing of only products that are in both the food and beverage category. 

Have fun!

Bryan Sampica