Complex Grouping Query from Linq to SQL breaks when converted to OpenAccess. Solutions?

2 posts, 0 answers
  1. Jonathan
    Jonathan avatar
    14 posts
    Member since:
    Feb 2011

    Posted 18 Aug 2012 Link to this post

    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: 

  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 22 Aug 2012 Link to this post

    Hello Jonathan,

    The query you have posted is a nice and complex one and unfortunately we are unable to handle it properly as you have seen.
    We have found a work around for this issue that requires slight modifications to the query:
    01.var ApptList = from a in db.DBAppointments
    02.                where a.RespiteDateIn >= dtmStart.ToUniversalTime() && a.RespiteDateIn < dtmEnd.ToUniversalTime()
    03.                group a by a.AppointmentType.TypeName into g
    04.                select new
    05.                {
    06.                    VisitType = g.Key,
    07.                    TotalHours = (g.Count() == 0 ? 0 : g.Sum(a => a.TotalMinutes)) / 60,
    08.                    TotalAppts = g.Count(),
    09.                    CancelledHours = g.Sum(c => c.CurrentStatusID == 3 ? a => a.TotalMinutes : 0) / 60,
    10.                    CancelledAppts = g.Sum(c => c.CurrentStatusID == 3 ? 1 : 0)
    11.                };

    Important changes are on lines 9 and 10 - we are summing all records, but the ones that do not match our criteria have value of 0

    If you consider the changes that we have made safe you can apply them to your code.

    Kind regards,
    Viktor Zhivkov
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
Back to Top