0

I have to do multi-part sorts and want to do it dynamically.

I found this question but do not know how to use func in a dbquery statement.

No generic method 'ThenBy' on type 'System.Linq.Queryable'

If I could get the code in the thread to work it would be nirvana.

All the examples I have seen use then within a where statement, but I need to use the function to do sorting.

I have written extensions using IQueryable, including ones for orderby and orderbydescending. The problem is thenby and thenbydescending use iorderedqueryable.

The error I get when using ThenByProperty is

Object of type 'System.Data.Entity.Infrastructure.DbQuery1[ORMModel.v_Brand]' cannot be converted to type 'System.Linq.IOrderedEnumerable1[ORMModel.v_Brand]'.

Do not get such an error when I use a comparable OrderByProperty extension.

what a mess, obviously I do not post often here. Anyway I am stumped and clueless so any tips are very appreciated.

Tried to post code but kept getting format errors so gave up. But help me anyways :)

Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
FairSite2C
  • 81
  • 3
  • 7
  • I think it's the correct behavior, you can't use `ThenBy` without `OrderBy` => `dataset.OrderBy().ThenBy()` – Kien Chu Jan 31 '19 at 00:56
  • Instead of doing the multi sort on the server side, I would take the result from the server and then do the sort locally: `dbContext.Items.Where(item => item.ID < 20).AsEnumerable().OrderBy(item => item.LastName).ThenBy(item => item.FirstName)`. The biggest performance hit with a database is to filter out only the currently relevant data. If this has been done on the server side, sorting is quite easy on the client side. – Oliver Jan 31 '19 at 09:54
  • Kien Chu you are correct but I do not know how to include the func in a dbquery statement. For example the methods I wrote that return IQueryable can be used like a native linq extension. In this case I could code var output = _db.MyTable.MultiSort("field1,field2:desc").ToArray(); – FairSite2C Jan 31 '19 at 10:03
  • Experimenting I tried this, q = q.OrderBy(x => MultipleSorts(sortBy)); and got this error, LINQ to Entities does not recognize the method 'System.Func`2[System.Linq.IQueryable`1[ORMModel.v_Brand],System.Linq.IOrderedQueryable`1[ORMModel.v_Brand]] MultipleSorts[v_Brand](System.String)' method, and this method cannot be translated into a store expression. – FairSite2C Jan 31 '19 at 11:31
  • Just in case anybody else comes along looking for multiple level sorts I found this code easier to use. http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html – FairSite2C Feb 02 '19 at 18:32

1 Answers1

0

If you use method syntax, you'll see func quite often, for instance in Where, GroupBy, Join, etc

Every method with some input parameters and one return value can be translated to a Func<...> as follows

MyReturnType DoSomething(ParameterType1 p1, ParameterType2, p2) {...}

Func<ParameterType1, ParameterType2, MyReturnType> myFunc = (x, y) => DoSomething(x, y);

The part Func<ParameterType1, ParameterType2, MyReturnType> means: a function with two input parameters and one return value. The input parameters are of type ParameterType1 and ParameterType2, in this order. The return value is of MyReturnType.

You instantiate an object of Func<ParameterType1, ParameterType2, MyReturnType> using a lambda expression. Before the => you type a declaration for the input parameters, after the => you call the function with these input parameters. If you have more than one input parameter you make them comma separated surrounded by brackets.

For a Where you need a Func<TSource, bool>. So a function that has as input one source element, and as result a bool:

Where(x => x.Name == "John Doe")

For a GroupJoin you need a resultSelector of type Func<TOuter,System.Collections.Generic.IEnumerable<TInner>,TResult> resultSelector

So this is a function with as input one element of the outer sequence, and a sequence of elements of the inner sequence. For example, to query Teachers with their Students:

var result = Teachers.GroupJoin(Students,
    teacher => teacher.Id,           // from every Teacher take the Id,
    student => student.TeacherId,    // from every Student take the TeacherId,
    (teacher, students) => new
    {
        Id = teacher.Id,
        Name = teacher.Name,
        Students = students.Select(student => new
        {
            Id = student.Id,
            Name = student.Name,
        })
        .ToList(),
    });

Here you see several Funcs. TOuter is Teacher, TInner is Student, TKey is int

  • OuterKeySelector: Func<TOuter, TKey>: teacher => teacher.Id
  • InnerKeySelector: Func<TInner, TKey>: student => student.TeacherId
  • ResultSelector: Func<Touter, IEnumerable<TInner>, TResult>

The resultSelector is a function that takes one TOuter (a Teacher), and a sequence of TInner (all students of this Teacher) and creates one object using the input parameters

(teacher, students) => new {... use teacher and students }

When creating the lambda expression it is often helpful if you use plurals to refer to collections (teachers, students) and singulars if you refer one element of a collection (student).

Use the => to start defining the func. You can use input parameters that were defined before the => to define the result after the =>

Harald Coppoolse
  • 28,834
  • 7
  • 67
  • 116