How to implement paging with LINQ

Before going into how paging is implemented with LINQ, Let's discuss the need for implementing paging.

With large amounts of data, it is not a good practice to pull all records from database when you are showing a fraction of them in one page. It is always recommended to use data on demand approach. When you want to show first 20 records out of the search results then you must get the first 20 records from database and discard the rest. Similarly when you want to show next 20 records of the search results then you need to get the next 20 records from database and discard the rest. This is nothing but called paging.

LINQ has made the paging solution very simple as shown below example.

public List<Client> 
GetAllClients(bool? isActive, int pageNumber, int pageSize, out int totalPages)
{
//Actual query which returns large data
var query = dataContext.Clients.Where(p => isActive == null || p.IsActive == isActive);

//Calculating total number of pages by taking ceiling number of the fractional value
totalPages = (int)Math.Ceiling((decimal)query.Count() / (decimal)pageSize);

//Paging logic goes here
return query.Skip((pageNumber - 1)*pageSize).Take(pageSize).ToList();
}

The parameters which play major role in paging are page number and page size. The page number is to identify the page of which the records to be returned. And the page size to identify the number of records to be returned. And there is another out parameter totalPages which is used to hold the total number of pages available within the data returned. This is needed to show the number of pages to the user and also useful in the logic which enables/disables page navigation.

Find common elements in c# generic list

The following code demonstrates how to get the common elements in
given 2 lists using Intersect method of System.Collections.Generic.List.

using System;
using System.Linq;

namespace ConsoleApplication1
{
static class Program
{
    static void Main(string[] args)
    {
        int[] elementSet1 = { 5, 1, 6, 3, 8 };
        int[] elementSet2 = { 3, 7, 8, 6, 5 };

        foreach (int element in elementSet1.Intersect(
                           elementSet2))
        {
            Console.WriteLine(element);
        }
        Console.Read();
    }
}
}

Return multiple result sets using IMultipleResults in linq c#

As we know that LINQ introduced a new feature which lets us to get multiple result sets from the stored procedure. But there is one important which all should know about IMultipleResults. The order of the GetResult's should be same as the order of the select statements in the sproc. This must be followed in order to avoid unexpected errors. I spent many hours on this to get rid of this problem.

For better understanding please go through the following

The following tsql code shows how the spoc which returns multiple result sets look like

CREATE PROCEDURE GetMultipleResults
@SomeID int
AS
BEGIN
SELECT * FROM SomeTable1 where SomeColumn1=@SomeID
SELECT * FROM SomeTable2 where SomeColumn2=@SomeID
SELECT * FROM SomeTable3 where SomeColumn3=@SomeID
SELECT * FROM SomeTable4 where SomeColumn4=@SomeID
END

The below code shows how to get the IMultipleResult object by calling the above sproc using LINQ

[Function(Name = "dbo.SPROCName")]
[ResultType(typeof(ResultSet1))]
[ResultType(typeof(ResultSet2))]
[ResultType(typeof(ResultSet3))]
[ResultType(typeof(ResultSet4))]
public IMultipleResults SomeMethod([Parameter(DbType = "INT")] int? SomeID
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), SomeID);
return ((IMultipleResults)(result.ReturnValue));
}


The below code shows how to read the result set from the object of IMultipleResult type

public void SomeOtherMethod(int SomeID)
{
DataContext1 context = new DataContext1 (dbConnString);
IMultipleResults results = context.SomeMethod(SomeID);

ResultSet1 resultSet1= results.GetResult().FirstOrDefault();
IEnumerable resultSet2 = results.GetResult();
IEnumerable resultSet3 = results.GetResult();
ResultSet4 resultSet4= results.GetResult().FirstOrDefault();
}

If you observe the above code you can notice that the method is reading individual result sets from the IMultipleResult object in the same sequence as the sproc returns the results. This is very important that the order of the IMultipleResults.GetResult() statements for the result sets should be same as the order of the select statements in sproc. If the order is different then you will get runtime errors or invalid objects and many more issues.