Hi Jeremy,
We have encountered another problem. This time a NotSupportedException is being thrown when we try to run the following query. From the full exception (at the end of this message), it looks like there's a problem with our join criteria. Are you able to provide any advice?
Also, we seem to be encountering more problems than I would have expected, if we're using LightSpeed in an unusual way please be sure to tell me so that we can adjust etc.
Here's the C# that's causing the problem:
var targets = from t in uow.Data_Desktop_Targets
join co in uow.Config_Organizations on t.Config_OrganizationId equals co.Id
where t.Config_Desktop_Target_FYId == 28
select new
{
OrgId = co.Id,
OrgName = co.Abbreviation,
Target = t.Target,
Month = t.TargetMonth
};
var replacements = from r1 in uow.Data_Desktop_Replacements
group r1 by new { MonthReplaced = r1.MonthReplaced, OrgId = r1.Config_OrganizationId } into r2
select new
{
OrgId = (int?)r2.Key.OrgId,
MonthReplaced = r2.Key.MonthReplaced,
TotalReplacements = (int?)r2.Count()
};
orgProgress = (from t in targets
join r1 in replacements
on new { o = (int?)t.OrgId, m = t.Month } equals new { o = r1.OrgId, m = r1.MonthReplaced }
into r2
from r in r2.DefaultIfEmpty()
group new { r, t } by new { t.OrgId, t.OrgName } into a
select new
{
OrgId = a.Key.OrgId,
OrgName = a.Key.OrgName,
Target = a.Sum(x => x.t.Target),
Replacements = a.Sum(x => x.r.TotalReplacements) ?? 0
}).ToArray().AsEnumerable();
LINQPad translates this into the following SQL:
-- Region Parameters
DECLARE @p0 Int = 28
DECLARE @p1 Int = 0
-- EndRegion
SELECT [t7].[Id] AS [OrgId], [t7].[Abbreviation] AS [OrgName], [t7].[value] AS [Target], COALESCE([t7].[value2],@p1) AS [Replacements]
FROM (
SELECT SUM([t0].[Target]) AS [value], SUM([t6].[value]) AS [value2], [t1].[Id], [t1].[Abbreviation]
FROM [Data_Desktop_Target] AS [t0]
INNER JOIN [Config_Organization] AS [t1] ON [t0].[Config_OrganizationId] = [t1].[Id]
CROSS APPLY ((
SELECT NULL AS [EMPTY]
) AS [t2]
OUTER APPLY (
SELECT [t5].[value]
FROM (
SELECT [t4].[Config_OrganizationId], [t4].[MonthReplaced], [t4].[value] AS [value]
FROM (
SELECT COUNT(*) AS [value], [t3].[MonthReplaced], [t3].[Config_OrganizationId]
FROM [Data_Desktop_Replacement] AS [t3]
GROUP BY [t3].[MonthReplaced], [t3].[Config_OrganizationId]
) AS [t4]
) AS [t5]
WHERE (([t1].[Id]) = [t5].[Config_OrganizationId]) AND ([t0].[TargetMonth] = [t5].[MonthReplaced])
) AS [t6])
WHERE [t0].[Config_Desktop_Target_FYId] = @p0
GROUP BY [t1].[Id], [t1].[Abbreviation]
) AS [t7]
(and it executes fine against our DB).
The full exception thrown when running the C# snippet is:
System.NotSupportedException: Specified method is not supported.
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractMemberCriteria(LinqQueryPlanExpression plan, MemberExpression member)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.JoinBase.ExtractJoinCriteria(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.JoinBase.ExtractJoinConditions(LinqQueryPlanExpression plan, Expression lhsCriteriaExpression, Expression rhsCriteriaExpression, Selectable rhsSelectable)
at Mindscape.LightSpeed.Linq.Sqo.GroupJoin.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Sqo.SelectMany.EvaluateDefaultIfEmpty(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.SelectMany.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Sqo.GroupBy.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at Agent.CmdLineClient.Program.LinqTest() in d:\MTL TFS\PortalOne\Development\Agent.CmdLineClient\Program.cs:line 103
at Agent.CmdLineClient.Program.Main(String[] args) in d:\MTL TFS\PortalOne\Development\Agent.CmdLineClient\Program.cs:line 123
Any advice or suggestions you may have would be greatly appreciated.
Cheers,
Alex
(edited to improve subject)
