Making the LINQ projection generic

Question

I have a LINQ which access a table with a large data set, so this query is performance critical. I also want the filtered dataset to be sorted dynamically, so I'm using a LINQ extension to do the sorting by passing the sort column at runtime.

Due to a performance issue encountered, I'm doing the projection of data after sorting (the queries are given below). Because of this, I have to repeat the projection inside both OrderBy and OrderByDescending extensions. Is there a way to extract out the projection so that there's less duplicate code?

Here's my code: Filtering data from the source table -

    var allOrderEntries = (from l in context.Orders
            where (branches.Count == 0 || branches.Contains(l.BranchId)) 
                && l.IsActive && !l.IsArchived 
                && (l.PreferredLanguage.LanguageKey == languageKey string.IsNullOrEmpty(languageKey))
                && (string.IsNullOrEmpty(searchText) || (l.FirstName == searchText || l.LastName == searchText || l.Notes == searchText || l.AccountNumber == searchText))
            select l;

This returns an IQueryable, which I then pass to the OrderBy Extension. This is where I have the projection duplication which I want to resolve.

if (sortInfo.SortDirection == Common.Enum.SortDirection.Ascending)
                orderedList = allOrderEntries.OrderBy(sortInfo.SortColumn).Skip(pageInfo.Skip).Take(pageInfo.PageSize).Select(data => new OrderEntryDetailsDto
                {
                    Id = data.OrderId.ToString(),
                    AccountNumber = accountNumber.Mask(data.AccountNumber),
                    FirstName = data.FirstName,
                    LastName = data.LastName,
                    ServicesRequested = data.ServicesRequested, //string.Join(",", data.ServicesRequested.Select(s=> s.ServiceName).ToArray()),
                    PersonRequested = data.UserRequested != null ? data.UserRequested.FirstName + " " + data.UserRequested.LastName : data.GroupRequested != null ? data.GroupRequested.GroupName : string.Empty,
                    Notes = data.Notes ?? string.Empty,
                    WaitTime = string.Format("{0:hh\\:mm\\:ss}", (TimeSpan)(DateTime.UtcNow - data.AddedTime)),
                    Type = data.FkAppointmentId == null ? "Walk-In" : (from a in context.Appointments where a.AppointmentId == data.FkAppointmentId select a).Single().AppointmentStatus.AppointmentStatusName.ToString()           
                }).ToList();
            else
                orderedList = allOrderEntries.OrderByDescending(sortInfo.SortColumn).Skip(pageInfo.Skip).Take(pageInfo.PageSize).Select(data => new OrderEntryDetailsDto
                {
                    Id = data.OrderId.ToString(),
                    AccountNumber = accountNumber.Mask(data.AccountNumber),
                    FirstName = data.FirstName,
                    LastName = data.LastName,
                    ServicesRequested = data.ServicesRequested, //string.Join(",", data.ServicesRequested.Select(s=> s.ServiceName).ToArray()),
                    PersonRequested = data.UserRequested != null ? data.UserRequested.FirstName + " " + data.UserRequested.LastName : data.GroupRequested != null ? data.GroupRequested.GroupName : string.Empty,
                    Notes = data.Notes ?? string.Empty,
                    WaitTime = string.Format("{0:hh\\:mm\\:ss}", (TimeSpan)(DateTime.UtcNow - data.AddedTime)),
                    Type = data.FkAppointmentId == null ? "Walk-In" : (from a in context.Appointments where a.AppointmentId == data.FkAppointmentId select a).Single().AppointmentStatus.AppointmentStatusName.ToString()   
                }).ToList();

So I want to re-do the Select part, which is duplicated and what's the best way to do so?

Answer 1

try this since it is IQueryable

if (sortInfo.SortDirection == Common.Enum.SortDirection.Ascending)
{     
    allOrderEntries = allOrderEntries.OrderBy(sortInfo.SortColumn)
                     .Skip(pageInfo.Skip).Take(pageInfo.PageSize).AsQueryable();
}
else
{
    allOrderEntries = allOrderEntries.OrderByDescending(sortInfo.SortColumn)
                     .Skip(pageInfo.Skip).Take(pageInfo.PageSize).AsQueryable();
}


var orderedList = allOrderEntries.Select(data => new OrderEntryDetailsDto
            {
                Id = data.OrderId.ToString(),
                AccountNumber = accountNumber.Mask(data.AccountNumber),
                FirstName = data.FirstName,
                LastName = data.LastName,
                ServicesRequested = data.ServicesRequested, //string.Join(",", data.ServicesRequested.Select(s=> s.ServiceName).ToArray()),
                PersonRequested = data.UserRequested != null ? data.UserRequested.FirstName + " " + data.UserRequested.LastName : data.GroupRequested != null ? data.GroupRequested.GroupName : string.Empty,
                Notes = data.Notes ?? string.Empty,
                WaitTime = string.Format("{0:hh\\:mm\\:ss}", (TimeSpan)(DateTime.UtcNow - data.AddedTime)),
                Type = data.FkAppointmentId == null ? "Walk-In" : (from a in context.Appointments where a.AppointmentId == data.FkAppointmentId select a).Single().AppointmentStatus.AppointmentStatusName.ToString()   
            }).ToList();