CODEWITHSUNDEEP
entity-frameworkef-code-firstmany-to-many
Alex Guerin
Alex Guerin

Reputation: 2386

Entity Framework: Many-to-Many

I have the following scenario: A Doctor can have multiple Companions. A Companion can also have multiple Doctors. Here are my classses (minus the context):

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

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.

Dim query = From d In context.Doctors
                From c In context.Companions
                Select New DoctorViewModel With {
                    .Actor = d.Actor,
                    .DoctorId = d.DoctorId,
                    .Companions = XXXXXXX}

EDIT: If I query:

(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

I get:

"Unable to cast the type 'System.Collections.Generic.IEnumerable1' to type 'System.Collections.Generic.List1'. LINQ to Entities only supports casting Entity Data Model primitive types."

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?

Upvotes: 0

Views: 226

Answers (2)

Alex Guerin
Alex Guerin

Reputation: 2386

Solved! I've spent days on this! Trick was to change the List inside the Doctor ViewModel to IEnumerable(Of CompanionViewModel)

Upvotes: 0

Dennis Traub
Dennis Traub

Reputation: 51624

It probably would be not only okay but much more readable (aka maintainable) as well. Especially since the query wouldn't return an anonymous type, it would return an IQueryable

Upvotes: 1

Related Questions