Row_Number simulation in LINQ

Introduction

Sql Server 2005 has introduced the Row_Number () function. It returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Syntax

ROW_NUMBER () OVER ([partition_by_clause] order_by_clause)

Let us discuss this with a small example. Let's say that we have some fruit names in a table and want to generate sequential numbers to the fruit names while projecting the record.

In Sql Server, the query will be

Declare @FruitTable Table(FruitNames Varchar(20))
Insert Into @FruitTable Values('Apple'),('Banana'),('Guava'),('Mango'),('Watermelon')

-- Project records with sequential numbers
Select 
	SequentialNumber = Row_Number() Over(Order By FruitNames)
	,FruitNames
From @FruitTable

/* Result

SequentialNumber	FruitNames
1	Apple
2	Banana
3	Guava
4	Mango
5	Watermelon

*/

Well, it is a simple query that will generate the rows on the fly.And for doing so, we have the in built Row_Number() function. But in LINQ, there is no such function available. Then how can we do so in LINQ?

Well this article will address that. Also, we have seen that there is a Partition By clause being associated with the Row_Number() function. The purpose of that is to generate sequential number of a row within a partition of a result set.Let us see the same with an example too.We will basically extend our previous example as under.

Declare @FruitTable Table(FruitNames Varchar(20))
Insert Into @FruitTable Values('Apple'),('Banana'),('Guava'),('Mango'),('Watermelon'),('Mango'),('Guava'),('Guava')

-- Project records with sequential numbers
Select 
	SequentialNumber = Row_Number() Over(Partition By FruitNames Order By FruitNames)
	,FruitNames
From @FruitTable

/* Result

SequentialNumber	FruitNames
1	Apple
1	Banana
1	Guava
2	Guava
3	Guava
1	Mango
2	Mango
1	Watermelon

*/

As can be figure out that for "Guava" the SequentialNumber is 1,2,and 3 , for "Mango" it is 1 and 2 while it is 1 for the rest. The reason is that we have Grouped (or Partitioned) the result sets by "FruitNames" and henceforth is the count. That is , within the Group, the sequential numbers have been generated. How can we do the same using Linq? This article will address that too.

Simple Row_Number() simulation

Let us first write the program

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // create the collection
            List<FruitTable> lstFruitTable = new List<FruitTable>();
            lstFruitTable.Add(new FruitTable { FruitNames = "Apple" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Banana" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Guava" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Mango" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Watermelon" });

            // add the sequence number on the fly
            var simpleRowNumber = lstFruitTable
                    .Select((x, index) => new
                    {
                        SequentialNumber = index + 1
                        ,FruitNames = x.FruitNames                        
                    }).ToList();

            //display the record
            foreach (var item in simpleRowNumber)
            {
                Console.WriteLine("SequentialNumber = {0}   FruitNames = {1}", item.SequentialNumber, item.FruitNames);
            }

            Console.ReadKey();
        }
    }

    class FruitTable
    {
        public string FruitNames { get; set; }
    }
}

The output is

The overloaded method of Select<TSource, TResult> projects each element of a sequence into a new form by incorporating the element's index.And this did the trick.

Row_Number() with Partition By clause simulation

Let us first write the program

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // create the collection
            List<FruitTable> lstFruitTable = new List<FruitTable>();
            lstFruitTable.Add(new FruitTable { FruitNames = "Apple" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Banana" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Guava" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Mango" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Watermelon" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Mango" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Guava" });
            lstFruitTable.Add(new FruitTable { FruitNames = "Guava" });            

            var rowNumberWithPartitionBy = lstFruitTable
                        .OrderBy(o => o.FruitNames)
                        .GroupBy(g => g.FruitNames)
                        .Select(s => new { s, Count = s.Count() })
                        .SelectMany(sm => sm.s.Select(s => s)
                                          .Zip(Enumerable.Range(1, sm.Count), (fruitTable, index) 
                                              => new { SequentialNumber = index, fruitTable.FruitNames })
                                    ).ToList();
            

            //display the record
            foreach (var item in rowNumberWithPartitionBy)
            {
                Console.WriteLine("SequentialNumber = {0}   FruitNames = {1}", item.SequentialNumber, item.FruitNames);
            }

            Console.ReadKey();
        }
    }

    class FruitTable
    {
        public string FruitNames { get; set; }
    }
}

The output is

Lot of things here.Right? Ok , we will decipher one by one. Consider the below statement

lstFruitTable.OrderBy(o => o.FruitNames)

In this line, we are simply performing a sorting on the collection based on the "FruitNames". The output of this is being passed to

GroupBy(g => g.FruitNames)

Here we are grouping the results on the "FruitNames" property

Select(s => new { s, Count = s.Count() })

The above line explains that the result obtained from the "GroupBy" method is passed to the "Select" method where we are doing a "Count" on the number of items

The "SelectMany", projects each element of a sequence to an IEnumerable<T> and flattens the resulting sequences into one sequence.

SelectMany(sm => sm.s.Select(s => s)

If we print at this point

lstFruitTable
.OrderBy(o => o.FruitNames).GroupBy(g => g.FruitNames)
.Select(s => new { s, Count = s.Count() })
.SelectMany(sm => sm.s.Select(s => s))
.ToList()
.ForEach(i => Console.WriteLine(i.FruitNames));

we will get the below output

So, we can find that result has been flattened at this point of time and we just need to create the sequential numbers within the partitions

For doing so , we are using the "Zip" extension method that merges two sequences by using the specified predicate function.

.Zip(
		Enumerable.Range(1, sm.Count), (fruitTable, index) 
        => new { SequentialNumber = index, fruitTable.FruitNames }
	)

The Enumerable.Range(1, sm.Count) will generate the numbers between 1 and the count of the items for the "FruitNames" group . This is the first sequence.

The second sequence is the (fruitTable, index). This means that we are getting the index in the "fruitTable" collection. Finally, by using the "Zip" method, we are merging the sequence.

Reference

1. Enumerable.Zip
2. Enumerable.SelectMany

Conclusion

Row_Number() is a very handy function that comes into use in many places.In this article we have seen about it's implementation is Linq using C# language.Hope this will be helpful. Thanks for reading.

N.B.~ Zipped file is attached