Sorting in LINQ to SQL

Question

SQL

There is a task to display ... well, for example, products in the online store section.

LINQ to SQL is used.

I am writing code like this:

List lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.Take(iPageSize).ToList();

The problem is that the goods need to be ordered. The code is converted:

List lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.OrderBy(x=>x.Name).Take(iPageSize).ToList();

But the user can choose to sort by one of several fields, and in any direction.

We get something like this:
The first option

List lstGoods = null ;
if (sSortField == "name" )
{
  if (sSortDirection == "ASC" )
  {
    lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.OrderBy(x=>x .Name).Take(iPageSize).ToList();
  }
  else
  {
    lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.OrderByDescending(x=>x.Name).Take(iPageSize).ToList();
  }
}

And this is for all fields. Horror! So many requests differing only in one parameter. And if there are fifteen fields for sorting and the query occupies ten lines? This is impossible to support.

Second option

List lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.ToList();
if (sSortField == "name" )
{
  if (sSortDirection == "ASC" )
  {
    lstGoods = lstGoods .OrderBy(x=>x.Name).Take(iPageSize);
  }
  else
  {
    lstGoods = lstGoods .OrderByDescending(x=>x.Name).Take(iPageSize);
  }
}

Already good. But as far as I understand the work of LINQ to SQL, the first query (the one before the first if) will be compiled into one SQL query, executed, then a List of Good will be returned to me (and from all those in this section, we cannot select the first iPageSize goods, because their order is not known), then already inside the program this entire List will be sorted and the first iPageSize elements will be taken from it. It turns out wasting computing power for sorting inside the code. This SQL server will do better and faster. And do not send goods that are not included in the current page.

What is the correct way to solve my problem without resorting to DataContext.ExecuteQuery?

Answer 1

Here is a working class that I have already used
http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html
LINQ query is executed directly at its usage stage (ToList(), foreach, etc.), and not at the formation stage.

Answer 2

code:
var allGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods;
List lstGoods = allGoods.OrderBy(x=>x.Name).OrderByDescending(x=>x.Price).Take(iPageSize).ToList();
will execute one query...

Answer 3

as an option, you can use reflection, though you still need to think about optimization. reflection thing is not fast.

List<Person> list = new List<Person>();
list.Add(new Person() { Age = 20, Name="Вася"});
list.Add(new Person() { Age = 21, Name = "Петя" });
list.Add(new Person() { Age = 23, Name = "Коля" });
list.Add(new Person() { Age = 18, Name = "Саша" });

PropertyInfo pi = new Person().GetType().GetProperty("Name");
            
List<Person> sorted = list.OrderBy(p=> pi.GetValue(p, null)).ToList();

Answer 4

            List<Person> list = new List<Person>();
            list.Add(new Person() { Age = 20, Name = "Вася" });
            list.Add(new Person() { Age = 21, Name = "Петя" });
            list.Add(new Person() { Age = 23, Name = "Коля" });
            list.Add(new Person() { Age = 18, Name = "Саша" });

            string propertyName = "Age";

            ParameterExpression parameterExpr = 
                Expression.Parameter(typeof(Person), "person");

            Expression<Func<Person, object>> orderByExpr = 
                Expression.Lambda<Func<Person, object>>(
                    Expression.TypeAs(
                        Expression.Property(
                            parameterExpr, propertyName), typeof(object)), parameterExpr);

            Func<Person, object> orderByFunc = orderByExpr.Compile();

            List<Person> sorted = list.OrderByDescending(p => orderByFunc(p)).ToList();

Try like this. In theory, it should work both in L2O and L2S.