Entity Framework - Keep loaded/included related objects after end using?

Question

I am trying to have a query in a method that looks like this:

Public Shared Function listParticipationsByTeamCount(ByVal count As Integer, ByVal challenge As Challenge) As List(Of Participation)
    Dim participationList As List(Of Participation)

    If count <> Nothing And challenge IsNot Nothing Then
        Using db As New DatabaseEntities()
            participationList = db.Participations.Where(Function(x) x.TeamCount = count And x.Challenge.Id = challenge.Id).OrderByDescending(Function(x) x.TeamCount).ThenBy(Function(x) x.Team.Name).ToList()
        End Using
    End If

    Return participationList
End Function

I have a Participation table, that has a many to 1 relationship between Participation and Team table, and a many to many relationship between Participation and TeamMember table. On my markup page I try something like this when I iterate through the list like so:

<% For Each participation As Participation In participationsList%>
    <tr>
        <td><a class="external-link" href="<%= participation.Team.Website %>"><%= participation.Team.Name%></a></td>
        <td><%= participation.Percentage%>%</td>
        <td>
            <%  For Each member As TeamMember In participation.TeamMembers%>
                <%= member.Name%><br /> 
            <% Next%>
        </td>
    </tr>
<% Next%>

I get the following error:

The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

Now I understand that is because I put the query inside a using and after the End Using I can't get the related objects, upon looking this up I tried changing the query the using statement to this:

Using db As New DatabaseEntities()
        participationList = db.Participations.Include("Team").Include("TeamMember").Where(Function(x) x.TeamCount = count And x.Team.Id = team.Id).OrderByDescending(Function(x) x.TeamCount).ThenBy(Function(x) x.Team.Name).ToList()
End Using

This didn't work. I also tried loading entity references like so:

Using db As New DatabaseEntities()
        participationList = db.Participations.Where(Function(x) x.TeamCount = count And x.Team.Id = team.Id).OrderByDescending(Function(x) x.TeamCount).ThenBy(Function(x) x.Team.Name).ToList()
    For each part as Participation in participationList
            part.TeamReference.Load()
            part.TeamMembers.Load()
    Next
End Using

The error still persisted. How can I load all these related objects into my participationList so I can reference them still after the I End Using??? I know EF4 now does lazyloading by default, but even when I explicitly load related objects it still doesn't seem to be working.

Edit: Thanks for all the answers all, it was a matter of not pluralizing the TeamMembers include, since it was a collection. This broke the whole query. So the answer is:

Using db As New DatabaseEntities()
        participationList = db.Participations.Include("Team").Include("TeamMembers").Where(Function(x) x.TeamCount = count And x.Team.Id = team.Id).OrderByDescending(Function(x) x.TeamCount).ThenBy(Function(x) x.Team.Name).ToList()
End Using

Using the Include and calling ToList should get all the needed data and the ObjectContext should not be needed later. Could you check the query being sent to the DB? Sometimes EF ignores the Include although this case it shouldn't. — amit_g, Mar 24 '11 at 17:00
In `If count <> Nothing And team IsNot Nothing Then` where is `team` coming from? Which line in your markup is causing the exception? Is this asp.net mvc? — Brian Cauthon, Mar 25 '11 at 15:09
Ah sorry Brian, i was abstracting my object names to give no indication of where I work (as that would have happened) and forgot to change one. The specific line that is causing the error is : `<%= participation.Team.Name%> ` When it tries to get the related object. No it's not MVC. — SventoryMang, Mar 25 '11 at 15:25

score 3 · Answer 1 · answered Mar 24 '11 at 16:17

3

The problem appears to be that you are setting up the query but not executing it.

You only have access to the database when you are inside the using statement.
The request is sent to the database when you actually try to use the data.

So you should loop through your data and place it inside a domain object, before exiting the using statement.

answered Mar 24 '11 at 16:17

Shiraz Bhaiji

64,065
34
143
252

I am already doing this. From my first block of code you can see I am storing in a list of domain objects, and by setting the query .ToList() it forces it to enumerate within the using. Also you can see within my last code block in my post, I did try iterating through the list to explicitly load the objects. So it seems like I am already doing what you suggested and it doesn't remove the error. – SventoryMang Mar 25 '11 at 14:48
What is the type of your participationList? – Shiraz Bhaiji Mar 25 '11 at 14:51
Look in my first block of code, it is `Dim participationList As List(Of Participation)`. In my lower blocks, I was just replacing what was inside the using statement of the first block. – SventoryMang Mar 25 '11 at 14:57
Use SQL profiler to check which SQL statement is being sent to the database. – Shiraz Bhaiji Mar 25 '11 at 15:31

score 2 · Accepted Answer · answered Mar 25 '11 at 15:20

2

In the third code block you are including TeamMember. However, from what I can see in the second code block, your navigation properties are pluralized, so try including TeamMembers instead.

answered Mar 25 '11 at 15:20

bernhof

6,219
2
45
71

Heh I think this was it. While Team object wasn't being included either, the fact that the second include was singular I think that broke the whole thing. No errors after doing this. – SventoryMang Mar 25 '11 at 15:50

Entity Framework - Keep loaded/included related objects after end using?

2 Answers2