query results cannot be enumerated more than once error

Question

Hello

I have a problem where I call a stored proedure twice, each with different parameters and I need 2 seperate lists but linq is caching the data and also giving me the error above

I have tried 2 different methods to get round the error message, one using ToList() on tbl and the other manually walking through the data

My code is shown below

CODE FOR GETTING THE TOTALS

  public static List<MeterTotalConsumpRecord> GetTotalAllTimesConsumption(DateTime dtStart, DateTime dtEnd, EUtilityGroup ug, int nMeterSelectionType, int nCustomerID, 
        int nUserID, string strSelection, bool bClosedLocations, bool bDisposedLocations)
    {

        dbChildDataContext db = DBManager.ChildDataConext(nCustomerID);

        db.MeterTotalConsumpRecordSet.MergeOption = System.Data.Objects.MergeOption.NoTracking;
        var tbl = from t in db.GetTotalConsumptionByMeter(dtStart, dtEnd, (int) ug, nMeterSelectionType, nCustomerID, nUserID, strSelection, bClosedLocations, bDisposedLocations, 1)                       
                  select t;

        List<MeterTotalConsumpRecord> objList = new List<MeterTotalConsumpRecord>();

        foreach (MeterTotalConsumpRecord objRecord in tbl)
        {
            objList.Add(objRecord);
        }
        return objList;

    }

    public static List<MeterTotalConsumpRecord> GetTotalDayConsumption(DateTime dtStart, DateTime dtEnd, EUtilityGroup ug, int nMeterSelectionType, int nCustomerID,
                int nUserID, string strSelection, bool bClosedLocations, bool bDisposedLocations)
    {
        dbChildDataContext db = DBManager.ChildDataConext(nCustomerID);
        db.MeterTotalConsumpRecordSet.MergeOption = System.Data.Objects.MergeOption.NoTracking;
        var tbl = from t in db.GetTotalConsumptionByMeter(dtStart, dtEnd, (int)ug, nMeterSelectionType, nCustomerID, nUserID, strSelection, bClosedLocations, bDisposedLocations, 3)
                  select t;

        return tbl.ToList();
    }

{

...Code for setting properties using parameters..

_P2Totals = ProfileDataService.DataService.GetTotalAllTimesConsumption(_P2StartDate, _P2EndDate, EUtilityGroup.Electricity, 1, nCustomerID, nUserID, strLocations, bIncludeClosedLocations, bIncludeDisposedLocations);

    _P1Totals = ProfileDataService.DataService.GetTotalAllTimesConsumption(_StartDate, _EndDate, EUtilityGroup.Electricity, 1, nCustomerID, nUserID, strLocations, 
        bIncludeClosedLocations, bIncludeDisposedLocations);


    PopulateLines() //This fills up a list of objects with information for my report ready for the totals to be added

    PopulateTotals(_P1Totals, 1);
    PopulateTotals(_P2Totals, 2);

}

I get the error the second time I go into GetTotalAllTimesConsumption

Is there a way I can go back to the start of the list? There is a method called FirstOrDefault not sure if this would help?

Cheers

Paul

Answer 1

What error are you getting? You havent specified it. Try re-creating the data context on each call?

Ps: To convert to a list on a linq query, you can just call .ToList() on the result from the linq query