Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace. - Jon Galloway

Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

Someone asked me via Twitter if ASP.NET Web API queries are filtered at the database level, or if ASP.NET Web API queries the entire result set and then filters in code. Good question. I was pretty sure I knew the answer (below) but since everything in ASP.NET Web API changes so fast and they just released some big OData support updates, I wanted to make sure before answering.

How would you do that?

Option 1: Read the code

Since ASP.NET Web API and OData are both open source code, you can read through the code. A good guess at where to start would be the [Queryable] attribute:

http://aspnetwebstack.codeplex.com/SourceControl/changeset/view/3dad0922b324#src/System.Web.Http.OData/QueryableAttribute.cs ExecuteQuery

private IQueryable ExecuteQuery(IEnumerable query, HttpRequestMessage request, HttpConfiguration configuration, HttpActionDescriptor actionDescriptor) 
{ 
    Type originalQueryType = query.GetType(); 
    Type entityClrType = TypeHelper.GetImplementedIEnumerableType(originalQueryType);

    if (entityClrType == null) 
    { 
        // The element type cannot be determined because the type of the content 
        // is not IEnumerable<T> or IQueryable<T>. 
        throw Error.InvalidOperation( 
            SRResources.FailedToRetrieveTypeToBuildEdmModel, 
            this.GetType().Name, 
            actionDescriptor.ActionName, 
            actionDescriptor.ControllerDescriptor.ControllerName, 
            originalQueryType.FullName); 
    }

    ODataQueryContext queryContext = CreateQueryContext(entityClrType, configuration, actionDescriptor); 
    ODataQueryOptions queryOptions = new ODataQueryOptions(queryContext, request); 
    ValidateQuery(request, queryOptions);

    // apply the query 
    IQueryable queryable = query as IQueryable; 
    if (queryable == null) 
    { 
        queryable = query.AsQueryable(); 
    }

    ODataQuerySettings querySettings = new ODataQuerySettings 
    { 
        EnsureStableOrdering = EnsureStableOrdering, 
        HandleNullPropagation = HandleNullPropagation, 
        MaxAnyAllExpressionDepth = MaxAnyAllExpressionDepth, 
        PageSize = _pageSize 
    };

    return queryOptions.ApplyTo(queryable, querySettings); 
}

Hmm. That's not manually filtering data, but the query itself is obviously a few levels deeper. Oh, let's look at the call stack:

