SQL ROW_NUMBER() in LINQ Query Syntax

Question

I have this query which I wish to replace with Linq as query syntax:

select 
    ii.Id, ii.Name as Supplier,
    qi.Price1, qi.Price2, qi.Price3,
    case when qi.price1 is null then NULL else ROW_NUMBER() OVER(ORDER BY isNull(qi.price1,1000000) ASC) end AS Price1Order,    
    case when qi.price2 is null then NULL else ROW_NUMBER() OVER(ORDER BY isNull(qi.price2,1000000) ASC) end AS Price2Order,    
    case when qi.price3 is null then NULL else ROW_NUMBER() OVER(ORDER BY isNull(qi.price3,1000000) ASC) end AS Price3Order
From dbo.InquiryItems ii
left join dbo.quoteItems qi on ii.Id = qi.QuoteItem_InquiryItem

SQL-Query Result:

Id      Supplier        Price1  Price2  Price3  Price1Order Price2Order Price3Order
1655    Supplier 2      80.00   NULL    40.00   3           NULL        1
1656    Supplier 4      65.00   30.00   42.00   2           1           2
1662    Supplier 1      15.00   35.00   43.00   1           2           3
1670    Supplier 3      250.00  NULL    NULL    4           NULL        NULL

In C# I need this query as a IQueryable Object. I must filter the query for different parts (one or more) and then group it by Supplier (IdAccount) and SUM the prices. This prices I must rank.

return colQuoteItem = from vQuote in this.vQuoteItemOverviews
    where vQuote.IdConstructionStageId == ConstructionStageId                               
    group vQuote by new
    {
        vQuote.IdAccount
    } into g                                                              
    select new vQuoteItemOverviewSum
    {
        Id = g.Max(x => x.Id),                                   
        Price1Order = null,  //Here I need the ROW_NUMBER like in the SQL-Syntax
        Price2Order = null,  //Here I need the ROW_NUMBER like in the SQL-Syntax
        Price3Order = null,  //Here I need the ROW_NUMBER like in the SQL-Syntax
        price1 = g.Sum(x => x.price1),
        price2 = g.Sum(x => x.price2),
        price3 = g.Sum(x => x.price3),                                   
    }
    ;

Answer 1

Would this work?

var qi1 = (from qi in quoteItems orderby qi.price1 select new {qi.QuoteItem_InquiryItem}).AsEnumerable().Select((i, index) => new {i.QuoteItem_InquiryItem, Rank = index + 1});

var qi2 = (from qi in quoteItems orderby qi.price2 select new {qi.QuoteItem_InquiryItem}).AsEnumerable().Select((i, index) => new {i.QuoteItem_InquiryItem, Rank = index + 1});

var qi3 = (from qi in quoteItems orderby qi.price3 select new {qi.QuoteItem_InquiryItem}).AsEnumerable().Select((i, index) => new {i.QuoteItem_InquiryItem, Rank = index + 1});


return colQuoteItem = from vQuote in this.vQuoteItemOverviews.AsEnumerable()
    where vQuote.IdConstructionStageId == ConstructionStageId                               
    group vQuote by new
    {
        vQuote.IdAccount
    } into g                                                              
    select new vQuoteItemOverviewSum
    {
        Id = g.Max(x => x.Id),                                   
        Price1Order = qi1.FirstOrDefault(_ => _.IdConstructionStageId == x.Id)?.Rank,  //Here i need the ROW_NUMBER like in the SQL-Syntax
        Price2Order = qi2.FirstOrDefault(_ => _.IdConstructionStageId == x.Id)?.Rank,  //Here i need the ROW_NUMBER like in the SQL-Syntax
        Price3Order = qi3.FirstOrDefault(_ => _.IdConstructionStageId == x.Id)?.Rank,  //Here i need the ROW_NUMBER like in the SQL-Syntax
        price1 = g.Sum(x => x.price1),
        price2 = g.Sum(x => x.price2),
        price3 = g.Sum(x => x.price3),                                   
    }
    ;