Same data being returned by linq for 2 different executions of a stored procedure?

Question

Hello

I have a stored procedure that I am calling through Entity Framework.

The stored procedure has 2 date parameters. I supply different argument in the 2 times I call the stored procedure. I have verified using SQL Profiler that the stored procedure is being called correctly and returning the correct results.

When I call my method the second time with different arguments, even though the stored procedure is bringing back the correct results, the table created contains the same data as the first time I called it.

dtStart = 01/08/2009  
dtEnd = 31/08/2009

public List<dataRecord> GetData(DateTime dtStart, DateTime dtEnd)
{
  var tbl = from t in db.SP(dtStart, dtEnd)                       
                      select t;
  return tbl.ToList();            
}

GetData((new DateTime(2009, 8, 1), new DateTime(2009, 8, 31))
// tbl.field1 value = 45450 - CORRECT

GetData(new DateTime(2009, 7, 1), new DateTime(2009, 7, 31))
// tbl.field1 value = 45450 - WRONG 27456 expected 

Is this a case of Entity Framework being clever and caching? I can't see why it would cache this though as it has executed the stored procedure twice.

Do I have to do something to close tbl?

• using Visual Studio 2008 + Entity Framework.
• I also get the message "query cannot be enumerated more than once" a few times every now and then, am not sure if that is relevant?

FULL CODE LISTING

namespace ProfileDataService
{
    public partial class DataService
    {

        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);

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

            return tbl.ToList();            
        }
}
}

/// CALLER

List<MeterTotalConsumpRecord> _P1Totals;
List<MeterTotalConsumpRecord> _P2Totals;

 public void LoadData(int nUserID, int nCustomerID, ELocationSelectionMethod locationSelectionMethod, string strLocations, bool bIncludeClosedLocations, bool bIncludeDisposedLocations,
            DateTime dtStart, DateTime dtEnd, ReportsBusinessLogic.Lists.EPeriodType durMainPeriodType, ReportsBusinessLogic.Lists.EPeriodType durCompareToPeriodType, ReportsBusinessLogic.Lists.EIncreaseReportType rptType,
            bool bIncludeDecreases)
{

   ///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);

}


 void PopulateTotals(List<MeterTotalConsumpRecord> objTotals, int nPeriod)
 {
        MeterTotalConsumpRecord objMeterConsumption = null;

        foreach (IncreaseReportDataRecord objLine in _Lines)
        {
            objMeterConsumption = objTotals.Find(delegate(MeterTotalConsumpRecord t) { return t.MeterID == objLine.MeterID; });

            if (objMeterConsumption != null)
            {
                if (nPeriod == 1)
                {
                    objLine.P1Consumption = (double)objMeterConsumption.Consumption;
                }
                else
                {
                    objLine.P2Consumption = (double)objMeterConsumption.Consumption;
                }

                objMeterConsumption = null;
            }
        }
    }
}

Answer 1

Consider changing the DataService class to look like this:

public partial class DataService
{
    public List<MeterTotalConsumpRecord> 
                      GetTotalAllTimesConsumption(SearchCriteria sc)
    {    
        var db = new dbChildDataContext(); // new every time, just in this test case.

        var totalConsumption = db.GetTotalConsumptionByMeter(sc.DtStart, 
                                                             sc.DtEnd, 
                                                             sc.ug, 
                                                             sc.MeterSelectionType, 
                                                             sc.CustomerID, 
                                                             sc.UserID, 
                                                             sc.Selection, 
                                                             sc.ClosedLocations, 
                                                             sc.DisposedLocations, 1)
                                 .ToList();                       

        //inspect how many results are returned.
        int rowCount = totalConsumption.Count;


        return totalConsumption;

    }
} 


//use objects to pass between classes
public class SearchCriteria
{
    public DateTime DtStart {get;set;}
    public DateTime DtEnd {get;set;}
    public int ug {get;set;}
    public int MeterSelectionType {get;set;}
    public int CustomerID {get;set;}
    public int UserID {get;set;}
    public string Selection {get;set;}
    public bool ClosedLocations {get;set;}
    public bool DisposedLocations  {get;set;}
}