System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteReader(System.Data.CommandBehavior behavior = {unknown}, string method = {unknown})    
System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(System.Data.CommandBehavior behavior = {unknown})    
System.Data.dll!System.Data.Common.DbCommand.ExecuteReader(System.Data.CommandBehavior behavior = {unknown})    
EntityFramework.dll!System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(System.Data.Entity.Core.EntityClient.EntityCommand entityCommand = {unknown}, System.Data.CommandBehavior behavior = {unknown})    
EntityFramework.dll!System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute(System.Data.Entity.Core.Objects.ObjectContext context = {unknown}, System.Data.Entity.Core.Objects.ObjectParameterCollection parameterValues = {unknown})    
EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(System.String (error) = Internal Error in IntelliTrace)    
EntityFramework.dll!System.Data.Entity.Core.Objects.ObjectQuery`1.AnonymousMethod()    
mscorlib.dll!System.Lazy`1.CreateValue()    
mscorlib.dll!System.Lazy`1.LazyInitValue()    
mscorlib.dll!Get System.Lazy`1.Value()    
EntityFramework.dll!System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()    
mscorlib.dll!System.Collections.Generic.List`1..ctor(System.Collections.Generic.IEnumerable collection = {unknown})    
System.Core.dll!System.Linq.Enumerable.ToList(System.Collections.Generic.IEnumerable source = {unknown})    
Newtonsoft.Json.dll!Newtonsoft.Json.Serialization.JsonArrayContract.CreateWrapper(object list = {unknown})    
Newtonsoft.Json.dll!Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeValue(Newtonsoft.Json.JsonWriter writer = {unknown}, object value = {unknown}, Newtonsoft.Json.Serialization.JsonContract valueContract = {unknown}, Newtonsoft.Json.Serialization.JsonProperty member = {unknown}, Newtonsoft.Json.Serialization.JsonContainerContract containerContract = {unknown}, Newtonsoft.Json.Serialization.JsonProperty containerProperty = {unknown})    
Newtonsoft.Json.dll!Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(Newtonsoft.Json.JsonWriter jsonWriter = {unknown}, object value = {unknown})    
Newtonsoft.Json.dll!Newtonsoft.Json.JsonSerializer.SerializeInternal(Newtonsoft.Json.JsonWriter jsonWriter = {unknown}, object value = {unknown})    
Newtonsoft.Json.dll!Newtonsoft.Json.JsonSerializer.Serialize(Newtonsoft.Json.JsonWriter jsonWriter = {unknown}, object value = {unknown})    
System.Net.Http.Formatting.dll!<>c__DisplayClassd.<WriteToStreamAsync>b__c()    
System.Net.Http.Formatting.dll!System.Threading.Tasks.TaskHelpers.RunSynchronously(System.Action action = {unknown}, System.Threading.CancellationToken token = {unknown})    
System.Net.Http.Formatting.dll!System.Net.Http.Formatting.JsonMediaTypeFormatter.WriteToStreamAsync(System.Type type = {unknown}, object value = {unknown}, System.IO.Stream writeStream = {unknown}, System.Net.Http.HttpContent content = {unknown}, System.Net.TransportContext transportContext = {unknown})    
System.Net.Http.Formatting.dll!System.Net.Http.ObjectContent.SerializeToStreamAsync(System.IO.Stream stream = {unknown}, System.Net.TransportContext context = {unknown})    
System.Net.Http.dll!System.Net.Http.HttpContent.CopyToAsync(System.IO.Stream stream = {unknown}, System.Net.TransportContext context = {unknown})    
System.Net.Http.dll!System.Net.Http.HttpContent.CopyToAsync(System.IO.Stream stream = {unknown})    
System.Web.Http.WebHost.dll!System.Web.Http.WebHost.HttpControllerHandler.WriteBufferedResponseContentAsync(System.Web.HttpContextBase httpContextBase = {unknown}, System.Net.Http.HttpContent responseContent = {unknown}, System.Net.Http.HttpRequestMessage request = {unknown})    
System.Web.Http.WebHost.dll!System.Web.Http.WebHost.HttpControllerHandler.WriteResponseContentAsync(System.Web.HttpContextBase httpContextBase = {unknown}, System.Net.Http.HttpResponseMessage response = {unknown}, System.Net.Http.HttpRequestMessage request = {unknown})    
System.Web.Http.WebHost.dll!System.Web.Http.WebHost.HttpControllerHandler.ConvertResponse(System.Web.HttpContextBase httpContextBase = {unknown}, System.Net.Http.HttpResponseMessage response = {unknown}, System.Net.Http.HttpRequestMessage request = {unknown})    
System.Web.Http.WebHost.dll!<>c__DisplayClass3.AnonymousMethod(System.Net.Http.HttpResponseMessage response = {unknown})    
System.Web.Http.WebHost.dll!<>c__DisplayClass3e`1.AnonymousMethod(System.Threading.Tasks.Task t = {unknown})    
System.Web.Http.WebHost.dll!System.Threading.Tasks.TaskHelpersExtensions.ThenImpl(TTask task = {unknown}, System.Func continuation = {unknown}, System.Threading.CancellationToken cancellationToken = {unknown}, bool runSynchronously = {unknown})    
System.Web.Http.WebHost.dll!System.Threading.Tasks.TaskHelpersExtensions.Then(System.Threading.Tasks.Task task = {unknown}, System.Func continuation = {unknown}, System.Threading.CancellationToken token = {unknown}, bool runSynchronously = {unknown})    
System.Web.Http.WebHost.dll!System.Web.Http.WebHost.HttpControllerHandler.BeginProcessRequest(System.Web.HttpContextBase httpContextBase = {unknown}, System.AsyncCallback callback = {unknown}, object state = {unknown})    
System.Web.Http.WebHost.dll!System.Web.Http.WebHost.HttpControllerHandler.System.Web.IHttpAsyncHandler.BeginProcessRequest(System.Web.HttpContext httpContext = {unknown}, System.AsyncCallback callback = {unknown}, object state = {unknown})    
System.Web.dll!CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()    
System.Web.dll!System.Web.HttpApplication.ExecuteStep(IExecutionStep step = {unknown}, ref bool completedSynchronously = {unknown})    
System.Web.dll!PipelineStepManager.ResumeSteps(System.Exception error = {unknown})    
System.Web.dll!System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext context = {unknown}, System.AsyncCallback cb = {unknown})    
System.Web.dll!System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest wr = {unknown}, System.Web.HttpContext context = {unknown})    
System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})    
System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})    
System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})    
System.Web.dll!System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(System.IntPtr rootedObjectsPointer = {unknown}, System.IntPtr nativeRequestContext = {unknown}, System.IntPtr moduleData = {unknown}, int flags = {unknown})

