Same data being returned by linq for 2 different executions of a stored procedure?
- by Paul
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;
}
}
}
}
