Linq To Entities - OrderBy

Question

I am following Phil Haacks tutorial on using JQGrid with ASP.Net MVC. My application is ASP.Net MVC 3 using Entity Framework 4.

I have the following code to perform the sorting and paging for my data which is returned to the JQGrid

var query = equipService.GetAllEquipment().AsQueryable()
                .OrderBy("it." + sidx + " " + sord)
                .Skip(pageIndex * pageSize)
                .Take(pageSize);

However, this code creates an error at the .OrderBy("it." + sidx + " " + sord) line. The error is

System.Linq.Queryable.OrderBy<TSource,TKey>(System.Linq.IQueryable<TSource>, System.Linq.Expressions.Expression<System.Func<TSource,TKey>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

The query is calling a method, GetAllEquipment(), in my service layer which looks like this

public List<Equipment> GetAllEquipment()
    {
        List<Equipment> equipList = new List<Equipment>();
        equipList = equipRepository.GetAllEquipment();

        return equipList;
    }

This method, then calls the same name of method in my repository, like so

public List<Equipment> GetAllEquipment()
    {
        var query = (from e in Data.DBEntities.Equipments
                     select e).ToList();

        return query;
    }

I can kind of solve the problem by creating an instance of my objectcontext in my controller method and use this code

using (AssetEntities context = new AssetEntities())
        {
            int pageIndex = Convert.ToInt32(page) - 1;
            int pageSize = rows;
            int totalRecords = context.Equipments.Count();
            int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);


            var query = context.Equipments
              .OrderBy("it." + sidx + " " + sord)
              .Skip(pageIndex * pageSize)
              .Take(pageSize);

        }

However, I don't really want to do this, rather I would like to keep to the repository pattern I use throughout my application for all database interaction.

Any ideas on how I can fix this?

Thanks as ever everyone.

`.OrderBy("it." + sidx + " " + sord)` is *not* dynamic LINQ syntax. It's ESQL / QueryBuilder syntax. Do you know which method you're calling? — Craig Stuntz, Feb 25 '11 at 15:37
@Craig: I had to download the Dynamic Query Library as stated here weblogs.asp.net/scottgu/archive/2008/01/07/…, then reference System.Linq.Dynamic at the top of my contorller. My code now works :) Thanks. — tcode, Feb 25 '11 at 15:56

score 1 · Answer 1 · answered Feb 25 '11 at 09:52

1

Your repository is terribly wrong. It always load all data from db to application and performs paging and sorting in web server's memory. That is a big difference to your last query which executes both sorting and paging in db and returns only data for one page. If you want to create query in controller both repository and service must return IQueryable and they mustn't call ToList. Calling ToList executes current query.

answered Feb 25 '11 at 09:52

Ladislav Mrnka

360,892
59
660
670

I changed both my service and repository methods to return IQueryable, and then changed the call in my controller to IQueryable query = equipService.GetEquipment() .OrderBy(sidx + " " + sord) .Skip(pageIndex * pageSize) .Take(pageSize); – tcode Feb 25 '11 at 09:58
However, I still have the same error as mentioned above. Any ideas? – tcode Feb 25 '11 at 09:58
Are you using some special Linq extenssions like Dynamic Linq? Because common OrderBy doesn't allow specifying ordering as string. – Ladislav Mrnka Feb 25 '11 at 10:10
Yes, I am using Dynamic Linq, as you can see from the following line .OrderBy("it." + sidx + " " + sord) I am concatenating strings together. – tcode Feb 25 '11 at 10:14

score 1 · Accepted Answer · edited May 23 '17 at 10:24

1

I recommed you to use PropertyInfo, GetProperty or FieldInfo, GetField depend on your data model. In the case you can implement OrderBy operation without any extensions. See the answer for details.

UPDATED: I reread your question carefully one more time. It seems to me, that your problem is inside of GetAllEquipment method. It returns List<Equipment> and not IQueryable<Equipment>. So the GetAllEquipment method get "SELECT * FROM it.Equipment" and return the data as a List which are no more Entity. With equipService.GetAllEquipment().AsQueryable() you will have IQueryable<Equipment> object, but you will not more use LINQ to Entity and so you should not use "it." prefix before the names.

edited May 23 '17 at 10:24

Community

1
1

answered Feb 25 '11 at 10:30

Oleg

220,925
34
403
798

@Oleg. I updated my repository and service classes to return IQueryable. I also took out the "it." out of my OrderBy clause. My query now looks like this var query = equipService.GetEquipment() .OrderBy(sidx + " " + sord) .Skip(pageIndex * pageSize) .Take(pageSize); But the same error still exists. – tcode Feb 25 '11 at 15:03
No, my code still isnt working Oleg. I am still getting the error on .OrderBy(sidx + " " + sord) – tcode Feb 25 '11 at 15:06
@Oleg: If i change my query to this...var query = equipService.GetEquipment() .OrderBy(e => e.equipmentID) .Skip(pageIndex * pageSize) .Take(pageSize); This works. So it looks as if my problem is the dynamic OrderBy clause I am using... – tcode Feb 25 '11 at 15:08
@tgriffiths: Do you tried to use `GetProperty` to implement ordering of data like I did in http://stackoverflow.com/questions/4078592/sortable-jqgrid-using-linq-to-mysql-dblinq-and-dynamic-linq-orderby-doesnt-w/4079121#4079121? – Oleg Feb 25 '11 at 15:14
@Oleg: I finally worked it out. I had to download the Dynamic Query Library as stated here http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx, then reference System.Linq.Dynamic at the top of my contorller. My code now works :) Thanks for your help. – tcode Feb 25 '11 at 15:55
@tgriffiths: I am glad to hear, that your program works now, but in case that you use Entity Framework you **don't need** `Dynamic Query Library`. I downloaded Phil Haacks tutorial, made some minimal bug fixing and modifications, replaced LINQ to SQL to Entity Framework, **removed System.Linq.Dynamic** and all work without any problem. I don't know what problem exist in your code, but you can download Phil Haack's demo after my modification from http://www.ok-soft-gmbh.com/jqGrid/JQueryGridGemo.zip and verify that it works. – Oleg Feb 25 '11 at 16:43
@Oleg: I think this might explain why I had to use Dynamic Query Language http://stackoverflow.com/questions/5140809/linq-dynamic-query-library – tcode Mar 01 '11 at 16:23
@tgriffiths: Your problem is that you try to mix LINQ to Entities and Entity SQL queries (see [here](http://msdn.microsoft.com/en-us/library/bb896238.aspx)). The `Data.DBEntities.Equipments` object is `ObjectQuery` if support `IQueryable`, but in other SQL dialect: [Entity SQL](http://msdn.microsoft.com/en-us/library/bb399560.aspx). Probably I don't commented my last demo enough. In your example it means, that you just should use **directly** `Data.DBEntities.Equipments` instead of `equipService.GetAllEquipment().AsQueryable()` and all will work. – Oleg Mar 01 '11 at 17:26
@tgriffiths: The `ObjectQuery` has the method `OrderBy(string)` method (see [here](http://msdn.microsoft.com/en-us/library/bb358828.aspx)) and LINQ To SQL **not has** (see [here](http://msdn.microsoft.com/en-us/library/system.linq.queryable.orderby.aspx)). So it you stay by LINQ to Entities you can implement all what you need for jqGrid **without System.Linq.Dynamic**. – Oleg Mar 01 '11 at 17:29

Linq To Entities - OrderBy

2 Answers2