PetaPoco Orderby dynamically created column

Question

is it possible to order by dynamically created column like this (i want sort by AreaPriority, but with this query i get error invalid column name: AreaPriority, when i run this query in SQL Server Managment Studio everything works fine but in petapoco it runs error). Here is this select query:

SELECT strGet.StrengthId as AreaId, strGet.[Description] as AreaName,
(SELECT SUM(ias.Score) FROM ImprovementAreaScore ias WHERE ias.StrengthId=strGet.StrengthId) as AreaPriority
FROM
{0}.Strength strGet
WHERE
strGet.[Description] like '%' + @0 and strGet.SelfAssessmentId=@1 and strGet.IsStrength=0 and strGet.ToImprovementProject=1 ORDER BY AreaPriority", ...

score 0 · Answer 1 · answered Apr 07 '14 at 14:24

0

I am pretty sure you cannot put a sub-select in the column list.

You should do a JOIN....

SELECT 
    strGet.StrengthId as AreaId, 
    strGet.[Description] as AreaName, 
    SUM(ias.Score) as AreaPriority

FROM {0}.Strength strGet

JOIN ImprovementAreaScore as ias ON ias.StrengthId=strGet.StrengthId

WHERE
    strGet.[Description] like '%' + @0 
and strGet.SelfAssessmentId=@1 
and strGet.IsStrength=0 
and strGet.ToImprovementProject=1 

ORDER BY AreaPriority", ...

answered Apr 07 '14 at 14:24

Michael Coxon

5,311
1
24
51

Thank you, its working in sql but in PetaPoco there is still the same error about wrong column. I have done small workaround but not so nice (added query for summing elements to order by) and its working in PetaPoco :) – Dawid PR Apr 07 '14 at 14:48

score 0 · Accepted Answer · answered Apr 07 '14 at 16:53

It's strange to get different results because PetaPoco it's only running the query through ADO.net, but you can solve the problem ordering by the expression (not the column name) as was done in older SQL versions:

SELECT strGet.StrengthId as AreaId, strGet.[Description] as AreaName,
(SELECT SUM(ias.Score) FROM ImprovementAreaScore ias WHERE ias.StrengthId=strGet.StrengthId) as AreaPriority
FROM
{0}.Strength strGet
WHERE
strGet.[Description] like '%' + @0 and strGet.SelfAssessmentId=@1 and strGet.IsStrength=0 and strGet.ToImprovementProject=1 
ORDER BY (SELECT SUM(ias.Score) FROM ImprovementAreaScore ias WHERE ias.StrengthId=strGet.StrengthId)",

PetaPoco Orderby dynamically created column

2 Answers2