Does the same data return linq for two different executions of the stored procedure?

Question

I have a stored procedure that I call through the Entity Framework.

The stored procedure has 2 date parameters. I supply 2 times different arguments when I call a stored procedure. I checked with SQL Profiler that the stored procedure is called correctly and returns the correct results.

When I call my method for the second time with different arguments, although the stored procedure returns the correct results, the created table 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 an example that the Entity Framework is smart and caching? I don’t understand why he will cache this, because since he executed the stored procedure twice.

- , tbl?

• Visual Studio 2008 + Entity Framework.
• " " , , , ?

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

DataService :

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