LINQ many-to-many relationships: Solution?

Question

LINQ so far has been remarkably elegant, but to perform basic m2m queries it offers no solution I can imediately see.

What's worse is that while it works for any other table relationship, LINQ is not giving me an association in the class structure for my m2m table.

So I can do things like

artwork.artists.where(...)
//or
artist.Artworks.add(artwork)

but I can't do

artwork.artowrks_subjects.tagSubjects.where(...)
//or
tagSubject.artworks_subjects.add(artwork)

alt text http://img299.imageshack.us/img299/257/20090902122107.png

Is there a common pattern for solving this limitation?

Answer 1

the way I have gotten M2M working in LINQ2SQL:

1. drag the tables into the builder, like you're showing in the question
2. remove the relationship between artworks_subject and artwork
3. create a new relationship FROM artworks_subject TO artwork
4. click on the new relationship to get its properties
5. change the cardinality from OneToMany to OneToOne (because ManyToOne doesn't exist)
6. open the Child Property section and change the Name field to make it singular (Artworks to Artwork)

now the tagSubject entity will have a collection of artwork_subjects, and the artwork_subject will have a property of type artwork called Artwork. so you can now make a LINQ expression like

var x = dbcontext.tagSubjects.Single(s=>s.name=="Landscape").
    Artwork_Subjects.
    Select(as=>as.Artwork.Name);

Answer 2

Found the solution myself. For automated relationships to work, both tables need primary keys (oops). Notice artworks_subjects is missing the PK symbol.

Answer 3

This largely depends on what framework you are using. It sounds like you are using LINQ-to-SQL, which is very literal about tables to objects. With Entity Framework, there is inbuilt support for many-to-many, in particular for the trivial case you've listed (a linking table with no additional properties). EF will generally spot this pattern, and hide the link table from the conceptual model (I can't remember, but it might need a spanning PK over the two FK columns).

Of course, this then goes sour if you want to add columns to the link-table; so in some ways I'd be tempted to leave it "as is".

With regards the where etc - how do you mean? You can do joins over the association, and you should be able to use Any etc; do you have a concrete example of what you want to do?