Order dynamic list of object by common property with pagination

Question

I have a several types of objects, which are I get from database, with different models that have common date property. I want to sort all this models but pull out data only in the end by pages.

Problem is when I add all IQueryable models in dynamic list they pull out all data from database and if there hundreds of records it will take a long time.

What I have already done:

1. Created base interface and in models inherited from him.
2. Added all models in dynamic list.
3. Sort this list by common property.
4. Added pages with X.PagedList

Example code:

Models:

        private interface BaseInfo
        {
            public DateTime Date { get; set; }
        }

        public class CompanyEvent : BaseInfo
        {
            public string Description { get; set; }
            public DateTime Date { get; set; }
        }

        public class SummerEvent : BaseInfo
        {
            public bool IsActive { get; set; }
            public DateTime Date { get; set; }
            public string MainPartner { get; set; }
        }

        public class Birthday : BaseInfo
        {
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public int Age { get; set; }
            public DateTime Date { get; set; }
        }

Code

public IPagedList<dynamic> GetDynamicInfo(int pageNumber)
{
    int pageSize = 10;

    List<dynamic> dynamicList = new List<dynamic>();

    IQueryable<CompanyEvent> companyEvents = Employees.Select(a => new CompanyEvent()
            {
                Date = a.EventDate,
                Description = a.Description
            });
    IQueryable<SummerEvent> summerEvents = Employees.Select(a => new SummerEvent()
            {
                Date = a.StartEventDate,
                IsActive = a.IsActive,
                MainPartner = a.Partner.Name
            });
    IQueryable<Birthday> birthdays = Employees.Select(a => new Birthday()
            {
                Date = a.DateBorn,
                FirstName = a.FirstName,
                a.LastName = a.LastName
            });

    dynamicList.AddRange(companyEvents);
    dynamicList.AddRange(summerEvents);
    dynamicList.AddRange(birthdays);

    var sortedDynamicList = dynamicList.OrderBy(a => ((BaseInfo)a).Date).ToPagedList(pageNumber, pageSize);
    return sortedDynamicList;
}

Answer 1

As you are using ToPageList that is bringing all the data in memory. You can use Skip and Take which will get translated to respective sql and do pagination on DB side like:

var sortedDynamicList = dynamicList
                      .OrderBy(a => ((BaseInfo)a).Date)
                      .Skip(pageNumber)
                      .Take(pageSize)
                      .ToPagedList(pageNumber, pageSize);

Secondly instead of using IPagedList<dynamic> you can use IPagedList<BaseInfo> that will also work same way.

UPDATE:

As in this scenario data is getting loaded from different tables and even different columns in them, one possible way is to load them separately each page of them and then do sort and paging on those like:

IQueryable<CompanyEvent> companyEvents = Employees.Skip(pageNumber).Take(pageSize)
       .Select(a => new CompanyEvent()
        {
            Date = a.EventDate,
            Description = a.Description
        });

IQueryable<SummerEvent> summerEvents = Employees.Skip(pageNumber).Take(pageSize)
        .Select(a => new SummerEvent()
        {
            Date = a.StartEventDate,
            IsActive = a.IsActive,
            MainPartner = a.Partner.Name
        });

IQueryable<Birthday> birthdays = Employees.Skip(pageNumber).Take(pageSize)
        .Select(a => new Birthday()
        {
            Date = a.DateBorn,
            FirstName = a.FirstName,
            a.LastName = a.LastName
        });

and then do the following:

        dynamicList.AddRange(companyEvents);
        dynamicList.AddRange(summerEvents);
        dynamicList.AddRange(birthdays);
        var sortedDynamicList = dynamicList.OrderBy(a => ((BaseInfo)a).Date)
                                           .ToPagedList(pageNumber, pageSize);