CODEWITHSUNDEEP
linq
Paul Hutchinson
Paul Hutchinson

Reputation: 829

Percentage of a row in ordered query

Curious problem, not sure how to do this in linq. Part of the problem might be that I'm trying to do this all at once.

I have a phase table

[id, name, [order]]
{{1, 'phase 2', 2}, {2, 'phase 1', 1}

I'd like to get a percentage of progress through all the phases for a given phaseid.

I know of a way to do this with sql.

SELECT (SELECT a.row
        FROM   [phase] p 
               INNER JOIN (SELECT Row_number() 
                                    OVER( 
                                      ORDER BY [order]) row,
                                  id 
                           FROM   [phase]) p2 
                       ON p2.id = p.id 
        WHERE  p.id = @phaseid) / CONVERT(FLOAT, Count(*)) 
FROM   [phase]

Is there a way to do this in linq? All my attempts seem to fail

Upvotes: 0

Views: 80

Answers (2)

Paul Hutchinson
Paul Hutchinson

Reputation: 829

First thing I got working for anybody tackling this problem in the future. vb.net

Dim query = (From p2 In db.Phases Order By p2.Order).AsEnumerable().Select(Function(p2, index) New test With {.id = p2.ID, .row = index + 1})
Dim Percentage = query.Where(Function(x) x.id = 3).Select(Function(x) x.row).First / db.Phases.Count

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174329

Try the following:

var query = phases.OrderBy(x => x.Order).Select(x => x.Id).ToList();
var result = query.Select((x, i) => new { x.Id, Index = i + 1 })
                  .Single(x => x.Id == phaseId).Index / (double)query.Count;

It first queries the IDs of all Phases from the database, already correctly ordered. In memory, it then generates the row number and filters out the phase with the requested ID.
Based on the index and the total count of phases, the percentage is then calculated.

Upvotes: 1

Related Questions