This is a migrated thread and some comments may be shown as answers.
Complex Grouping Query from Linq to SQL breaks when converted to OpenAccess. Solutions?
1 Answer 28 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jonathan
Top achievements
Rank 1
Jonathan asked on 18 Aug 2012, 09:42 PM
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: 

1 Answer, 1 is accepted

Sort by
0
Viktor Zhivkov
Telerik team
answered on 22 Aug 2012, 04:34 PM
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!
Tags
LINQ (LINQ specific questions)
Asked by
Jonathan
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Share this question
or