title: ORM and many-to-many relationships
body: <p>This is more or less a general question and not about any specific ORM or language in particular:  this question comes up regardless of your ORM preference.  </p>
  
  <p>When mapping a many-to-many relationship it is possible to obscure the intermediary table or to make the intermediary table a part of your model.  In the case that the intermediary table has valuable data beyond the relationship, how do you handle the mapping?</p>
  
  <p>Consider the following tables:</p>
  
  <pre><code>CaseWorker (id, first_name, last_name)
  CaseWorkerCases (case_worker_id, case_id, date_opened, date_closed)
  Case (id, client_id, field_a, field_b)
  </code></pre>
  
  <p>As a programmer I would really rather be able to do:</p>
  
  <pre><code>CaseWorker.Cases
  </code></pre>
  
  <p>than</p>
  
  <pre><code>CaseWorker.CaseWorkerCases.Cases
  </code></pre>
  
  <p>On the one hand, the table CaseWorkerCases contains useful data and hiding the intermediary table makes accessing that data less than convenient.  On the other, having to navigate through the intermediary table makes the common task of accessing Cases seem awkward.</p>
  
  <p>I supose one solution could be to expose the intermediate table in the model and then give the CaseWork object a wrapper property could work.  Something like:</p>
  
  <pre><code>public IEnumerable&lt;Case&gt; Cases
  {
      get{return (from caseWorkerCase in this.CaseWorkerCases
                  select caseWorkerCase.Case);}
  }
  </code></pre>
  
  <p>But that also seems wrong.</p>