SQL ROW_NUMBER () in LINQ query syntax

Question

I have this query that I want to replace Linq as the 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 

The result of the SQL query:

 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 an IQueryable object. . I have to filter the request for different parts (one or several), and then group it by the Supplier (IdAccount) and SUM prices. I must evaluate these prices.

 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), } ; 

Thanks.

Answer 1

Will 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), } ;