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

This blog post is a bit more lengthly than my usual posts and the idea came from an issue I faced when working in one of our project where we use an OData v3 REST interface that talks to an MS SQL Server (2012 R2) via EntityFramework v6.1.3.

In short: we wanted to implement ‘entity framework row level security’ (in a different approach than described in Web app with a multi-tenant database using Entity Framework and Row-Level Security) and realised that the ODataQueryOptions were not applied to the underlying SQL query but instead were only filtered by the framework when returning the result set. In our scenario this had a heavy impact, as we were traversing the resulting entity set effectively doing a full table scan on every query. A simple request https://www.example.com/baseurl/Endpoint/Jobs?$filter=startswith(Name , 'A') therefore took several seconds instead of microseconds.

This post is structured in the following sections:

The Setup

The situation started with this simple controller method for a Get* method that queries a SQL table Node via:

~/Nodes()?$filter=Name ne 'Root%20Node'&$orderby=Modified, Created desc, Id asc&$skip=5&$top=35`
  1. We have a model Node.cs
  2. … that is queried by the controller GetNodes in NodesController.cs
  3. We issue a GET request against this controller and see its response in HttpRequestResponse.js
  4. The internal SQL query is shown in SqlCommand.sql revealed via the IDbCommandInterceptor interface enabled in web.config
public class Node : BaseEntity
{
public long? EntityId { get; set; }
[Required]
public string Parameters { get; set; }
[Required]
public long EntityKindId { get; set; }
[JsonIgnore]
[ForeignKey("EntityKindId")]
public virtual EntityKind EntityKind { get; set; }
[Required]
public long ParentId { get; set; }
[JsonIgnore]
[ForeignKey("ParentId")]
public virtual Node Parent { get; set; }
[JsonIgnore]
public virtual ICollection<Node> Children { get; set; }
[JsonIgnore]
[InverseProperty("Destination")]
public virtual ICollection<Assoc> IncomingAssocs { get; set; }
[JsonIgnore]
[InverseProperty("Source")]
public virtual ICollection<Assoc> OutgoingAssocs { get; set; }
}

view raw
01-Node.cs
hosted with ❤ by GitHub

[AppclusiveAuthorize]
[EnableQuery(PageSize = 45)]
public async Task<IHttpActionResult> GetNodes(ODataQueryOptions<Node> queryOptions)
{
Contract.Requires(null != queryOptions, "|400|");
queryOptions.Validate(_validationSettings);
return Ok<IEnumerable<Node>>(db.Nodes);
}

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
AcceptCharset: UTF8
UserAgent: Microsoft ADO.NET Data Services
Authorization: Basic UXVlc3Rpb246d2hvLXJlYWxseS1wb3N0cy1CQVNFNjQtZW5jb2RlZC1jcmVkZW50aWFscy10by1wdWJsaWMtd2ViLXNpdGVz
Host: localhost:53422
HTTP/1.1 200 OK
CacheControl: nocache
Pragma: nocache
ContentType: application/json; odata=minimalmetadata; charset=utf8
Expires: 1
Server: MicrosoftIIS/8.0
SetCookie: biz.dfch.CS.Appclusive.Core.Security.AuthenticationFilters.MultiAuthenticationFilter=ABC04FACB70172EF578074A3B0E4D6E3A44C5A73794DBA64A9033756D49BA64D
DataServiceVersion: 3.0
XAspNetVersion: 4.0.30319
PersistentAuth: true
XPoweredBy: ASP.NET
Date: Sat, 27 Feb 2016 13:03:28 GMT
ContentLength: 5826
{
"odata.metadata":"http://localhost:53422/api/Core/$metadata#Nodes&quot;,"value":[
{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"13","Tid":"22222222-2222-2222-2222-222222222222","Name":"locally","Description":"locally is a mandrake for whiles purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:49.7239236+01:00","Modified":"2016-02-19T13:05:49.7239236+01:00","RowVersion":"AAAAAAAAjUU="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"14","Tid":"22222222-2222-2222-2222-222222222222","Name":"sneak","Description":"sneak is a pitman for stagnate purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:50.0039396+01:00","Modified":"2016-02-19T13:05:50.0039396+01:00","RowVersion":"AAAAAAAAjUs="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"15","Tid":"22222222-2222-2222-2222-222222222222","Name":"recess","Description":"recess is a oxidase for rainbow purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:50.3719606+01:00","Modified":"2016-02-19T13:05:50.3719606+01:00","RowVersion":"AAAAAAAAjVE="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"16","Tid":"22222222-2222-2222-2222-222222222222","Name":"petrosal","Description":"petrosal is a ramrod for carabao purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:50.6599771+01:00","Modified":"2016-02-19T13:05:50.6599771+01:00","RowVersion":"AAAAAAAAjVc="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"17","Tid":"22222222-2222-2222-2222-222222222222","Name":"carabao","Description":"carabao is a hindmost for caroche purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:50.9159918+01:00","Modified":"2016-02-19T13:05:50.9159918+01:00","RowVersion":"AAAAAAAAjV0="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"18","Tid":"22222222-2222-2222-2222-222222222222","Name":"still","Description":"still is a basque for mainland purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.2250094+01:00","Modified":"2016-02-19T13:05:51.2250094+01:00","RowVersion":"AAAAAAAAjWM="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"19","Tid":"22222222-2222-2222-2222-222222222222","Name":"vendace","Description":"vendace is a lurdan for joy purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.5430276+01:00","Modified":"2016-02-19T13:05:51.5430276+01:00","RowVersion":"AAAAAAAAjWk="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"20","Tid":"22222222-2222-2222-2222-222222222222","Name":"mariner","Description":"mariner is a covert for derma purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.8220436+01:00","Modified":"2016-02-19T13:05:51.8220436+01:00","RowVersion":"AAAAAAAAjW8="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"21","Tid":"22222222-2222-2222-2222-222222222222","Name":"terrapin","Description":"terrapin is a sporty for spicule purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:52.0860587+01:00","Modified":"2016-02-19T13:05:52.0860587+01:00","RowVersion":"AAAAAAAAjXU="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"22","Tid":"22222222-2222-2222-2222-222222222222","Name":"vitellin","Description":"vitellin is a squeeze for varices purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:52.3860758+01:00","Modified":"2016-02-19T13:05:52.3860758+01:00","RowVersion":"AAAAAAAAjXs="
},{
"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":"24","Tid":"22222222-2222-2222-2222-222222222222","Name":"missal","Description":"missal is a resale for vendace purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.0281126+01:00","Modified":"2016-02-19T13:05:53.0281126+01:00","RowVersion":"AAAAAAAAjYc="
},{
"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":"26","Tid":"22222222-2222-2222-2222-222222222222","Name":"oxidase","Description":"oxidase is a ultra for mannish purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.6611488+01:00","Modified":"2016-02-19T13:05:53.6611488+01:00","RowVersion":"AAAAAAAAjZM="
},{
"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="
}
]
,
"odata.nextLink":"http://localhost:53422/api/Core/Nodes()?$filter=Name%20ne%20%27Root%20Node%27&$orderby=Modified%2C%20Created%20desc%2C%20Id%20asc&$top=20&$skip=20"
}

20160227 14:03:28,285 [9] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__0: Root Node
20160227 14:03:28,286 [9] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__1: 5
20160227 14:03:28,286 [9] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__2: 35
20160227 14:03:28,323 [9] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> Intercepted on: ReaderExecuted : IsAsync: False, Command Text:
SELECT TOP (16)
[Limit1].[Id] AS [Id],
[Limit1].[EntityId] AS [EntityId],
[Limit1].[Parameters] AS [Parameters],
[Limit1].[EntityKindId] AS [EntityKindId],
[Limit1].[ParentId] AS [ParentId],
[Limit1].[Tid] AS [Tid],
[Limit1].[Name] AS [Name],
[Limit1].[Description] AS [Description],
[Limit1].[CreatedById] AS [CreatedById],
[Limit1].[ModifiedById] AS [ModifiedById],
[Limit1].[Created] AS [Created],
[Limit1].[Modified] AS [Modified],
[Limit1].[RowVersion] AS [RowVersion]
FROM ( 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
) AS [Limit1]
ORDER BY [Limit1].[Modified] ASC, [Limit1].[Created] DESC, [Limit1].[Id] ASC

view raw
04-SqlCommand.sql
hosted with ❤ by GitHub

/**
* 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.
*/

view raw
LICENSE
hosted with ❤ by GitHub

Here we see that the query options from the ODATA query are applied to the SQL table via three nested SQL SELECT statements. The outer SELECT adds a TOP 16 (which is page size plus 1) to determine if an OData NextLink should be added to the HTTP response. In fact this is a bit redundant as the inner SELECT statement utilises the OFFSET option to apply both the $skip and the $top options to the query. And for whatever reason the $orderby clause is applied twice via SQL ORDER BY in the inner and outer command. In addition, it is interesting to see, that the $top constraint is applied to the inner SELECT statement, though the outer statement reduces the amount to the defined page size (which is smaller). So in a worst case scenario a client performs a query which $top set to a huge value that is later restricted to a much smaller page size.

In the HTTP response you can see the odata.nextLink which includes the original query options plus the adjusted $skip and $top options. For an unknown reason the $top option is set to 20 though our defined PageSize is set to 15.

Note1: Just in case you wonder what the @DynamicFilterParam_1 param means. This is due to the fact that we utilise EntityFramework.DynamicFilters to implement tenant filtering in our database tables.

Note2: The outer SELECT statement is actually inserted due to the EnableConstantParameterization property which is by default set to true.

The Problem

So far, so good. However, when we implemented a row level or entity level check things got a little bit messy.

  1. The model stays the same (actually through the course of this blog post)
  2. The controller NodesController.cs now performs some kind of permission check based on the entity …
  3. … and the result is just as expected with 15 entities and an adjusted $skip option (and $top still wrong, but that does not matter).
  4. however, the underlying SQL statement reveals that it actually performs a full table scan SELECT * FROM Node and the filtering and ordering are performed when returning via Ok&lt;IEnumerable&gt;(entitySet).
[AppclusiveAuthorize]
[EnableQuery(PageSize = 15)]
public async Task<IHttpActionResult> GetNodes(ODataQueryOptions<Node> queryOptions)
{
Contract.Requires(null != queryOptions, "|400|");
queryOptions.Validate(_validationSettings);
var entitySet = new List<Node>();
foreach(var entity in db.Nodes)
{
if(new AccessManager().HasPermission(entity, Permissions.NodesCanRead))
{
entitySet.Add(entity);
}
}
return Ok<IEnumerable<Node>>(entitySet);
}

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
AcceptCharset: UTF8
UserAgent: Microsoft ADO.NET Data Services
Authorization: Basic UXVlc3Rpb246d2hvLXJlYWxseS1wb3N0cy1CQVNFNjQtZW5jb2RlZC1jcmVkZW50aWFscy10by1wdWJsaWMtd2ViLXNpdGVz
Host: localhost:53422
HTTP/1.1 200 OK
CacheControl: nocache
Pragma: nocache
ContentType: application/json; odata=minimalmetadata; charset=utf8
Expires: 1
Server: MicrosoftIIS/8.0
SetCookie: biz.dfch.CS.Appclusive.Core.Security.AuthenticationFilters.MultiAuthenticationFilter=0FDA54E4EDBCE0C1BF4698B2614705091127977AEC8881AAB754FE1B30AB73D0
DataServiceVersion: 3.0
XAspNetVersion: 4.0.30319
PersistentAuth: true
XPoweredBy: ASP.NET
Date: Sat, 27 Feb 2016 13:41:10 GMT
ContentLength: 5826
{
"odata.metadata":"http://localhost:53422/api/Core/$metadata#Nodes&quot;,"value":[
{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"19","Tid":"22222222-2222-2222-2222-222222222222","Name":"vendace","Description":"vendace is a lurdan for joy purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.5430276+01:00","Modified":"2016-02-19T13:05:51.5430276+01:00","RowVersion":"AAAAAAAAjWk="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"21","Tid":"22222222-2222-2222-2222-222222222222","Name":"terrapin","Description":"terrapin is a sporty for spicule purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:52.0860587+01:00","Modified":"2016-02-19T13:05:52.0860587+01:00","RowVersion":"AAAAAAAAjXU="
},{
"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="
}
]
,
"odata.nextLink":"http://localhost:53422/api/Core/Nodes()?$filter=Name%20ne%20%27Root%20Node%27&$orderby=Modified%2C%20Created%20desc%2C%20Id%20asc&$top=20&$skip=20"
}

20160227 14:41:00,120 [8] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> Intercepted on: ReaderExecuted : IsAsync: False, Command Text: SELECT
[Var_4].[Id] AS [Id],
[Var_4].[EntityId] AS [EntityId],
[Var_4].[Parameters] AS [Parameters],
[Var_4].[EntityKindId] AS [EntityKindId],
[Var_4].[ParentId] AS [ParentId],
[Var_4].[Tid] AS [Tid],
[Var_4].[Name] AS [Name],
[Var_4].[Description] AS [Description],
[Var_4].[CreatedById] AS [CreatedById],
[Var_4].[ModifiedById] AS [ModifiedById],
[Var_4].[Created] AS [Created],
[Var_4].[Modified] AS [Modified],
[Var_4].[RowVersion] AS [RowVersion]
FROM [core].[Node] AS [Var_4]
WHERE (([Var_4].[Tid] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND (([Var_4].[Tid] = @DynamicFilterParam_3) OR (@DynamicFilterParam_4 IS NOT NULL))

view raw
04-SqlCommand.sql
hosted with ❤ by GitHub

/**
* 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.
*/

view raw
LICENSE
hosted with ❤ by GitHub

You can imagine that first performing a permission check on every entity and secondly filtering the actual results is far from desired. And there is side effect that is currently not observable, but we will come to that later.

Note: for illustration purpose in this example our ‘permission check’ only returns entities Id % 2 == 0.

First Approach

Luckily, we have some kind of queryOptions passed to our controller method that have an ApplyTo method that should help us to transform the $orderby and $filter options into SQL statements.

  1. The model stays the same
  2. This time we use ApplyTo to apply $orderby, $filter, $top and $skip
  3. … and get a result back
  4. The underlying SQL statement shows that the options are really applied at the database level
[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);
}

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
AcceptCharset: UTF8
UserAgent: Microsoft ADO.NET Data Services
Authorization: Basic UXVlc3Rpb246d2hvLXJlYWxseS1wb3N0cy1CQVNFNjQtZW5jb2RlZC1jcmVkZW50aWFscy10by1wdWJsaWMtd2ViLXNpdGVz
Host: localhost:53422
HTTP/1.1 200 OK
CacheControl: nocache
Pragma: nocache
ContentType: application/json; odata=minimalmetadata; charset=utf8
Expires: 1
Server: MicrosoftIIS/8.0
SetCookie: biz.dfch.CS.Appclusive.Core.Security.AuthenticationFilters.MultiAuthenticationFilter=1AAC01542730F494FE7031C22659BB7DB3F23D24FE210EFFD5ACD93C8D2A8883
DataServiceVersion: 3.0
XAspNetVersion: 4.0.30319
PersistentAuth: true
XPoweredBy: ASP.NET
Date: Sat, 27 Feb 2016 14:11:38 GMT
ContentLength: 4882
{
"odata.metadata":"http://localhost:53422/api/Core/$metadata#Nodes&quot;,"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="
}
]
}

20160227 15:02:36,672 [7] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__0: Root Node
20160227 15:02:36,672 [7] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__1: 5
20160227 15:02:36,672 [7] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__2: 35
20160227 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

view raw
04-SqlCommand.sql
hosted with ❤ by GitHub

/**
* 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.
*/

view raw
LICENSE
hosted with ❤ by GitHub

This looks good at first sight. However, looking closer we see a couple of potential problems:

  1. The OData NextLink is missing
  2. The response only returns 13 entities (instead of requested 35 or PageSize (which is 15) entities. The page size is actually totally ignored
  3. The first two entities (with Id 19 and 21) which we got returned from the the previous query are skipped from this response
  4. The inner SQL SELECT does restrict the query to the $skip and $top option (I am not a SQL expert nor did I research on that; so maybe this is not a performance impact)

Of course, our permission check ‘cut away’ some records from the result set. This was not visible in the previous example as we first accidentally performed an implicit full table scan (SELECT *) and then performed the permission check. And as we end up with a result set smaller than the page size, OData certainly does not include a next link.
This is somehow understandable, but the real problem stems from fact the we miss two valid entities. When debugging the code, we see that our permission actually does process the missing records. This effectively means that the return statement Ok&lt;IEnumerable&gt;(entitySet) skips the 5 records which is not our intention. If we had selected a smaller $top count we would have got returned even fewer records. Furthermore the $skip count in the nextlink is wrong as it does not take the skipped records from the permission check into calculation.

The Solution

To actually perform a query to the database that actually reads until the pagesize (+1) is reached we need a modified ApplyTo method and a SetNextLink method that takes any skipped records into account.

  1. The model stays the same
  2. The controller performs a query via our new PageableEntityFilter method. This method does all the heavy lifting and invokes our permission check as a Func that either returns true or false to indicate if an entity should be returned to the caller.
  3. This time our response returns us records with Id starting at 18 and generates a $skip option of 41 (and the $top option is also corrected). Id 18 is actually correct, as the entity set of Nodes has Ids starting at 8. As according to our permission filter every even entity is to be included in the result set and we specified to skip 5 entities (8, 10, 12, 14, 16) the first entity to be returned is 18.
  4. This time we get two SQL statements showing an increased skip OFFSET of 15 and 31
[AppclusiveAuthorize]
public async Task<IHttpActionResult> GetNodes(ODataQueryOptions<Node> queryOptions)
{
Contract.Requires(null != queryOptions, "|400|");
queryOptions.Validate(_validationSettings);
db.DisableGeneralTenantFilterForUberAdmin();
var pageableFilterResult = db.Nodes.PageableEntityFilter(queryOptions, (entity, context) =>
{
if(context.AccessManager.HasPermission(entity, Permissions.NodesCanRead))
{
return true;
}
return false;
});
this.SetNextLink(pageableFilterResult.EntitySet, pageableFilterResult.SkipCount);
return Ok<IEnumerable<Node>>(pageableFilterResult.EntitySet);
}

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
AcceptCharset: UTF8
UserAgent: Microsoft ADO.NET Data Services
Authorization: Basic UXVlc3Rpb246d2hvLXJlYWxseS1wb3N0cy1CQVNFNjQtZW5jb2RlZC1jcmVkZW50aWFscy10by1wdWJsaWMtd2ViLXNpdGVz
Host: localhost:53422
HTTP/1.1 200 OK
CacheControl: nocache
Pragma: nocache
ContentType: application/json; odata=minimalmetadata; charset=utf8
Expires: 1
Server: MicrosoftIIS/8.0
SetCookie: biz.dfch.CS.Appclusive.Core.Security.AuthenticationFilters.MultiAuthenticationFilter=D405FC924110BB316F7C18B91F0C9162B8E04F2C0CF6BEA8F253197606A3B45A
DataServiceVersion: 3.0
XAspNetVersion: 4.0.30319
PersistentAuth: true
XPoweredBy: ASP.NET
Date: Sat, 27 Feb 2016 15:57:30 GMT
ContentLength: 5819
{
"odata.metadata":"http://localhost:53422/api/Core/$metadata#Nodes&quot;,"value":[
{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"18","Tid":"22222222-2222-2222-2222-222222222222","Name":"still","Description":"still is a basque for mainland purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.2250094+01:00","Modified":"2016-02-19T13:05:51.2250094+01:00","RowVersion":"AAAAAAAAjWM="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"20","Tid":"22222222-2222-2222-2222-222222222222","Name":"mariner","Description":"mariner is a covert for derma purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.8220436+01:00","Modified":"2016-02-19T13:05:51.8220436+01:00","RowVersion":"AAAAAAAAjW8="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"20","Tid":"22222222-2222-2222-2222-222222222222","Name":"mariner","Description":"mariner is a covert for derma purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:51.8220436+01:00","Modified":"2016-02-19T13:05:51.8220436+01:00","RowVersion":"AAAAAAAAjW8="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"22","Tid":"22222222-2222-2222-2222-222222222222","Name":"vitellin","Description":"vitellin is a squeeze for varices purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:52.3860758+01:00","Modified":"2016-02-19T13:05:52.3860758+01:00","RowVersion":"AAAAAAAAjXs="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"24","Tid":"22222222-2222-2222-2222-222222222222","Name":"missal","Description":"missal is a resale for vendace purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.0281126+01:00","Modified":"2016-02-19T13:05:53.0281126+01:00","RowVersion":"AAAAAAAAjYc="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"26","Tid":"22222222-2222-2222-2222-222222222222","Name":"oxidase","Description":"oxidase is a ultra for mannish purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:53.6611488+01:00","Modified":"2016-02-19T13:05:53.6611488+01:00","RowVersion":"AAAAAAAAjZM="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"28","Tid":"22222222-2222-2222-2222-222222222222","Name":"terrapin","Description":"terrapin is a fraenum for caroche purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:54.259183+01:00","Modified":"2016-02-19T13:05:54.259183+01:00","RowVersion":"AAAAAAAAjZ8="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"30","Tid":"22222222-2222-2222-2222-222222222222","Name":"stagnate","Description":"stagnate is a potheen for pitman purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:55.0032255+01:00","Modified":"2016-02-19T13:05:55.0032255+01:00","RowVersion":"AAAAAAAAjas="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"32","Tid":"22222222-2222-2222-2222-222222222222","Name":"fraenum","Description":"fraenum is a hindmost for vendace purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:55.5642576+01:00","Modified":"2016-02-19T13:05:55.5642576+01:00","RowVersion":"AAAAAAAAjbc="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"34","Tid":"22222222-2222-2222-2222-222222222222","Name":"matins","Description":"matins is a baronet for overtake purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:56.1272898+01:00","Modified":"2016-02-19T13:05:56.1272898+01:00","RowVersion":"AAAAAAAAjcM="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"36","Tid":"22222222-2222-2222-2222-222222222222","Name":"unwashed","Description":"unwashed is a petrosal for aside purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:56.9203352+01:00","Modified":"2016-02-19T13:05:56.9203352+01:00","RowVersion":"AAAAAAAAjc8="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"38","Tid":"22222222-2222-2222-2222-222222222222","Name":"revolute","Description":"revolute is a mannish for leal purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:57.721381+01:00","Modified":"2016-02-19T13:05:57.721381+01:00","RowVersion":"AAAAAAAAjds="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"40","Tid":"22222222-2222-2222-2222-222222222222","Name":"toneme","Description":"toneme is a papaya for lollygag purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:05:59.0604576+01:00","Modified":"2016-02-19T13:05:59.0604576+01:00","RowVersion":"AAAAAAAAjec="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"42","Tid":"22222222-2222-2222-2222-222222222222","Name":"leaky","Description":"leaky is a pitman for leal purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:00.3735327+01:00","Modified":"2016-02-19T13:06:00.3735327+01:00","RowVersion":"AAAAAAAAjfM="
},{
"EntityId":null,"Parameters":"{}","EntityKindId":"23","ParentId":"1","Id":"44","Tid":"22222222-2222-2222-2222-222222222222","Name":"lurdan","Description":"lurdan is a resale for oodles purposes","CreatedById":"3","ModifiedById":"3","Created":"2016-02-19T13:06:01.2015801+01:00","Modified":"2016-02-19T13:06:01.2015801+01:00","RowVersion":"AAAAAAAAjf8="
}
],"odata.nextLink":"http://localhost:53422/api/Core/Nodes()?$filter=Name%20ne%20'Root%20Node'&$orderby=Modified,%20Created%20desc,%20Id%20asc&$top=35&$skip=41"
}

20160227 18:36:15,206 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__0: Root Node
20160227 18:36:15,207 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__1: 0
20160227 18:36:15,207 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__2: 16
20160227 18:36:15,213 [36] 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
[Limit1].[Id] AS [Id],
[Limit1].[EntityId] AS [EntityId],
[Limit1].[Parameters] AS [Parameters],
[Limit1].[EntityKindId] AS [EntityKindId],
[Limit1].[ParentId] AS [ParentId],
[Limit1].[Tid] AS [Tid],
[Limit1].[Name] AS [Name],
[Limit1].[Description] AS [Description],
[Limit1].[CreatedById] AS [CreatedById],
[Limit1].[ModifiedById] AS [ModifiedById],
[Limit1].[Created] AS [Created],
[Limit1].[Modified] AS [Modified],
[Limit1].[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]
ORDER BY [Extent1].[Id] ASC
OFFSET 15 ROWS FETCH NEXT 16 ROWS ONLY
) AS [Limit1]
WHERE (([Limit1].[Tid] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND (([Limit1].[Tid] = @DynamicFilterParam_3) OR (@DynamicFilterParam_4 IS NOT NULL)) AND ( NOT (([Limit1].[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
20160227 18:36:15,222 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__0: Root Node
20160227 18:36:15,223 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__1: 0
20160227 18:36:15,223 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> p__linq__2: 16
20160227 18:36:15,230 [36] 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
[Limit1].[Id] AS [Id],
[Limit1].[EntityId] AS [EntityId],
[Limit1].[Parameters] AS [Parameters],
[Limit1].[EntityKindId] AS [EntityKindId],
[Limit1].[ParentId] AS [ParentId],
[Limit1].[Tid] AS [Tid],
[Limit1].[Name] AS [Name],
[Limit1].[Description] AS [Description],
[Limit1].[CreatedById] AS [CreatedById],
[Limit1].[ModifiedById] AS [ModifiedById],
[Limit1].[Created] AS [Created],
[Limit1].[Modified] AS [Modified],
[Limit1].[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]
ORDER BY [Extent1].[Id] ASC
OFFSET 31 ROWS FETCH NEXT 16 ROWS ONLY
) AS [Limit1]
WHERE (([Limit1].[Tid] = @DynamicFilterParam_1) OR (@DynamicFilterParam_2 IS NOT NULL)) AND (([Limit1].[Tid] = @DynamicFilterParam_3) OR (@DynamicFilterParam_4 IS NOT NULL)) AND ( NOT (([Limit1].[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
20160227 18:36:15,233 [36] INFO biz.dfch.CS.Utilities.Logging.LogBase [(null)] <(null)> 000000000000000053000080020000fbEND [200]

view raw
04-SqlCommand.sql
hosted with ❤ by GitHub

public static PageableEntityFilterResult<TEntity> PageableEntityFilter<TEntity>
(
this IQueryable<TEntity> queryable
,
ODataQueryOptions<TEntity> queryOptions
,
Func<TEntity, PageableEntityFilterContext, bool> filter
)
where TEntity : BaseEntity
{
Contract.Requires(null != queryable);
Contract.Requires(null != queryOptions);
Contract.Requires(null != filter);
Contract.Ensures(null != Contract.Result<PageableEntityFilterResult<TEntity>>());
// accessManager will be passed down to the filter
var accessManager = new AccessManager();
var context = new PageableEntityFilterContext();
context.AccessManager = accessManager;
// get skip count and top count if specified
// round down top count to max pageSize+1
var originalSkipCount = null != queryOptions.Skip ? queryOptions.Skip.Value : 0;
var skipCount = 0;
var topCount = null != queryOptions.Top ?
Math.Min(queryOptions.Top.Value, Constants.ODATA_ENABLEQUERY_PAGESIZE + 1) :
(Constants.ODATA_ENABLEQUERY_PAGESIZE + 1);
// entitySet holds all entities that have passed the filter expression
var entitySet = new List<TEntity>();
var orderedEntitySetCount = 0;
do
{
// get the result set with filter and order applied
// for queries with top < pageSize we end up with a bad query
// as we potentially have re-query the database too often.
// Therefore we always query pageSize+1 records from the table and
// break the loop if we either have top records -or- pageSize+1 records
IQueryable<TEntity> orderedResultSet = queryOptions.ApplyOrderedTo<TEntity>(queryable, skipCount, Constants.ODATA_ENABLEQUERY_PAGESIZE + 1);
orderedEntitySetCount = 0;
foreach (TEntity entity in orderedResultSet)
{
// count returned entities (regardless of filter expression)
orderedEntitySetCount++;
var isEntityToBeAdded = filter(entity, context);
if(isEntityToBeAdded)
{
// apply the skip count
if(originalSkipCount > 0)
{
originalSkipCount;
}
else
{
// add entity to cache
CacheManager.Default.Add<TEntity>(entity);
// add entity to resultSet
entitySet.Add(entity);
}
}
// abort if page size (+1) is reached, +1 is necessary to determine if we need a next link
if (entitySet.Count >= Constants.ODATA_ENABLEQUERY_PAGESIZE + 1 || entitySet.Count >= topCount)
{
break;
}
}
// adjust skip count for next iteration (regardless of actually returned entities)
skipCount += orderedEntitySetCount;
}
// continue while
// we have returned entries AND
// we have not yet reached the specified topCount
while (0 < orderedEntitySetCount && topCount > entitySet.Count);
// decrease skip count as we are now off by one
skipCount;
// return list a IQueryable
return new PageableEntityFilterResult<TEntity>()
{
EntitySet = entitySet
,
SkipCount = skipCount
};
}

public class PageableEntityFilterContext
{
public AccessManager AccessManager;
}

public class PageableEntityFilterResult<TEntity>
where TEntity : BaseEntity
{
public IList<TEntity> EntitySet;
public int SkipCount;
}

public class ODataQueryOptionsExtensionsImpl
{
public IQueryable ApplyOrderedTo<TSource>(ODataQueryOptions<TSource> oDataQueryOptions, IQueryable<TSource> entitySet, ODataQuerySettings querySettings)
where TSource : BaseEntity
{
var topCount = querySettings.PageSize.HasValue ? querySettings.PageSize.Value : 0;
var result = ApplyOrderedTo<TSource>(oDataQueryOptions, entitySet, 0, topCount);
return result;
}
public IQueryable<TSource> ApplyOrderedTo<TSource>(ODataQueryOptions<TSource> oDataQueryOptions, IQueryable<TSource> entitySet, int skipCount, int topCount)
where TSource : BaseEntity
{
Contract.Requires(null != oDataQueryOptions);
Contract.Requires(null != entitySet);
Contract.Ensures(null != Contract.Result<IQueryable>());
var originalSkipCount = null != oDataQueryOptions.Skip ? oDataQueryOptions.Skip.Value : 0;
var originalTopCount = null != oDataQueryOptions.Top ? oDataQueryOptions.Top.Value : 0;
SetSkipOption(oDataQueryOptions, 0);
SetTopOption(oDataQueryOptions, topCount);
var orderByPropertyExtractor = new ODataOrderByPropertyExtractor();
IQueryable resultSet;
if (null != oDataQueryOptions.OrderBy && null != oDataQueryOptions.OrderBy.OrderByClause)
{
IOrderedQueryable<TSource> orderedQueryable;
// get first orderby clause
var orderByClauseIndex = 0;
var orderByPropertyName = orderByPropertyExtractor.GetPropertyName(oDataQueryOptions.OrderBy.RawValue, orderByClauseIndex);
var orderByPropertyType = orderByPropertyExtractor.GetPropertyType(oDataQueryOptions.OrderBy, orderByClauseIndex);
#region entitySet.OrderBy(orderByPropertyName)
if (oDataQueryOptions.OrderBy.OrderByClause.Direction == Microsoft.Data.OData.Query.OrderByDirection.Ascending)
{
if (typeof(DateTimeOffset) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, DateTimeOffset>(orderByPropertyName);
}
else if (typeof(long) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, long>(orderByPropertyName);
}
else if (typeof(string) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, string>(orderByPropertyName);
}
else if (typeof(int) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, int>(orderByPropertyName);
}
else if (typeof(Guid) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, Guid>(orderByPropertyName);
}
else if (typeof(DateTime) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderBy<TSource, DateTime>(orderByPropertyName);
}
else
{
Contract.Assert(null != orderByPropertyType, "Unsupported orderBy type");
orderedQueryable = null;
}
}
else
{
if (typeof(DateTimeOffset) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, DateTimeOffset>(orderByPropertyName);
}
else if (typeof(long) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, long>(orderByPropertyName);
}
else if (typeof(string) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, string>(orderByPropertyName);
}
else if (typeof(int) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, int>(orderByPropertyName);
}
else if (typeof(Guid) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, Guid>(orderByPropertyName);
}
else if (typeof(DateTime) == orderByPropertyType)
{
orderedQueryable = entitySet.OrderByDescending<TSource, DateTime>(orderByPropertyName);
}
else
{
Contract.Assert(null != orderByPropertyType, "Unsupported orderBy type");
orderedQueryable = null;
}
}
#endregion
// get subsequent orderby clauses
var thenBy = oDataQueryOptions.OrderBy.OrderByClause.ThenBy;
while (null != thenBy)
{
orderByClauseIndex++;
var thenByPropertyName = orderByPropertyExtractor.GetPropertyName(oDataQueryOptions.OrderBy.RawValue, orderByClauseIndex);
var thenByPropertyType = orderByPropertyExtractor.GetPropertyType(oDataQueryOptions.OrderBy, orderByClauseIndex);
#region orderedQueryable.OrderBy(thenByPropertyName);
if (thenBy.Direction == Microsoft.Data.OData.Query.OrderByDirection.Ascending)
{
if (typeof(DateTimeOffset) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, DateTimeOffset>(thenByPropertyName);
}
else if (typeof(long) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, long>(thenByPropertyName);
}
else if (typeof(string) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, string>(thenByPropertyName);
}
else if (typeof(int) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, int>(thenByPropertyName);
}
else if (typeof(Guid) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, Guid>(thenByPropertyName);
}
else if (typeof(DateTime) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderBy<TSource, DateTime>(thenByPropertyName);
}
else
{
Contract.Assert(null != thenByPropertyName, "Unsupported orderBy type");
orderedQueryable = null;
}
}
else
{
if (typeof(DateTimeOffset) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, DateTimeOffset>(thenByPropertyName);
}
else if (typeof(long) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, long>(thenByPropertyName);
}
else if (typeof(string) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, string>(thenByPropertyName);
}
else if (typeof(int) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, int>(thenByPropertyName);
}
else if (typeof(Guid) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, Guid>(thenByPropertyName);
}
else if (typeof(DateTime) == thenByPropertyType)
{
orderedQueryable = orderedQueryable.OrderByDescending<TSource, DateTime>(thenByPropertyName);
}
else
{
Contract.Assert(null != thenByPropertyName, "Unsupported orderBy type");
orderedQueryable = null;
}
}
#endregion
// get next orderby clause
thenBy = thenBy.ThenBy;
}
// execute ordered query
resultSet = oDataQueryOptions.ApplyTo
(
orderedQueryable
.Skip(skipCount)
.Take(topCount)
);
}
else
{
// append default orderby and execute query
resultSet = oDataQueryOptions.ApplyTo
(entitySet
.OrderBy(e => e.Id)
.Skip(skipCount)
.Take(topCount)
);
}
SetSkipOption(oDataQueryOptions, originalSkipCount);
SetTopOption(oDataQueryOptions, originalTopCount);
return resultSet.Cast<TSource>();
}
public bool SetSkipOption<TSource>(ODataQueryOptions<TSource> oDataQueryOptions, int value)
where TSource : BaseEntity
{
Contract.Requires(null != oDataQueryOptions);
Contract.Requires(0 <= value);
if (null == oDataQueryOptions.Skip)
{
return false;
}
if(oDataQueryOptions.Skip.Value == value)
{
return true;
}
var fieldInfoRawValue = GetBackingFieldInfo(oDataQueryOptions.Skip, "RawValue");
fieldInfoRawValue.SetValue(oDataQueryOptions.Skip, value.ToString());
var runtimeFieldInfoValue = oDataQueryOptions.Skip.GetType().GetRuntimeFields().FirstOrDefault(e => e.Name == "_value");
runtimeFieldInfoValue.SetValue(oDataQueryOptions.Skip, value);
Contract.Assert(oDataQueryOptions.Skip.Value == value);
Contract.Assert(oDataQueryOptions.Skip.RawValue == value.ToString());
return true;
}
public bool SetTopOption<TSource>(ODataQueryOptions<TSource> oDataQueryOptions, int value)
where TSource : BaseEntity
{
Contract.Requires(null != oDataQueryOptions);
Contract.Requires(0 <= value);
if (null == oDataQueryOptions.Top)
{
return false;
}
if (oDataQueryOptions.Top.Value == value)
{
return true;
}
var fieldInfoRawValue = GetBackingFieldInfo(oDataQueryOptions.Top, "RawValue");
fieldInfoRawValue.SetValue(oDataQueryOptions.Top, value.ToString());
var runtimeFieldInfoValue = oDataQueryOptions.Top.GetType().GetRuntimeFields().FirstOrDefault(e => e.Name == "_value");
runtimeFieldInfoValue.SetValue(oDataQueryOptions.Top, value);
Contract.Assert(oDataQueryOptions.Top.Value == value);
Contract.Assert(oDataQueryOptions.Top.RawValue == value.ToString());
return true;
}
public int GetTakeCount<TSource>(ODataQueryOptions<TSource> queryOptions, int pageSize = Constants.ODATA_ENABLEQUERY_PAGESIZE)
where TSource : BaseEntity
{
Contract.Requires(null != queryOptions);
var count = null != queryOptions.Top ? Math.Min(queryOptions.Top.Value, pageSize) : pageSize;
return ++count;
}
private string GetBackingFieldName(string propertyName)
{
Contract.Ensures(!string.IsNullOrWhiteSpace(Contract.Result<string>()));
var result = string.Format("<{0}>k__BackingField", propertyName);
return result;
}
private FieldInfo GetBackingFieldInfo(object obj, string propertyName)
{
Contract.Ensures(null != Contract.Result<FieldInfo>());
var fieldName = GetBackingFieldName(propertyName);
var type = obj.GetType();
var backingFieldInfo = type.GetField(fieldName, BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.FlattenHierarchy);
return backingFieldInfo;
}
}

public class ODataControllerExtensionsImpl
{
public Uri SetNextLink<TEntity>(ODataController controller, IList<TEntity> entitySet, int pageSize, int skipCount)
where TEntity : BaseEntity
{
Contract.Requires(null != controller);
Contract.Requires(null != entitySet);
Contract.Requires(0 < pageSize);
var oDataProperties = controller.Request.ODataProperties();
if(0 >= entitySet.Count)
{
return oDataProperties.NextLink;
}
if (pageSize >= entitySet.Count)
{
return oDataProperties.NextLink;
}
Contract.Assert(0 <= skipCount);
// DFTODO – currently we have an issue when the resultSet.Count is 1 AND the pagesoze is 1
Contract.Assert(1 <= entitySet.Count);
entitySet.RemoveAt(entitySet.Count 1);
var absoluteUri = controller.Request.RequestUri.AbsoluteUri;
var nextLinkBase = Regex.Replace(absoluteUri, "\\$skip=\\d+", "").TrimEnd('&');
var nextLinkUri = string.Format("{0}&$skip={1}", nextLinkBase, skipCount).Replace("&&", "&");
oDataProperties.NextLink = new Uri(nextLinkUri);
return oDataProperties.NextLink;
}
}

/**
* 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.
*/

view raw
LICENSE
hosted with ❤ by GitHub

While implementing this method I ran into a couple of problems:

  1. ODataQueryOptions.Skip and ODataQueryOptions.Top are readonly properties with Getters only resulting in having these options applied multiple times when re-querying the database table
  2. ODataQueryOptions does not have a constructor that can be used to manually initialise query options with a custom Skip or Top
  3. SkipOptions and TopOptions itself are readonly as well
  4. Secifying an SQL OFFSET requires to have an ORDER BY clause as well, meaning that we have to specify an orderby if there is none specified by the caller
  5. ODataQueryOptions.ApplyTo() incorrectly applies its $orderby clause to the outer SQL statement only resulting in a wrongly ordered result set
  6. Converting the OrderByClause expression into an IQueryable compatible expression does not seem to be possible
  7. ODataQueryOptions.ApplyTo() works with typeless IQueryable that must be cast to a specific entity to be returned by OData

The Details

To be honest this approach might come as some kind of unusual solution as it uses some internals that are not for the faint of heart:

  1. We write ‘readonly’ properties via reflection and its internal backing fields, see SetTopOption and SetSkipOption
  2. We have to manually recreate an OrderByClause by analysing all cascaded clauses, ApplyOrderedTo
  3. We have to create a custom ‘nextlink’ via an ODataController extension method

Though this solution does have its downsides, in our opinion its advantages outweigh its disadvantages. The original approach with a full table scan does just not perform. In addition we created a very flexible solution that acts as a pageable entity filter that can perform just about anything deemed feasible.

Note1: in case you wonder why we use implementation classes instead of static extension classes: we think for testing purposes it makes more sense to use a facade class

Note2: the PageableEntityFilterContext is a convenience for the filter method allowing the filter to create commonly used classes once and pass them to the actual filter (AccessManager in our example).

Note3: When not manually re-creating the OrderByClause, but just trying to use ApplyTo on an IQueryable with Skip() you receive an System.NotSupportedException in OrderByLifter.PassthroughOrderByLifter.Skip.

>> “The method ‘Skip’ is only supported for sorted input in LINQ to Entities. The method ‘OrderBy’ must be called before the method ‘Skip’.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: