在LINQ查询语法中使用SQL ROW_NUMBER()

Question

在LINQ查询语法中使用SQL ROW_NUMBER()

5

我有这个查询，希望用Linq替换为查询语法：

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-查询结果：

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

在C#中，我需要将此查询作为IQueryable对象。我必须根据不同的零件（一个或多个）过滤查询，然后按供应商（IdAccount）分组并对价格求和。这些价格必须进行排名。

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

- HGR User

1

可能是Row_number over (Partition by xxx) in Linq?的重复问题。 - captainsac

1

有点复杂，因为你有3个字段想要行号。你可以先尝试这篇帖子中的建议：https://dev59.com/23RC5IYBdhLWcg3wP-dh#365127 - Prisoner

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- artm · Accepted Answer

这能行吗？

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