Filtering data for jQuery DataTables

Question

Using DataTables (Table plug-in for jQuery) with server-side processing, I had to create filtering for my data with good performance. However, in my opinion it's kinda ugly, especially the part of (.Where(...)), where I have to manually compare each property with the search variable. Is it possible to make it better?

using System.Linq.Dynamic; // because of special .OrderBy

public class SomeRepository
{
    public DataTableDTO GetAllFromBase(int start, int length, string sortColumn, string sortColumnDir, string search)
    {
        var dataFiltered = db.User
            .AsNoTracking()
            .Select(x => new { x.Id, x.FirstName, x.LastName, x.Description})
            .OrderBy(sortColumn + " " + sortColumnDir)
            .Where(search.Length > 0, x => x.Id.ToString().Contains(search.ToLower())
                                        || x.FirstName.ToLower().Contains(search.ToLower())
                                        || x.LastName.ToLower().Contains(search.ToLower())
                                        || x.Description.ToLower().Contains(search.ToLower()));

        var recordsFiltered = dataFiltered.Count();
        var recordsTotal = db.User.Count();

        var dataToShow = dataFiltered
            .Skip(start)
            .Take(length)
            .ToList();

        var dataForTable = new DataTableDTO
        {
            Data = dataToShow,
            RecordsTotal = recordsTotal,
            RecordsFiltered = recordsFiltered
        };

        return dataForTable;
    }
}

public static class LinqExtensions
{
    public static IQueryable<T> Where<T>(this IQueryable<T> query, bool condition, Expression<Func<T, bool>> whereClause)
    {
        if (condition)
        {
            return query.Where(whereClause);
        }
        return query;
    }
}

Answer 1

I would suggest to add some guard evaluation.

if (string.IsNullOrEmpty(search))
    return new DataTableDTO();

You can also convert string 'search' to lower case ones at the beginning of method.

search = search.ToLower();

Then you can ommit the first condition in Where statement:

.Where(search.Length > 0,

Answer 2

You can simplify the query by extending the model with an interface.

interface ISearchable
{
    IEnumerable<string> Properties { get; }
}

Use it to return searchable properties:

partial class User : ISearchable
{
    public IEnumerable<string> Properties
    {
        get
        {
            yield return Id.ToString();
            yield return FirstName.ToString();
            yield return LastName.ToString();
            yield return Description.ToString();
        }
    }
}

Then update your query to use it recpecitvely:

var dataFiltered = db.User
    .AsNoTracking()
    .Cast<ISearchable>()
    .OrderBy(sortColumn + " " + sortColumnDir)
    .Where(
        search.Length > 0, 
        x => x.Properties.Any(p => p.IndexOf(search, StringComparison.OrdinalIgnoreCase)));

Probably for IndexOf to work, you'll have to call AsEnumerable after AsNoTracking because EF won't be able to translate it to SQL.