Sorting in LINQ to SQL

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

Using LINQ to SQL.

Write code similar to this:
List lstGoods = DataContext.Sections.Where(x=>x.id = iMySectionId).First().Goods.Take(iPageSize).ToList();


The problem is that the goods should be ordered. Code is converted to:
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 several fields, and in any direction.

We get something like:
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 for all fields. The horror also! So many queries that only differ in one parameter. But if sort fields fifteen and the query takes ten lines? It's impossible to support.

The 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);
}
}


That's good. But as far as I know work in LINQ to SQL the first query (the one before the first if), will be compiled into a single SQL query is made, then I'll be back List of Good (and of all reclining in this section, we cannot choose the first iPageSize products, because I do not know their order), then inside the program the whole List will be sorted and taken from him the first iPageSize elements. It turns out the waste of processing power to sort within the code. This SQL server will do better and faster. And not necessary goods were not included on the current page forward.

How to solve my problem without resorting to the DataContext.ExecuteQuery?
October 10th 19 at 09:39
4 answers
October 10th 19 at 09:41
Here is the working class that I've used
http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html

LINQ the query is executed directly on the phase of use (ToList(), foreach, etc.), and not at the stage of formation.
October 10th 19 at 09:43
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();

fulfill one request...
October 10th 19 at 09:45
alternatively, you can use reflexionem really will have to think about optimization. reflekshen thing is not fast.

List<person> list = new list<person>();
list.Add(new Person() { Age = 20, Name="Bob"});
list.Add(new Person() { Age = 21, Name = "Peter" });
list.Add(new Person() { Age = 23, Name = "nick" });
list.Add(new Person() { Age = 18, Name = "Sasha" });

PropertyInfo pi = new Person().GetType().GetProperty("Name");

List<person> sorted = list.OrderBy(p=> pi.GetValue(p, null)).ToList();
</person></person></person>
Apparently I'm not exactly conveyed the essence of the question. The need not to reduce the query to one line, and to make sorting and clipping the required number of items in the SQL server, not to do it inside my program. - Vince_Donnelly commented on October 10th 19 at 09:48
He will run on SQL server, as you know, local collection antonlustin led for example, instead of the substitute entity. - domenico.Adams77 commented on October 10th 19 at 09:51
This option is available only for LinqToObject, L2S can't translate such queries into SQL code - michel commented on October 10th 19 at 09:54
By the way new Person().GetType() can be replaced by typeof(Person) - Vince_Donnelly commented on October 10th 19 at 09:57
regarding the broadcast of the SQL for7raid rights. This option falls in the runtime errore. Works the solution is given KING. As I understand it is a small "development" of this method - Vince_Donnelly commented on October 10th 19 at 10:00
October 10th 19 at 09:47
 List<person> list = new list<person>();
 list.Add(new Person() { Age = 20, Name = "Bob" });
 list.Add(new Person() { Age = 21, Name = "Peter" });
 list.Add(new Person() { Age = 23, Name = "nick" });
 list.Add(new Person() { Age = 18, Name = "Sasha" });

 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();
</person></person></func<person></func<person></person></person>

Try this. The idea is to work at L2O, and L2S.
alas, with the Object L2S doesn't work. It is necessary to strictly classify the sort function. - Vince_Donnelly commented on October 10th 19 at 09:50

Find more questions by tags SQLLINQCC#