Okay, it's got to be in there somewhere. But looking for it seems like hard work. On to Option 2!

Option 2: Use IntelliTrace

If you've got Visual Studio Ultimate, this is one of those times you can enjoy your Ultimate Elite Preferred Coder status. View IntelliTrace Events using either Debug / IntelliTrace Events / View IntelliTrace Events or using keyboard shortcuts:

  • CTRL+ALT+Y, F <- Nice that there's a shortcut, but you're not going to remember this
  • CTRL+Q (Quick Launch) +  type "intellitrace events"  + hit Enter

Then scroll down the events list to see the ADO.NET calls and click on one that looks promising:

2012-12-22_18h50_46

Just to show the end result, here's the JSON result in the browser tools with the executed SQL query below:

ASP.NET Web API - OData Filtering

That query in text is:

SELECT 
[Extent1].[AlbumId] AS [AlbumId], 
[Extent1].[GenreId] AS [GenreId], 
[Extent1].[ArtistId] AS [ArtistId], 
[Extent1].[Title] AS [Title], 
[Extent1].[Price] AS [Price], 
[Extent1].[AlbumArtUrl] AS [AlbumArtUrl], 
[Extent2].[GenreId] AS [GenreId1], 
[Extent2].[Name] AS [Name], 
[Extent2].[Description] AS [Description], 
[Extent3].[ArtistId] AS [ArtistId1], 
[Extent3].[Name] AS [Name1] 
FROM   [dbo].[Albums] AS [Extent1] 
INNER JOIN [dbo].[Genres] AS [Extent2] ON [Extent1].[GenreId] = [Extent2].[GenreId] 
INNER JOIN [dbo].[Artists] AS [Extent3] ON [Extent1].[ArtistId] = [Extent3].[ArtistId] 
WHERE N'Kind of Blue' = [Extent1].[Title]

Final Answer: Yes

As long as your data provider supports deferred queries and you don't force evaluation by calling something like .ToList(), the query will not be evaluated until the OData filters are applied, and they'll be handled at the database level.

Published Saturday, December 22, 2012 7:12 PM by Jon Galloway

Comments

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

What about select top? I believe that is filtered only on the server. Also, you could've set a breakpoint on the query to see the resultant sql. Nevertheless, thanks for the effort and the post.

Sunday, December 23, 2012 12:13 AM by Dan

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

どうしても忘れられず、探偵を雇って探してもらおうと思ったこともあるほどの彼。独身か分からないけど……。

Monday, December 24, 2012 4:05 AM by luxurybrandwonqhrho@mail.com

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

リシュモン傘下英倫贅沢な紳士服や皮具ブランドダンヒル(Alfred近日Dunhill)を任命したと発表しEraldo Polettoを最高経営責任者は、2012年11月5日から発効する ヴィトン モノグラム ビニール コピー。

Tuesday, December 25, 2012 1:10 AM by luxurybrandyimmwh@mail.com

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

A faster and far easier way to find out would be using the SQL Server Profiler.

It tells you which queries are fired on the database.

Wednesday, December 26, 2012 4:50 PM by Arnoud van Bers

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

Thanks Jon - and by the way, as a Trumpet player, I love the selection of Music!!

Thursday, December 27, 2012 11:26 AM by Paul Johnson

# re: Does ASP.NET Web API + OData filter at the database level? Let's ask IntelliTrace.

Jon, thanks for the response. It was me who asked the question. Although, you answered me through Twitter, I (and probably many others) appreciate the detailed response. Thanks!

Wednesday, January 2, 2013 10:01 AM by Jonas Stawski