ROW_NUMBER function equivalent

Question

I have a large data set returned in code that I am prepping for a client-side SSRS report.  I use the data set for a few different things, but now I need to be able to add a ranking by category.  I can do it easily in SQL using the ROW_NUMBER function, but I cannot figure out how to do the same thing in LINQ.  Here is the basic query in SQL:

SELECT ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Category, SUM(Sales) DESC) AS Rank,
    Category,
    Salesperson,
    SUM(Sales) AS Total Sales
FROM MyTable
GROUP BY Category, Salesperson

I can easily do everything in LINQ but that function, and I don't want to have to pull the data all over again from SQL as it takes a long time as it is.  Does anyone have any suggestions?

Answer 1

Hello,

 

You can first query the necessary data via LINQ and then call .AsEnumerable() on the IQueryable LINQ query result, so we may perform a LINQ to Objects query later to generate the row number column:

=========================================================================================================

var query = (from ….

                    …

                    select new { Rank = …, Category = …, Salesperson = …, TotalSales = … }).AsEnumerable().

                    Select((r, index) => new { Rank = r.Rank, Category = r.Category, Salesperson = r.Salesperson, TotalSales = r.TotalSales, RowNumber = index + 1 });

=========================================================================================================

 

Is this work for you?  

 

If you have any questions, please feel free to let me know.

 

Have a great day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi,

 

I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Hi,

Please help me to find a solution for the following situation. The table contains following data.

 

ID Username ModifiedDate

1 A.BEENA 2009-12-07 04:48:17.980
2 A.BEENA 2009-11-17 06:02:27.443
3 Abhilash 2009-12-07 04:48:17.980
4 abhilash.r 2009-12-07 04:48:17.980
5 AJI 2009-11-17 06:02:27.443
6 haris 2009-12-07 04:48:17.980
7 haris 2009-11-17 06:02:27.443

 

 

I want to select details of all distinct users order by ModifiedDate.

I need output like this

A.BEENA 2009-12-07 04:48:17.980
Abhilash 2009-12-07 04:48:17.980
abhilash.r 2009-12-07 04:48:17.980
AJI 2009-11-17 06:02:27.443
haris 2009-12-07 04:48:17.980

 

 

I can get the output using following query from the database

 

WITH CTE (DuplicateCount,Username,ModifiedDate)
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY Username ORDER BY ModifiedDate DESC)AS DuplicateCount,
Username, ModifiedDate FROM )
SELECT * FROM CTE WHERE DuplicateCount = 1

 

How can I do this in LINQ. Please help me.

 

Answer 4

This would create an incrementing index ovr the whole population, but is there a way to reproduce the partition by and order by clauses?