ODataQueryOptions do not apply $filter and $orderby options to IQueryable when using EntityFramework 6 with SQL Server

00-README.md

First Approach

• Node.cs
• NodesController.cs
• Http Request / Response
• SQL Command

02-NodesController.cs

[AppclusiveAuthorize]
[EnableQuery(PageSize = 15)]
public async Task<IHttpActionResult> GetNodes(ODataQueryOptions<Node> queryOptions)
{
  Contract.Requires(null != queryOptions, "|400|");

  queryOptions.Validate(_validationSettings);

  var appliedEntitySet = queryOptions.ApplyTo(db.Nodes.AsQueryable<Node>());

  var entitySet = new List<Node>();
  foreach(Node entity in appliedEntitySet)
  {
    if(new AccessManager().HasPermission(entity, Permissions.NodesCanRead))
    {
      entitySet.Add(entity);
    }
  }
  return Ok<IEnumerable<Node>>(entitySet);
}

03-HttpRequestResponse.js

GET http://localhost:53422/api/Core/Nodes()?$filter=Name%20ne%20'Root%20Node'&$orderby=Modified,%20Created%20desc,%20Id%20asc&$skip=5&$top=35 HTTP/1.1
DataServiceVersion: 1.0;NetFx
MaxDataServiceVersion: 3.0;NetFx
Accept: application/json;odata=minimalmetadata
Accept-Charset: UTF-8
User-Agent: Microsoft ADO.NET Data Services
Authorization: Basic UXVlc3Rpb246d2hvLXJlYWxseS1wb3N0cy1CQVNFNjQtZW5jb2RlZC1jcmVkZW50aWFscy10by1wdWJsaWMtd2ViLXNpdGVz
Host: localhost:53422

HTTP/1.1 200 OK
Cache-Control: no-cache
Pragma: no-cache
Content-Type: application/json; odata=minimalmetadata; charset=utf-8
Expires: -1
Server: Microsoft-IIS/8.0
Set-Cookie: biz.dfch.CS.Appclusive.Core.Security.AuthenticationFilters.MultiAuthenticationFilter=1A-AC-01-54-27-30-F4-94-FE-70-31-C2-26-59-BB-7D-B3-F2-3D-24-FE-21-0E-FF-D5-AC-D9-3C-8D-2A-88-83
DataServiceVersion: 3.0
X-AspNet-Version: 4.0.30319
Persistent-Auth: true
X-Powered-By: ASP.NET
Date: Sat, 27 Feb 2016 14:11:38 GMT
Content-Length: 4882

