Generic Paginate and Order Function for Repository C# and Linq

Hi all,

I am trying to create a generic repository with reusable methods. Once method I want to add is the function to get a select number of itesm from the db based on an order column

I hav the following code which I have found in solutions on stack exchange;

public IQueryable<T> GetPage(int pageIndex = 0, int pageSize = 10, string sortBy = "ID", string sortDirection = "asc")
        {
            var param = Expression.Parameter(typeof(T));

            var sortExpression = Expression.Lambda<Func<T, object>>
                (Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param);

            var results = DataTable.Skip((pageIndex - 1)*pageSize).Take(pageSize);

            switch (sortDirection.ToLower())
            {
                case "asc":
                    return results.OrderBy(sortExpression);
                default:
                    return results.OrderByDescending(sortExpression);
            }
        }

Open in new window


Where DataTable is;

protected Table<T> DataTable;

        public Repository(DBDataContext dataContext)
        {
            DataTable = dataContext.GetTable<T>();
        }
        
        public Repository(DataContext dataContext)
        {
            DataTable = dataContext.GetTable<T>();
        }

Open in new window


DbDataContext is the one built from my DBML file.

now I get the following error when I try to run this using the following code;

var userRepository = new Repository<AdminUser>(dataContext);
var allUsers = userRepository.GetPage(page,rows,sidx,sord).ToList();

Open in new window


error is as follows;

An exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll but was not handled in user code

Additional information: Cannot order by type 'System.Object'.

Open in new window


now i assume this is down to the generic passed in the expression. Any ideas how I rectify this please?
flynnyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it_saigeDeveloperCommented:
You have a couple of problems in your code.  Here is a console example:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace EE_Q28642120
{
	class Program
	{
		static void Main(string[] args)
		{
			List<Employee> employees = new List<Employee>();
			for (int i = 0; i < 50; i++)
				employees.Add(new Employee() { ID = i + 1, Name = string.Format("Employee{0}", i + 1), Hired = DateTime.Now.AddDays(i), IsOnVacation = i % 2 == 0 });

			foreach (var employee in employees.GetPage(2, 10, "Name", SortDirection.Descending))
				Console.WriteLine(employee);
			Console.ReadLine();
		}
	}

	class Employee
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Hired { get; set; }
		public bool IsOnVacation { get; set; }

		public override string ToString()
		{
			return string.Format("{0} [{1}] hired on {2} is {3}.", Name, ID, Hired.ToString("MM/dd/yyyy"), IsOnVacation ? "on vacation" : "not on vacation");
		}
	}

	static class Extensions
	{
		public static IEnumerable<T> GetPage<T>(this IEnumerable<T> source, int pageIndex = 0, int pageSize = 10, string sortBy = "ID", SortDirection sortDirection = SortDirection.Ascending)
		{
			var param = Expression.Parameter(typeof(T), "x");
			Expression conversion = Expression.Convert(Expression.Property(param, sortBy), typeof(object));
			var sortExpression = Expression.Lambda<Func<T, object>>(conversion, param).Compile();
			var results = source.Skip((pageIndex - 1) * pageSize).Take(pageSize);

			switch (sortDirection)
			{
				case SortDirection.Ascending:
					results = results.OrderBy(sortExpression);
					break;
				case SortDirection.Descending:
					results = results.OrderByDescending(sortExpression);
					break;
			}
			return results;
		}
	}

	enum SortDirection
	{
		Ascending = 0,
		Descending = 1
	}
}

Open in new window

Produces the following output -Capture.JPG-saige-
it_saigeDeveloperCommented:
A better output example is provided by sorting on the IsOnVacation property -
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace EE_Q28642120
{
	class Program
	{
		static void Main(string[] args)
		{
			List<Employee> employees = new List<Employee>();
			for (int i = 0; i < 50; i++)
				employees.Add(new Employee() { ID = i + 1, Name = string.Format("Employee{0}", i + 1), Hired = DateTime.Now.AddDays(i), IsOnVacation = i % 2 == 0 });

			foreach (var employee in employees.GetPage(2, 10, "IsOnVacation", SortDirection.Ascending))
				Console.WriteLine(employee);
			Console.ReadLine();
		}
	}

	class Employee
	{
		public int ID { get; set; }
		public string Name { get; set; }
		public DateTime Hired { get; set; }
		public bool IsOnVacation { get; set; }

		public override string ToString()
		{
			return string.Format("{0} [{1}] hired on {2} is {3}.", Name, ID, Hired.ToString("MM/dd/yyyy"), IsOnVacation ? "on vacation" : "not on vacation");
		}
	}

	static class Extensions
	{
		public static IEnumerable<T> GetPage<T>(this IEnumerable<T> source, int pageIndex = 0, int pageSize = 10, string sortBy = "ID", SortDirection sortDirection = SortDirection.Ascending)
		{
			var param = Expression.Parameter(typeof(T), "x");
			Expression conversion = Expression.Convert(Expression.Property(param, sortBy), typeof(object));
			var sortExpression = Expression.Lambda<Func<T, object>>(conversion, param).Compile();
			var results = source.Skip((pageIndex - 1) * pageSize).Take(pageSize);

			switch (sortDirection)
			{
				case SortDirection.Ascending:
					results = results.OrderBy(sortExpression);
					break;
				case SortDirection.Descending:
					results = results.OrderByDescending(sortExpression);
					break;
			}
			return results;
		}
	}

	enum SortDirection
	{
		Ascending = 0,
		Descending = 1
	}
}

Open in new window

Capture.JPG-saige-

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.