LINQ many-to-many relationships: Solution?

Answer 1

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?

Answer 2

Yes. Instead of many-to-many use two many-to-one relationships:

Subject -*----1- ArtworkSubjectParticipation -1----*- Artwork

Answer 3

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 4

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