This question is locked. New answers and comments are not allowed.
I have a grouping query trying to pull out some summarized appointment data for an export, which was created when my project was using Linq-to-SQL. It worked fine under L2S, but is now breaking after converting to OpenAccess. I have a CurrentStatusID in my appointment table which designates whether the appointment is pending approval, approved, or cancelled. So most of the summary work is to calculate how many of each status there were during a provided date range.
I'm hoping someone has an idea what I need to change in the query to make it work for OpenAccess, or how I can accomplish the same result in a different fashion.
Here's the query line in the code that is bombing (C#):
var ApptList = (from a in db.DBAppointments where (a.RespiteDateIn >= dtmStart.ToUniversalTime() && a.RespiteDateIn < dtmEnd.ToUniversalTime()) group a by a.AppointmentType.TypeName into g select new { VisitType = g.Key, TotalHours = ((g.Count() == 0 ? 0 : g.Sum(a => a.TotalMinutes)) / 60), TotalAppts = g.Count(), CancelledHours = ((g.Where(c => c.CurrentStatusID == 3).Count() == 0 ? 0 : g.Where(c => c.CurrentStatusID == 3).Sum(a => a.TotalMinutes)) / 60), CancelledAppts = g.Where(c => c.CurrentStatusID == 3).Count() });
And here is the error message detail:
System.InvalidOperationException was unhandled by user code
Message="An exception occured during the execution of 'Extent<ScheduleData.DBAppointment>().Where(a => ((a.RespiteDateIn >= value(ScheduleData.reports+<>c__DisplayClass4).dtmStart.ToUniversalTime()) && (a.RespiteDateIn < value(ScheduleData.reports+<>c__DisplayClass4).dtmEnd.ToUniversalTime()))).GroupBy(a => a.AppointmentType.TypeName).Select(g => new <>f__AnonymousType0`5(VisitType = g.Key, TotalHours = (IIF((g.Count() = 0), 0, g.Sum(a => a.TotalMinutes)) / 60), TotalAppts = g.Count(), CancelledHours = (IIF((g.Where(c => (c.CurrentStatusID = 3)).Count() = 0), 0, g.Where(c => (c.CurrentStatusID = 3)).Sum(a => a.TotalMinutes)) / 60), CancelledAppts = g.Where(c => (c.CurrentStatusID = 3)).Count()))'. Failure: Unknown parameter.\r\nParameter name: parameterExpression\r\nActual value was c.\r\nSee InnerException for more details.\r\n"
Source="Telerik.OpenAccess.35.Extensions"
StackTrace:
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQueryMulti[T](Expression expr, ExecutionSettings settings, Object[] grpVals, Boolean checkOid, FetchStrategyBuilder strategyBuilder)
at Telerik.OpenAccess.Query.Piece`1.ExecuteMultiple()
at Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at ScheduleData.reports.RadBtnSchedCSV_Click(Object sender, EventArgs e) in C:\Documents and Settings\cheshire.HISCOM\My Documents\Visual Studio 2008\Projects\ScheduleData\ScheduleData\reports.aspx.cs:line 392
at Telerik.Web.UI.RadButton.OnClick(ButtonClickEventArgs e)
at Telerik.Web.UI.RadButton.RaisePostBackEvent(String eventArgument)
at Telerik.Web.UI.RadButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException: System.ArgumentOutOfRangeException
Message="Unknown parameter.\r\nParameter name: parameterExpression\r\nActual value was c."
Source="Telerik.OpenAccess.35.Extensions"
ParamName="parameterExpression"
StackTrace:
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
InnerException:
I'm hoping someone has an idea what I need to change in the query to make it work for OpenAccess, or how I can accomplish the same result in a different fashion.
Here's the query line in the code that is bombing (C#):
var ApptList = (from a in db.DBAppointments where (a.RespiteDateIn >= dtmStart.ToUniversalTime() && a.RespiteDateIn < dtmEnd.ToUniversalTime()) group a by a.AppointmentType.TypeName into g select new { VisitType = g.Key, TotalHours = ((g.Count() == 0 ? 0 : g.Sum(a => a.TotalMinutes)) / 60), TotalAppts = g.Count(), CancelledHours = ((g.Where(c => c.CurrentStatusID == 3).Count() == 0 ? 0 : g.Where(c => c.CurrentStatusID == 3).Sum(a => a.TotalMinutes)) / 60), CancelledAppts = g.Where(c => c.CurrentStatusID == 3).Count() });
And here is the error message detail:
System.InvalidOperationException was unhandled by user code
Message="An exception occured during the execution of 'Extent<ScheduleData.DBAppointment>().Where(a => ((a.RespiteDateIn >= value(ScheduleData.reports+<>c__DisplayClass4).dtmStart.ToUniversalTime()) && (a.RespiteDateIn < value(ScheduleData.reports+<>c__DisplayClass4).dtmEnd.ToUniversalTime()))).GroupBy(a => a.AppointmentType.TypeName).Select(g => new <>f__AnonymousType0`5(VisitType = g.Key, TotalHours = (IIF((g.Count() = 0), 0, g.Sum(a => a.TotalMinutes)) / 60), TotalAppts = g.Count(), CancelledHours = (IIF((g.Where(c => (c.CurrentStatusID = 3)).Count() = 0), 0, g.Where(c => (c.CurrentStatusID = 3)).Sum(a => a.TotalMinutes)) / 60), CancelledAppts = g.Where(c => (c.CurrentStatusID = 3)).Count()))'. Failure: Unknown parameter.\r\nParameter name: parameterExpression\r\nActual value was c.\r\nSee InnerException for more details.\r\n"
Source="Telerik.OpenAccess.35.Extensions"
StackTrace:
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
at Telerik.OpenAccess.Query.ExpressionExecution.PerformDatabaseQueryMulti[T](Expression expr, ExecutionSettings settings, Object[] grpVals, Boolean checkOid, FetchStrategyBuilder strategyBuilder)
at Telerik.OpenAccess.Query.Piece`1.ExecuteMultiple()
at Telerik.OpenAccess.Query.Piece`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at ScheduleData.reports.RadBtnSchedCSV_Click(Object sender, EventArgs e) in C:\Documents and Settings\cheshire.HISCOM\My Documents\Visual Studio 2008\Projects\ScheduleData\ScheduleData\reports.aspx.cs:line 392
at Telerik.Web.UI.RadButton.OnClick(ButtonClickEventArgs e)
at Telerik.Web.UI.RadButton.RaisePostBackEvent(String eventArgument)
at Telerik.Web.UI.RadButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException: System.ArgumentOutOfRangeException
Message="Unknown parameter.\r\nParameter name: parameterExpression\r\nActual value was c."
Source="Telerik.OpenAccess.35.Extensions"
ParamName="parameterExpression"
StackTrace:
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQueryImpl(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
at Telerik.OpenAccess.Query.ExpressionCompiler.PerformDatabaseQuery(Type type, Int32 elementAt, Object[] groupResolutionParamValues, Boolean single, Boolean checkOid)
InnerException: