1

I have a problem where the we have a lot of data grouped into what are called stages. The front end wants to display this data, grouped in stages, before rendering it. The number of stages is variable. So originally they were making an api per stage to bunching up the data before binding to a grid control.

When the number of stages become quite high, it becomes too slow. The browser will queue api requests and the overall response time would be very long. Better - we decided - would be to have a single api call that can deal with grouping AND paging. That is, you could say 'take=30' and it would give you 30 records of EACH stage, for however many stages there are.

So the query on the back end looks like what I have below. The problems is that the sorting of the data, before the skip and take is on a dynamic field, and this is where it falls over. Dynamic Linq doesn't seem to work (although it works perfectly fine for normal queries outside the GroupBy call), and I haven't been able to get any of the huge number of extension methods out there working either. They are all some variation of the errors you would find below: (Keep in mind the problem is getting it working with linq to sql)

        TestEntities context = new TestEntities();

        List<TestTable1> result;

        // This works (Hard coding at design time)
        result = context.TestTable1.GroupBy(x => x.StageId)
            .SelectMany(x => x.OrderBy(y => y.StageId).Skip(1).Take(1)).ToList();

        // This works (using a hard coded function at design time)
        Func<TestTable1, int> orderByExpression = x => x.StageId;
        result = context.TestTable1.GroupBy(x => x.StageId)
            .SelectMany(x => x.OrderBy(orderByExpression).Skip(1).Take(1)).ToList();

        // This doesn't work. Dynamic linq            
        result = context.TestTable1.GroupBy(x => x.StageId)
            .SelectMany(x => x.AsQueryable().OrderBy("Name").Skip(1).Take(1)).ToList();

        // This doesn't work. Can't convert an object to a primitive type
        Func<TestTable1, object> orderByObjectExpression = x => x.StageId;
        result = context.TestTable1.GroupBy(x => x.StageId)
            .SelectMany(x => x.AsQueryable().OrderBy(orderByObjectExpression).Skip(1).Take(1)).ToList();

        // This doesn't work. Same as above
        Func<TestTable1, dynamic> orderByDynamicExpression = x => x.StageId;
        result = context.TestTable1.GroupBy(x => x.StageId)
            .SelectMany(x => x.AsQueryable().OrderBy(orderByObjectExpression).Skip(1).Take(1)).ToList();           

        Console.WriteLine(JsonConvert.SerializeObject(result));
        Console.ReadKey();
Tony
  • 11
  • 1
  • Possible duplicate of [How do I specify the Linq OrderBy argument dynamically?](https://stackoverflow.com/questions/7265186/how-do-i-specify-the-linq-orderby-argument-dynamically) – user1672994 Oct 14 '19 at 05:38
  • Can't use reflection with linq to sql – Tony Oct 14 '19 at 05:49
  • Could you show us your method definition (I want to see how orderByObjectExpression is passed to that method)? Do you always work on the same table or do you want to have generic code that works on any table? – Arthur Oct 14 '19 at 08:37
  • BTW you can check thing called Expandable Linq or Expandable query [LINK](http://www.albahari.com/nutshell/linqkit.aspx). Code emission with Expression trees could also help. – Arthur Oct 14 '19 at 08:42
  • @Arthur Thanks so much for your replies. The method definition is pretty much as you see it, it is not passed in, but explicitly declared. Any instance where I explicitly declare the code at design time works, but my goal as you noted is to have it generic, as the sort by field specified by the client could be one of many values, not known until runtime. I don't always work on the same table, but am happy to get it working for just one at the moment. – Tony Oct 15 '19 at 00:07
  • @Arthur Will definitely take a look at expandable linq. I thought perhaps the internal dynamic linq string needed the internal table name it was using, such as [Extent1] or [Project1], but no luck :( – Tony Oct 15 '19 at 00:08

0 Answers0