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:
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:
Just to show the end result, here's the JSON result in the browser tools with the executed SQL query below:
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.