ADO.NET (WCF) Data Services Query Interceptor Hangs IIS

Question

I have an ADO.NET Data Service that's supposed to provide read-only access to a somewhat complex database.

Logically I have table-per-type (TPT) inheritance in my data model but the EDM doesn't implement inheritance. (Limitation of Data Services and navigation properties on derived types. STILL not fixed in .NET 4!) I can query my EDM directly (using a separate project) using a copy of the query I'm trying to run against the web service, results are returned within 10 seconds. Disabling the query interceptors I'm able to make the same query against the web service, results are returned similarly quickly. I can enable some of the query interceptors and the results are returned slowly, up to a minute or so later. Alternatively, I can enable all the query interceptors, expand less of the properties on the main object I'm querying, and results are returned in a similar period of time. (I've increased some of the timeout periods)

Up til this point Sql Profiler indicates the slow-down is the database. (That's a post for a different day) But when I enable all my query interceptors and expand all the properties I'd like to have the IIS worker process pegs the CPU for 20 minutes and a query is never even made against the database, ie the query never makes it past the web server. This implies to me that yes, my implementation probably sucks but regardless the Data Services "tier" is having an issue it shouldn't. WCF tracing didn't reveal anything interesting to my untrained eye.

Details:

• Data model: Agent->Person->Student
• Student has a collection of referrals
• Students and referrals are private, queries against the web service should only return "your" students and referrals. This means Person and Agent need to be filtered too. Other entities (Agent->Organization->School) can be accessed by anyone who has authenticated.
• The existing security model is poorly suited to perform this type of filtering for this type of data access, the query interceptors are complicated and cause EF to generate some entertaining sql queries.

Sample Interceptor

[QueryInterceptor("Agents")]
    public Expression<Func<Agent, Boolean>> OnQueryAgents()
    {
        //Agent is a Person(1), Educator(2), Student(3), or Other Person(13); allow if scope permissions exist
    return ag =>
               (ag.AgentType.AgentTypeId == 1 || ag.AgentType.AgentTypeId == 2 || ag.AgentType.AgentTypeId == 3 || ag.AgentType.AgentTypeId == 13) &&                
            ag.Person.OrganizationPersons.Count<OrganizationPerson>(op =>
                op.Organization.ScopePermissions.Any<ScopePermission>
                    (p => p.ApplicationRoleAccount.Account.UserName == HttpContext.Current.User.Identity.Name && p.ApplicationRoleAccount.Application.ApplicationId == 124) ||
                op.Organization.HierarchyDescendents.Any<OrganizationsHierarchy>(oh => oh.AncestorOrganization.ScopePermissions.Any<ScopePermission>
                    (p => p.ApplicationRoleAccount.Account.UserName == HttpContext.Current.User.Identity.Name && p.ApplicationRoleAccount.Application.ApplicationId == 124))) > 0; 
    }

The query interceptors for Person, Student, Referral are all very similar, ie they traverse multiple same/similar tables to look for ScopePermissions as above.

Sample Query

This sample query is just that, a sample, intended to illustrate to third parties how to access the data using the provided web service. I realize a production query wouldn't have that many expands. (But also remember that to get the entire object in the OOP sense I need an Agent, Person, and Student row.)

var referrals =
            (from r in service.Referrals
                 .Expand("Organization/ParentOrganization")              
                 .Expand("Educator/Person/Agent")
                 .Expand("Student/Person/Agent")
                 .Expand("Student")
                 .Expand("Grade")
                 .Expand("ProblemBehavior")
                 .Expand("Location")
                 .Expand("Motivation")
                 .Expand("AdminDecision")
                 .Expand("OthersInvolved")
             where
                 r.DateCreated >= coupledays &&
                 r.DateDeleted == null
             select r);

Any suggestions or tips would be greatly associated, for fixing my current implementation or in developing a new one, with the caveat that existing database logic can't be changed (though I can add to it) and that ultimately I need to expose a large portion of the database via a web service that limits data access to the data authorized for, for the purpose of data integration with multiple outside parties. These outside parties will be performing regular batch jobs to import our data into their database/data-warehouse.

THANK YOU!!!

UPDATE: Posted this issue on MSDN, received similar feedback. http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/1ccfc96c-dd35-4879-b36b-57e915d5e02f/

Answer 1

I'm just guessing here... but doing that many expands is almost never a good idea. The query will undoubtedly expand into some pretty awful SQL, that could easily cause timeouts.

Add TPT to the equation and things only get worse :(

Alex