{
  "odata.metadata":"http://localhost:53422/api/Core/$metadata#Nodes","value":[
    {
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"23","Tid":"22222222-2222-2222-2222-222222222222","Name":"locally","Description":"locally is a aggrade for ultra purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:52.7470965+01:00","Modified":"2016-02-19T13:05:52.7470965+01:00","RowVersion":"AAAAAAAAjYE="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"25","Tid":"22222222-2222-2222-2222-222222222222","Name":"matins","Description":"matins is a petrosal for swain purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.3061285+01:00","Modified":"2016-02-19T13:05:53.3061285+01:00","RowVersion":"AAAAAAAAjY0="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"27","Tid":"22222222-2222-2222-2222-222222222222","Name":"leal","Description":"leal is a varices for aggrade purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.9841672+01:00","Modified":"2016-02-19T13:05:53.9841672+01:00","RowVersion":"AAAAAAAAjZk="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"29","Tid":"22222222-2222-2222-2222-222222222222","Name":"batten","Description":"batten is a ultra for avaunt purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:54.6532055+01:00","Modified":"2016-02-19T13:05:54.6532055+01:00","RowVersion":"AAAAAAAAjaU="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"31","Tid":"22222222-2222-2222-2222-222222222222","Name":"ultra","Description":"ultra is a stagnate for jut purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:55.2842416+01:00","Modified":"2016-02-19T13:05:55.2842416+01:00","RowVersion":"AAAAAAAAjbE="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"33","Tid":"22222222-2222-2222-2222-222222222222","Name":"vitellin","Description":"vitellin is a locate for ecbolic purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:55.8562743+01:00","Modified":"2016-02-19T13:05:55.8562743+01:00","RowVersion":"AAAAAAAAjb0="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"35","Tid":"22222222-2222-2222-2222-222222222222","Name":"frag","Description":"frag is a barogram for missal purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:56.4993111+01:00","Modified":"2016-02-19T13:05:56.4993111+01:00","RowVersion":"AAAAAAAAjck="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"37","Tid":"22222222-2222-2222-2222-222222222222","Name":"recoup","Description":"recoup is a oxidase for locate purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:57.3283585+01:00","Modified":"2016-02-19T13:05:57.3283585+01:00","RowVersion":"AAAAAAAAjdU="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"39","Tid":"22222222-2222-2222-2222-222222222222","Name":"rainbow","Description":"rainbow is a proa for lurdan purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:58.1964082+01:00","Modified":"2016-02-19T13:05:58.1964082+01:00","RowVersion":"AAAAAAAAjeE="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"41","Tid":"22222222-2222-2222-2222-222222222222","Name":"fro","Description":"fro is a mariner for cleft purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:00.028513+01:00","Modified":"2016-02-19T13:06:00.028513+01:00","RowVersion":"AAAAAAAAje0="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"43","Tid":"22222222-2222-2222-2222-222222222222","Name":"melic","Description":"melic is a leaky for espy purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:00.7705554+01:00","Modified":"2016-02-19T13:06:00.7705554+01:00","RowVersion":"AAAAAAAAjfk="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"45","Tid":"22222222-2222-2222-2222-222222222222","Name":"peen","Description":"peen is a repel for barogram purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:01.5085976+01:00","Modified":"2016-02-19T13:06:01.5085976+01:00","RowVersion":"AAAAAAAAjgY="
    },{
      "EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"47","Tid":"22222222-2222-2222-2222-222222222222","Name":"lollygag","Description":"lollygag is a baronet for enclasp purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:02.0976313+01:00","Modified":"2016-02-19T13:06:02.0976313+01:00","RowVersion":"AAAAAAAAjhI="
    }
  ]
}

04-SqlCommand.sql

2016-02-27 15:02:36,672 [7] INFO  biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> - p__linq__0: Root Node
2016-02-27 15:02:36,672 [7] INFO  biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> - p__linq__1: 5
2016-02-27 15:02:36,672 [7] INFO  biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> - p__linq__2: 35
2016-02-27 15:02:36,700 [7] INFO  biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> - Intercepted on: ReaderExecuted :- IsAsync: False, Command Text: 
SELECT 
    [Project1].[Id] AS [Id], 
    [Project1].[EntityId] AS [EntityId], 
    [Project1].[Parameters] AS [Parameters], 
    [Project1].[EntityKindId] AS [EntityKindId], 
    [Project1].[ParentId] AS [ParentId], 
    [Project1].[Tid] AS [Tid], 
    [Project1].[Name] AS [Name], 
    [Project1].[Description] AS [Description], 
    [Project1].[CreatedById] AS [CreatedById], 
    [Project1].[ModifiedById] AS [ModifiedById], 
    [Project1].[Created] AS [Created], 
    [Project1].[Modified] AS [Modified], 
    [Project1].[RowVersion] AS [RowVersion]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[EntityId] AS [EntityId], 
        [Extent1].[Parameters] AS [Parameters], 
        [Extent1].[EntityKindId] AS [EntityKindId], 
        [Extent1].[ParentId] AS [ParentId], 
        [Extent1].[Tid] AS [Tid], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[CreatedById] AS [CreatedById], 
        [Extent1].[ModifiedById] AS [ModifiedById], 
        [Extent1].[Created] AS [Created], 
        [Extent1].[Modified] AS [Modified], 
        [Extent1].[RowVersion] AS [RowVersion]
        FROM [core].[Node] AS [Extent1]
        WHERE (([Extent1].[Tid] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND (([Extent1].[Tid] = @DynamicFilterParam_3) OR (@DynamicFilterParam_4 IS NOT NULL)) AND ( NOT (([Extent1].[Name] = @p__linq__0) AND (0 = (CASE WHEN (@p__linq__0 IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END))))
    )  AS [Project1]
    ORDER BY [Project1].[Modified] ASC, [Project1].[Created] DESC, [Project1].[Id] ASC
    OFFSET @p__linq__1 ROWS FETCH NEXT @p__linq__2 ROWS ONLY  

LICENSE

/**
 * Copyright 2014-2016 d-fens GmbH
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */