Congratulations to all the winners of April 2013, they have won INR 3400 cash and INR 20147 worth prizes !

 Online : 17383 |  Welcome, Guest!   Register  Login
Home > Articles > LINQ > Row_Number simulation in LINQ

# Row_Number simulation in LINQ

 1 vote(s)Rating: 4 out of 5
Article posted by Niladri.Biswas on 10/3/2012 | Views: 3263 | Category: LINQ | Level: Beginner | Points: 250

In this article , we will look into as how we can implement Row_Number() function of Sql Server in LINQ using C# as the language of choice.

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

}
}

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

}
}

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.

## 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

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.

 Experience: 6 year(s) Home page: http://www.dotnetfunda.com Member since: Monday, October 25, 2010 Level: Diamond Status: [Member] Biography: Lead Engineer at HCL Technologies Ltd., having 6 years of experience in IT field. I love to explore new technologies and love challenges and try to help others as much as possible not only by coding but also by all possible means.
>> Write Response - Respond to this post and get points
Related Posts

In this article, we shall learn how to use transactions in ADO.NET Entity framework in simplified manner.

Entity Data Model (EDM) is bridge between application and database. LINQ(Language Integated Query) to entity which performs CRUD operations against the Entity Data Model(EDM)

In this article, I shall show how to use frequently used LINQ extension methods. In order to work with LINQ we need to use from System.Linq namespace.

This is the article which will explain how to use linq

In this article, we will find the N-th highest salary of employee using LINQ/Lambda.

More ...