0

I am trying to do a dynamic order by on columns using Marc Gravell's code. I am posting the 2 queries. It works in one case but doesn't work in 2nd case. Can anybody tell me what changes I need to make to make both queries run perfectly?

This is the link to the Marc Gravell's answer:

https://stackoverflow.com/a/233505

I am using Northwind database. These are both my queries:

 var query = (from cust in northwindEntities.Customers
              select new 
              {
                  City = cust.City ,
                  Orders = northwindEntities.Orders
                       .Where(o => o.CustomerID == cust.CustomerID)
                       .OrderBy("OrderID")
              }); // doesn't work.

 var query = (from cust in northwindEntities.Customers
              select new 
              {
                  City = cust.City ,
                  //Orders = northwindEntities.Orders.Where(o => o.CustomerID == cust.CustomerID).
                  // OrderBy("OrderID")
              }).OrderBy("City"); // works

Here is the exception of the 1st query:

LINQ to Entities does not recognize the method 'System.Linq.IOrderedQueryable1[ConsoleApplication12.Order] OrderBy[Order](System.Linq.IQueryable1[ConsoleApplication12.Order], System.String)' method, and this method cannot be translated into a store expression.

Community
  • 1
  • 1
Jaggu
  • 5,932
  • 16
  • 54
  • 94
  • @ta.speot.is: Exception has already been posted. Please see my edit. – Jaggu Mar 26 '12 at 09:25
  • Should probably be `Orders = cust.Orders.OrderBy("OrderID")`. Not sure if this will fix your problem though. – George Duckett Mar 26 '12 at 09:47
  • @George: Nope! this is not what I want. It *may* solve the issue for this trivial query but not for complex ones. I *need* the ordering to be done in subquery. – Jaggu Mar 26 '12 at 09:53

2 Answers2

0

You need to order the final set of result, like you do it in the second case. In first case you order only northwindEntities.Orders.Where( result and not final one.

The correct query is the second.

Tigran
  • 59,345
  • 8
  • 77
  • 117
0

Obviously it would not work because of the same reason as 

var query = (from cust in northwindEntities.Customers
          select new 
          {
              City = cust.City ,
              Orders = northwindEntities.Orders
                   .MyCustomMethod()
          });

will not work. LINQ-to-Entities will walk through this expression tree and try to convert it to SQL. It can work on known sub set of methods to translate to SQL.

But in the second query, custom OrderBy method dynamically creates the OrderBy that LINQ-to-Entities knows.

Eranga
  • 31,383
  • 5
  • 88
  • 92
  • I understand the reason but any workaround to make it succeed? It so happens nowadays that whatever I try to do, I hit the EF's limitations. I guess I should switch to NHibernate only. – Jaggu Mar 26 '12 at 10:10
  • @Jaggu This would be an inherent problem in any LINQ provider which tries to translate the expression tree to SQL or any other. – Eranga Mar 26 '12 at 10:14
  • NHibernate's CriteriaQueries are much better and robust. We can use twist and turn queries and it still works. That is why it is so popular among the community. – Jaggu Mar 26 '12 at 10:18
  • Any idea how can we create a ssdl function for this similar to this link: http://stackoverflow.com/a/5971677 – Jaggu Mar 26 '12 at 10:19
  • @Jaggu I do not know whether you can use that for `OrderBy` clauses. Try using [Dynamic LINQ](http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx) – Eranga Mar 26 '12 at 10:52