CODEWITHSUNDEEP
asp.netlinq-to-sql
skyfoot
skyfoot

Reputation: 20769

How to force linq to Sql to execute query straight away

I have a repeater of controls on an aspx page. I am trying to move a query out of the control and into the parent page as there is a massive hit on the DB for each control. The problem is that I get the following error.

Cannot access a disposed object.
Object name: 'DataContext accessed after Dispose.'.

I don't understand this as I thought that doing .ToList() forces the query to be executed

My code in the Parent page

Private _activityList As IEnumerable(Of Activity)
Public ReadOnly Property ActivityList() As IEnumerable(Of Activity)
    Get
        Return _activityList
    End Get
End Property

Sub PopulatePage()
    Dim activityList = From a In dbContext.Activities Where a.PA.PA_Key.ToUpper().Trim() = "DCC" _
        Select a

    _activityList = activityList.AsEnumerable()
End Sub

The code in my control is:

Public _activityList As List(Of Activity)
Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
    _activityList = CType(Me.Page, ParentPage).ActivityList.ToList()
End Sub

Sub grdSelectedActivities_NeedDataSource(ByVal source As Object, ByVal e As Telerik.WebControls.GridNeedDataSourceEventArgs) Handles grdSelectedActivities.NeedDataSource

    Dim lnqActivities = _activityList
    Dim objActivity As Activity = (From x In lnqActivities Where x.AC_Code = ActivityCode Select x).Single

    Dim lnqRoundActivities = (From roundactivity In objActivity.RoundActivities Where roundactivity.RA_DS_Code = DepartmentalSettingsCode 
                                  Select roundactivity Order By roundactivity.RA_Name)

    grdSelectedActivities.DataSource = lnqRoundActivities
End Sub

EDIT

I think that is is failing as it is trying to get RoundActivities in the grdSelectedActivities_NeedDataSource control method. Therefore I need to send an Activities object which has RoundActivityies child objects.

I have tried to create this object but get the following error:

Explicit construction of entity type 'Activity' in query is not allowed.
enter code here

This is the updated code:

 Dim activityList = (From a In dbContext.Activities Where a.PA.PA_Key.ToUpper().Trim() = "DCC" Select New Activity With {.AC_Code = a.AC_Code, .RoundActivities = a.RoundActivities})

Solution:

I followed @kristoferA advice and did the following

Dim loadOptions = New DataLoadOptions()
loadOptions.LoadWith(Of Activity)(Function(a As Activity) a.RoundActivities)
loadOptions.AssociateWith(Of Activity)(Function(a As Activity) a.RoundActivities.Where(Function(z) If(z.RA_DS_Code = departmentCode, False)))
dataContext.LoadOptions = loadOptions

Upvotes: 2

Views: 808

Answers (3)

aifarfa
aifarfa

Reputation: 3939

I guest that the dbContext get disposed after PopulatePage() done and your Linq select return type is IEnumerable(Of Activity) which's just a prepared SQL statement.

try..

_activityList = activityList.ToList()//.AsEnumerable()

Edit

if your Model is stateless you can create new dbContext then enumerate and dispose it.

Using db As New dbContext()

    Dim activityList = (From a In dbContext.Activities Where a.PA.PA_Key.ToUpper().Trim() = "DCC" _
    Select a).ToList()

    _activityList = activityList.ToList()
End Using

Upvotes: 0

KristoferA
KristoferA

Reputation: 12397

Sounds like you are lazy loading some association.

Turn off lazy loading (dc.DeferredLoadingEnabled = false), and pass a DataLoadOptions object to dc.LoadOptions to instruct the DC what you want to eager load.

Upvotes: 1

Stéphane
Stéphane

Reputation: 11894

ai.farfa is correct with the ToList(). That is the call that trigger the actual query. I think you should do it on this line though : 

 grdSelectedActivities.DataSource = lnqRoundActivities.ToList()

Upvotes: 0

Related Questions