CODEWITHSUNDEEP
entity-frameworkasp.net-web-apiodata
Rahul Kamboj
Rahul Kamboj

Reputation: 86

Entity framework with OData(Web API) is sending Order By clause By default to Sql Query

I am using Web Api with OData. and I have an entity defined in an EF 5.0.
I am sending very simple request to Controller::

 $.ajax({url: "/odata/Details?$top=10",
            type: "GET",
            dataType: 'json',
            success: function (data) {
               viewModel.list(data.value);
            }

Now code on My controller::

 [Queryable]
    public override IQueryable<Area> Get()
    {  
    return db.Area.AsQueryable();
    }

Query i see using SQL Profiler::

 SELECT TOP (@p__linq__1) 
[Project1].[id] AS [id1], 
[Project1].[name] AS [name1], 
[Project1].[pucrhase] AS [pucrhase1], 
[Project1].[sale] AS [sale1]
FROM Area
ORDER BY [Project1].[id] DESC, [Project1].[name] ASC, [Project1].[pucrhase] ASC,      
[Project1].[sale] ASC,N',@p__linq__1 int,@p__linq__1=10

I have not requested for any Ordering , Order By Clause . EF adds ORDER BY clause by Itself to Query. Order By clause added contains all columns of table.This table has 3 millions records and Query is timing Out as it is Ordering by All columns .

I tested by removing Order By it took Less then a second to finish

So Question is

how to Stop Entity framework(support of Web Api Odata ) from sending Order By clause to Sql Query.

How to remove Order By clause from SQL Query Entity framework(Web Api Odata) runs on Server?

Any Help is appreciated.

Upvotes: 7

Views: 4197

Answers (2)

keipa
keipa

Reputation: 792

If you're using OData controller + CosmosDb data, entity framework sends order by c.Id by default.

If you add manually another orderby=field1 asc for instance it will add another clause to query which will cause an error from cosmos

Add this to your data controller, it will remove the default Id ordering

[EnableQuery(EnsureStableOrdering = false)]

Upvotes: 0

Alistair
Alistair

Reputation: 1144

Im not sure if this is the right answer, but im assuming that the odata service is attempting to maintain a stable sort ordering by ordering on all properties within your model.

Therefore try

[Queryable(EnsureStableOrdering=false)]

Upvotes: 13

Related Questions