6

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?

Sam Harwell
  • 97,721
  • 20
  • 209
  • 280
Justin Alexander
  • 2,004
  • 3
  • 21
  • 25

4 Answers4

5

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);
Mike Jacobs
  • 509
  • 3
  • 4
3

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

Justin Alexander
  • 2,004
  • 3
  • 21
  • 25
2

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?

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • pretty much exactly what I showed. The goal is to add a tag to the artwork object. The only reasonable join is Many to Many. The only way I've seen so fart to do it with LINQ-to-SQL is to run a loop, creating artwork_subject objects, and binding them manually. What I find so odd, is that I can't even do an one to many join between the artwork and artwork_subjects tables (same applies to tagSubjects and artwork_subjects). – Justin Alexander Sep 02 '09 at 10:59
-1

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

Subject -*----1- ArtworkSubjectParticipation -1----*- Artwork
Dmytrii Nagirniak
  • 23,696
  • 13
  • 75
  • 130