title: Entity Framework: Many-to-Many
body: <p>I have the following scenario: A <code>Doctor</code> can have multiple <code>Companions</code>. A <code>Companion</code> can also have multiple <code>Doctors</code>. Here are my classses (minus the context):</p>
  
  <pre><code>Public Class Doctor
  
      Public Property DoctorId As Integer
      Public Property Regeration As Integer
      Public Property Name As String
      Public Property Actor As String
      Public Property Companions As List(Of Companion)
  
  End Class
  
  Public Class Companion
  
      Public Property CompanionId As Integer
      Public Property Name As String
      Public Property Actor As String
      Public Property Doctors As List(Of Doctor)
  
  End Class
  
  Public Class DoctorViewModel
  
      Public Property DoctorId As Integer
      Public Property Actor As String
      Public Property Companions As List(Of CompanionViewModel)
  
  End Class
  
  Public Class CompanionViewModel
  
      Public Property Actor As String
  
  End Class
  </code></pre>
  
  <p>I'm trying to fetch a singular Doctor with a list of companions who have travelled with him. This I can do quite simply, but I'm trying to shape the query to get only a few columns and not the entire entity. He is my bungled query - the X's are what I can't figure out.</p>
  
  <pre><code>Dim query = From d In context.Doctors
                  From c In context.Companions
                  Select New DoctorViewModel With {
                      .Actor = d.Actor,
                      .DoctorId = d.DoctorId,
                      .Companions = XXXXXXX}
  </code></pre>
  
  <p><strong>EDIT:</strong> If I query:</p>
  
  <pre><code>(From d In Tardis.Doctors
   Where d.Actor = "Tom Baker"
   Select New DoctorViewModel With {.Actor = d.Actor, .Companions = d.Companions.Select(Function(c) New CompanionViewModel With {.Actor = c.Actor})}).SingleOrDefault
  </code></pre>
  
  <p>I get: </p>
  
  <blockquote>
    <p>"Unable to cast the type 'System.Collections.Generic.IEnumerable<code>1' to
    type 'System.Collections.Generic.List</code>1'. LINQ to Entities only
    supports casting Entity Data Model primitive types."</p>
  </blockquote>
  
  <p>Would it be considered nasty to ditch the ViewModel classes in the query and just get the stuff as an anonymous type, then pass this to a constuctor in a ViewModel (my models have a whack of functions that are needed) and fill the class like this?</p>