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

Possible duplicate of [Row\_number over (Partition by xxx) in Linq?](http://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq) — captainsac, Sep 15 '16 at 06:25
little bit complicate, cuz you have 3 fields wanna have row_number. You may try suggestion in this post first: http://stackoverflow.com/questions/365086/how-to-project-a-line-number-into-linq-query-results/365127#365127 — Prisoner, Sep 15 '16 at 06:26

score 1 · Answer 1 · answered Sep 15 '16 at 07:17

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

Thanks for your answer, it looks very good but it also doesn't work. I got this Error: You can't create a constant value of type 'Anonymous type'. In this context, only primitive types and enumerated types are supported. Thanks. — HGR User, Sep 15 '16 at 14:12
@HGRUser I wasn't sure if it'd work anyway but which line does the error come from? — artm, Sep 15 '16 at 23:48
@HGRUser Also it sounds like if you create a class for `{i.QuoteItem_InquiryItem, Rank = index + 1}` and replace `var qi1 =` with `IEnumerable qi1 = ` and `new myClass() {Item = i.QuoteItem_InquiryItem, Rank = index + 1}` that might get rid of the error. — artm, Sep 16 '16 at 04:46
The error i get from the line `_ => _.IdConstructionStageId == x.Id`. With the class i get the same Exception --> only primitive types and enumerated types are supported. — HGR User, Sep 16 '16 at 05:24
@HGRUser `_ => _.IdConstructionStageId == x.Id` is not correct, there is no x defined in there, I think that needs to be `_ => _.IdConstructionStageId == g.Max(x => x.Id)`. Just trying to get the ID of the item — artm, Sep 16 '16 at 05:29
I used it like you described :) The problem is that `myClass()` is not a primitive type. — HGR User, Sep 16 '16 at 05:42

SQL ROW_NUMBER() in LINQ Query Syntax

1 Answers1