search

OData / WebApi

OData Web API: A server library built upon ODataLib and WebApi
https://docs.microsoft.com/odata
Other
855 stars 473 forks source link

$query POST method fails for some filter expressions when odata service accessed through a VPN #2513

Open cam-m opened 3 years ago

cam-m commented 3 years ago

Short summary (3-5 sentences) describing the issue. This one is weird.

Some odata expressions that work fine in a GET request, cause the request to sit pending (I.e. no sql being executed) indefinitely when submitted in a POST request.... but! Only when accesing the odata endpoint through a VPN.

Examples of expressions:

[] eq []
PropertyId in []
25 eq null

E.g. 

$apply=filter([] eq [] or Property in [])

If I remove statements like [] eq [] and replace them with a literal logical equivalent like true or false, the $query POST method works fine.

Assemblies affected

Microsoft.AspNet.OData 7.5.1 EntityFramework 6.4.0

Reproduce steps

Submit a query that contains $apply=filter([] eq [] or Property in []) via GET and via POST body $query methods and compare sql generated.

Expected result

Queries can can be submitted as $query POST request or GET requests and the result will be identical.

Actual result

Some filter expressions in $apply cause the $query POST method to fail before the sql query is generated.

Additional detail

I realise the expressions I have issue with appear superfluous.

However I'm using aliases in a way that lets users set filters and allows expressions like '(@SomeList eq [] or Item in @SomeList) that handles the case where the user deselects all options in a list to mean 'all'. I'm also resolving the alias's in the client directly instead of tacking them onto the query.

So this:

$apply=filter(@UserList eq [] or Property in @UserList)

is resolved to this:

$apply=filter([] eq [] or Property in [])

When a user makes no selection, or this:

$apply=filter([1,2,3] eq [] or Property in [1,2,3])

I think volume of data is somehow at play here too since I can't replicate this in a test data base with limited real data.

These symptoms point to GET method and the $query POST method possibly using a different method of parsing the odata query.

Relates to this pull request

Optional, details of the root cause if known. Delete this section if you have no additional details to add.

I've tried this under two different VPN solutions (F5 and some random thing my company uses) and it occurs in both.

marabooy commented 3 years ago

@cam-m could you kindly share the model you are using and a section of your startup file. I am trying a repro using open-vpn and not sure if it will give the same error as it implements ssl and not ipsec.

cam-m commented 3 years ago

Hi @marabooy,

thanks for looking into it for me. Is there a way I can share the model details with you privately?

The startup code is pretty generic. I've trimmed out the model bits.

Startup

               // init a defaultLogger

                Log.Logger = defaultLogger;

                app.SetLoggerFactory(new SerilogOwinFactory(defaultLogger));

                app.Use<ExceptionHandlerMiddleware>(defaultLogger);

                HttpConfiguration webApiConfig = new HttpConfiguration();
                webApiConfig.MapHttpAttributeRoutes();
                HttpServer server = new HttpServer(webApiConfig);
                WebApiConfig.Register(webApiConfig, server, defaultLogger);
                app.UseWebApi(server);

Register

public static void Register(HttpConfiguration config, HttpServer server, ILogger rootLogger)
        {
            // set up of auth filters and logging commented out

            ODataConventionModelBuilder builder = new ODataConventionModelBuilder
            {
                Namespace = "QMV.Investigate.EntityModel"
            };

            #region "Tables"

            // build up the odata entity sets complex types and functions.

            config.Expand().Select().MaxTop(50).Filter().SkipToken().Count().OrderBy().AddODataQueryFilter();

            var model = builder.GetEdmModel() as EdmModel;

            config.MapODataServiceRoute("odata", "odata/v1", model, new DefaultODataBatchHandler(server));

            logger.Information("Web API configuration completed");
        }
marabooy commented 3 years ago

@cam-m there won't be a need to share the whole model, you could share a small sample or edit a portion of the sample https://services.odata.org/v4/(S(1hthk2rtxtoaogoaucumcdia))/trippinservicerw/$metadata to match one of the properties that is being filtered. What I'm looking for is a single entity definition and related entities.

marabooy commented 3 years ago

Also could you try the request under $batch and let me know if everything works?

cam-m commented 3 years ago

Thanks @marabooy

Yes $batch works perfectly and will need to be our workaround if we can't resolve the $query POST issues.

The model is quite large, so heres just the parts that pertain to EntitySet: IncidentBreakdowns

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
    <edmx:DataServices>
        <Schema Namespace="EntityModel" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            <EntityType Name="Incident">
                <Key>
                    <PropertyRef Name="IncidentId" />
                </Key>
                <Property Name="IncidentId" Type="Edm.Int32" Nullable="false" />
                <Property Name="IssueId" Type="Edm.Int32" />
                <Property Name="DisplayCode" Type="Edm.String" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="StatusType" Type="Edm.Byte" Nullable="false" />
                <Property Name="Priority" Type="Edm.Decimal" />
                <Property Name="AssignedTo" Type="Edm.String" />
                <Property Name="Description" Type="Edm.String" />
                <Property Name="IsAdvancedClassification" Type="Edm.Boolean" Nullable="false" />
                <Property Name="ApplicationEntityKeyValue" Type="Edm.String" />
                <Property Name="LastExecutionResultId" Type="Edm.Int32" />
                <Property Name="CreatedBy" Type="Edm.String" />
                <Property Name="CreatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="UpdatedBy" Type="Edm.String" />
                <Property Name="UpdatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="NextActionDate" Type="Edm.DateTimeOffset" />
                <Property Name="ApplicationId" Type="Edm.Int32" />
                <Property Name="EnvironmentId" Type="Edm.Int32" />
                <Property Name="ApplicationEntityId" Type="Edm.Int32" />
                <Property Name="SeverityTypeReferenceId" Type="Edm.Int32" />
                <NavigationProperty Name="Application" Type="EntityModel.Application">
                    <ReferentialConstraint Property="ApplicationId" ReferencedProperty="ApplicationId" />
                </NavigationProperty>
                <NavigationProperty Name="ApplicationEntity" Type="EntityModel.ApplicationEntity">
                    <ReferentialConstraint Property="ApplicationEntityId" ReferencedProperty="ApplicationEntityId" />
                </NavigationProperty>
                <NavigationProperty Name="Environment" Type="EntityModel.Environment">
                    <ReferentialConstraint Property="EnvironmentId" ReferencedProperty="EnvironmentId" />
                </NavigationProperty>
                <NavigationProperty Name="ExecutionResult" Type="EntityModel.ExecutionResult" />
                <NavigationProperty Name="Issue" Type="EntityModel.Issue">
                    <ReferentialConstraint Property="IssueId" ReferencedProperty="IssueId" />
                </NavigationProperty>
                <NavigationProperty Name="ResolveStatusTypeReference" Type="EntityModel.ResolveStatusTypeReference" />
                <NavigationProperty Name="IncidentReportPropertyLink" Type="Collection(EntityModel.IncidentReportPropertyLink)" />
                <NavigationProperty Name="IncidentExecutionResultLink" Type="Collection(EntityModel.IncidentExecutionResultLink)" />
                <NavigationProperty Name="IncidentScenarioLink" Type="Collection(EntityModel.IncidentScenarioLink)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="EventEntityLinkExceptionTypeLink" Type="Collection(EntityModel.EventEntityLinkExceptionTypeLink)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="SeverityTypeReference" Type="EntityModel.SeverityTypeReference">
                    <ReferentialConstraint Property="SeverityTypeReferenceId" ReferencedProperty="SeverityTypeReferenceId" />
                </NavigationProperty>
            </EntityType>
            <EntityType Name="FunctionalArea">
                <Key>
                    <PropertyRef Name="FunctionalAreaId" />
                </Key>
                <Property Name="FunctionalAreaId" Type="Edm.Int32" Nullable="false" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="Active" Type="Edm.Boolean" Nullable="false" />
                <NavigationProperty Name="Rule" Type="Collection(EntityModel.Rule)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="ExecutedScriptByResolutionType" Type="Collection(EntityModel.ExecutedScriptByResolutionType)" />
                <NavigationProperty Name="ExecutedScriptBySeverityType" Type="Collection(EntityModel.ExecutedScriptBySeverityType)" />
                <NavigationProperty Name="RuleLatestExecution" Type="Collection(EntityModel.RuleLatestExecution)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptByResolutionTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptByResolutionTypeDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptBySeverityTypeDateTimeline)" />
            </EntityType>
            <EntityType Name="ApplicationEntity">
                <Key>
                    <PropertyRef Name="ApplicationEntityId" />
                </Key>
                <Property Name="ApplicationEntityId" Type="Edm.Int32" Nullable="false" />
                <Property Name="ApplicationId" Type="Edm.Int32" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="DefaultApplicationEntityId" Type="Edm.Int32" />
                <Property Name="DisplayName" Type="Edm.String" />
                <NavigationProperty Name="Application" Type="EntityModel.Application">
                    <ReferentialConstraint Property="ApplicationId" ReferencedProperty="ApplicationId" />
                </NavigationProperty>
                <NavigationProperty Name="ApplicationEntityKey" Type="Collection(EntityModel.ApplicationEntityKey)" />
                <NavigationProperty Name="Incident" Type="Collection(EntityModel.Incident)" />
                <NavigationProperty Name="Issue" Type="Collection(EntityModel.Issue)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="ExecutedScriptByResolutionType" Type="Collection(EntityModel.ExecutedScriptByResolutionType)" />
                <NavigationProperty Name="ExecutedScriptBySeverityType" Type="Collection(EntityModel.ExecutedScriptBySeverityType)" />
                <NavigationProperty Name="RuleLatestExecution" Type="Collection(EntityModel.RuleLatestExecution)" />
                <NavigationProperty Name="DefaultApplicationEntity" Type="EntityModel.DefaultApplicationEntity">
                    <ReferentialConstraint Property="DefaultApplicationEntityId" ReferencedProperty="DefaultApplicationEntityId" />
                </NavigationProperty>
                <NavigationProperty Name="Entity" Type="Collection(EntityModel.Entity)" />
                <NavigationProperty Name="ExecutedEventScript" Type="Collection(EntityModel.ExecutedEventScript)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptByResolutionTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptByResolutionTypeDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptBySeverityTypeDateTimeline)" />
                <NavigationProperty Name="EntityResultReference" Type="Collection(EntityModel.EntityResultReference)" />
            </EntityType>
            <EntityType Name="Environment">
                <Key>
                    <PropertyRef Name="EnvironmentId" />
                </Key>
                <Property Name="EnvironmentId" Type="Edm.Int32" Nullable="false" />
                <Property Name="ApplicationId" Type="Edm.Int32" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="ConnectionString" Type="Edm.String" />
                <Property Name="DatabaseName" Type="Edm.String" />
                <Property Name="ExceptionPassword" Type="Edm.String" />
                <Property Name="WebApiEndpoint" Type="Edm.String" />
                <Property Name="EventRemediationStatusHistoryReport" Type="Collection(EntityModel.EventRemediationStatusHistoryReport)" />
                <NavigationProperty Name="Application" Type="EntityModel.Application">
                    <ReferentialConstraint Property="ApplicationId" ReferencedProperty="ApplicationId" />
                </NavigationProperty>
                <NavigationProperty Name="EnvironmentApiHeaderValue" Type="Collection(EntityModel.EnvironmentApiHeaderValue)" />
                <NavigationProperty Name="Incident" Type="Collection(EntityModel.Incident)" />
                <NavigationProperty Name="Issue" Type="Collection(EntityModel.Issue)" />
                <NavigationProperty Name="ScheduleEnvironmentLink" Type="Collection(EntityModel.ScheduleEnvironmentLink)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="ExecutedScriptByResolutionType" Type="Collection(EntityModel.ExecutedScriptByResolutionType)" />
                <NavigationProperty Name="ExecutedScriptBySeverityType" Type="Collection(EntityModel.ExecutedScriptBySeverityType)" />
                <NavigationProperty Name="RuleLatestExecution" Type="Collection(EntityModel.RuleLatestExecution)" />
                <NavigationProperty Name="Entity" Type="Collection(EntityModel.Entity)" />
                <NavigationProperty Name="ExecutedEventScript" Type="Collection(EntityModel.ExecutedEventScript)" />
                <NavigationProperty Name="ExecutedScript" Type="Collection(EntityModel.ExecutedScript)" />
                <NavigationProperty Name="RemediationPayment" Type="Collection(EntityModel.RemediationPayment)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptByResolutionTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptByResolutionTypeDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptBySeverityTypeDateTimeline)" />
                <NavigationProperty Name="RpcExecutionHistory" Type="Collection(EntityModel.RpcExecutionHistory)" />
                <NavigationProperty Name="ResolutionReportDetail" Type="Collection(EntityModel.ResolutionReportDetail)" />
                <NavigationProperty Name="EnvironmentScriptParameter" Type="Collection(EntityModel.EnvironmentScriptParameter)" />
                <NavigationProperty Name="RuleDefaultAssignedTo" Type="Collection(EntityModel.RuleDefaultAssignedTo)" />
            </EntityType>
            <EntityType Name="Rule">
                <Key>
                    <PropertyRef Name="RuleId" />
                </Key>
                <Property Name="RuleId" Type="Edm.Int32" Nullable="false" />
                <Property Name="FunctionalAreaId" Type="Edm.Int32" />
                <Property Name="DisplayCode" Type="Edm.String" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="StatusType" Type="Edm.Byte" Nullable="false" />
                <Property Name="Synopsis" Type="Edm.String" />
                <Property Name="CreatedBy" Type="Edm.String" />
                <Property Name="CreatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="UpdatedBy" Type="Edm.String" />
                <Property Name="UpdatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="IsIssue" Type="Edm.Boolean" Nullable="false" />
                <Property Name="AssignedTo" Type="Edm.String" />
                <Property Name="EmailNotification" Type="Edm.Boolean" Nullable="false" />
                <Property Name="IncludeMasterScript" Type="Edm.Boolean" Nullable="false" />
                <NavigationProperty Name="FunctionalArea" Type="EntityModel.FunctionalArea">
                    <ReferentialConstraint Property="FunctionalAreaId" ReferencedProperty="FunctionalAreaId" />
                </NavigationProperty>
                <NavigationProperty Name="Issue" Type="Collection(EntityModel.Issue)" />
                <NavigationProperty Name="RuleDetail" Type="Collection(EntityModel.RuleDetail)" />
                <NavigationProperty Name="RuleReportPropertyLink" Type="Collection(EntityModel.RuleReportPropertyLink)" />
                <NavigationProperty Name="Scenario" Type="Collection(EntityModel.Scenario)" />
                <NavigationProperty Name="Script" Type="Collection(EntityModel.Script)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="ExecutedScriptByResolutionType" Type="Collection(EntityModel.ExecutedScriptByResolutionType)" />
                <NavigationProperty Name="ExecutedScriptBySeverityType" Type="Collection(EntityModel.ExecutedScriptBySeverityType)" />
                <NavigationProperty Name="RuleLatestExecution" Type="Collection(EntityModel.RuleLatestExecution)" />
                <NavigationProperty Name="EventEntityLinkExceptionTypeLinkExecutionResultLink" Type="Collection(EntityModel.EventEntityLinkExceptionTypeLinkExecutionResultLink)" />
                <NavigationProperty Name="ExceptionTypeRuleLink" Type="Collection(EntityModel.ExceptionTypeRuleLink)" />
                <NavigationProperty Name="ExecutedRule" Type="Collection(EntityModel.ExecutedRule)" />
                <NavigationProperty Name="EventEntityLinkExceptionTypeLink" Type="Collection(EntityModel.EventEntityLinkExceptionTypeLink)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptByResolutionTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptByResolutionTypeDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptBySeverityTypeDateTimeline)" />
                <NavigationProperty Name="RuleDefaultAssignedTo" Type="Collection(EntityModel.RuleDefaultAssignedTo)" />
            </EntityType>
            <EntityType Name="IncidentBreakdown">
                <Key>
                    <PropertyRef Name="IncidentId" />
                </Key>
                <Property Name="IncidentId" Type="Edm.Int32" />
                <Property Name="StatusType" Type="Edm.Byte" Nullable="false" />
                <Property Name="IsDraft" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsActive" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsPending" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsSuppressed" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsClosed" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsDeferred" Type="Edm.Int32" Nullable="false" />
                <Property Name="CreatedDate" Type="Edm.DateTimeOffset" />
                <Property Name="CreatedDateYearMonth" Type="Edm.String" />
                <Property Name="CreatedDateYear" Type="Edm.Int32" />
                <Property Name="CreatedDateMonth" Type="Edm.Int32" />
                <Property Name="CreatedDateDayOfMonth" Type="Edm.Int32" />
                <Property Name="AssignedTo" Type="Edm.String" />
                <Property Name="IssueAssignedTo" Type="Edm.String" />
                <Property Name="IssueId" Type="Edm.Int32" />
                <Property Name="RuleId" Type="Edm.Int32" />
                <Property Name="FunctionalAreaId" Type="Edm.Int32" />
                <Property Name="EnvironmentId" Type="Edm.Int32" />
                <Property Name="ApplicationEntityId" Type="Edm.Int32" />
                <Property Name="CreatedDateText" Type="Edm.String" />
                <Property Name="CreatedDateTime" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="SeverityTypeReferenceId" Type="Edm.Int32" />
                <Property Name="IsCritical" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsHigh" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsLow" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsMedium" Type="Edm.Int32" Nullable="false" />
                <Property Name="IsUnclassified" Type="Edm.Int32" Nullable="false" />
                <NavigationProperty Name="ResolveStatusTypeReference" Type="EntityModel.ResolveStatusTypeReference" />
                <NavigationProperty Name="Issue" Type="EntityModel.Issue">
                    <ReferentialConstraint Property="IssueId" ReferencedProperty="IssueId" />
                </NavigationProperty>
                <NavigationProperty Name="ApplicationEntity" Type="EntityModel.ApplicationEntity">
                    <ReferentialConstraint Property="ApplicationEntityId" ReferencedProperty="ApplicationEntityId" />
                </NavigationProperty>
                <NavigationProperty Name="Rule" Type="EntityModel.Rule">
                    <ReferentialConstraint Property="RuleId" ReferencedProperty="RuleId" />
                </NavigationProperty>
                <NavigationProperty Name="FunctionalArea" Type="EntityModel.FunctionalArea">
                    <ReferentialConstraint Property="FunctionalAreaId" ReferencedProperty="FunctionalAreaId" />
                </NavigationProperty>
                <NavigationProperty Name="Environment" Type="EntityModel.Environment">
                    <ReferentialConstraint Property="EnvironmentId" ReferencedProperty="EnvironmentId" />
                </NavigationProperty>
                <NavigationProperty Name="Incident" Type="EntityModel.Incident">
                    <ReferentialConstraint Property="IncidentId" ReferencedProperty="IncidentId" />
                </NavigationProperty>
                <NavigationProperty Name="SeverityTypeReference" Type="EntityModel.SeverityTypeReference">
                    <ReferentialConstraint Property="SeverityTypeReferenceId" ReferencedProperty="SeverityTypeReferenceId" />
                </NavigationProperty>
            </EntityType>
            <EntityType Name="Issue">
                <Key>
                    <PropertyRef Name="IssueId" />
                </Key>
                <Property Name="IssueId" Type="Edm.Int32" Nullable="false" />
                <Property Name="DisplayCode" Type="Edm.String" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="StatusType" Type="Edm.Byte" Nullable="false" />
                <Property Name="Priority" Type="Edm.Decimal" />
                <Property Name="AssignedTo" Type="Edm.String" />
                <Property Name="Description" Type="Edm.String" />
                <Property Name="CreatedBy" Type="Edm.String" />
                <Property Name="CreatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="UpdatedBy" Type="Edm.String" />
                <Property Name="UpdatedDate" Type="Edm.DateTimeOffset" Nullable="false" />
                <Property Name="RuleId" Type="Edm.Int32" />
                <Property Name="ScriptId" Type="Edm.Int32" />
                <Property Name="ApplicationId" Type="Edm.Int32" />
                <Property Name="EnvironmentId" Type="Edm.Int32" />
                <Property Name="ApplicationEntityId" Type="Edm.Int32" />
                <Property Name="SeverityTypeReferenceId" Type="Edm.Int32" />
                <NavigationProperty Name="Application" Type="EntityModel.Application">
                    <ReferentialConstraint Property="ApplicationId" ReferencedProperty="ApplicationId" />
                </NavigationProperty>
                <NavigationProperty Name="ApplicationEntity" Type="EntityModel.ApplicationEntity">
                    <ReferentialConstraint Property="ApplicationEntityId" ReferencedProperty="ApplicationEntityId" />
                </NavigationProperty>
                <NavigationProperty Name="Environment" Type="EntityModel.Environment">
                    <ReferentialConstraint Property="EnvironmentId" ReferencedProperty="EnvironmentId" />
                </NavigationProperty>
                <NavigationProperty Name="Incident" Type="Collection(EntityModel.Incident)" />
                <NavigationProperty Name="ResolveStatusTypeReference" Type="EntityModel.ResolveStatusTypeReference" />
                <NavigationProperty Name="Rule" Type="EntityModel.Rule">
                    <ReferentialConstraint Property="RuleId" ReferencedProperty="RuleId" />
                </NavigationProperty>
                <NavigationProperty Name="Script" Type="EntityModel.Script">
                    <ReferentialConstraint Property="ScriptId" ReferencedProperty="ScriptId" />
                </NavigationProperty>
                <NavigationProperty Name="IssueReportPropertyLink" Type="Collection(EntityModel.IssueReportPropertyLink)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="SeverityTypeReference" Type="EntityModel.SeverityTypeReference">
                    <ReferentialConstraint Property="SeverityTypeReferenceId" ReferencedProperty="SeverityTypeReferenceId" />
                </NavigationProperty>
            </EntityType>
            <EntityType Name="ResolveStatusTypeReference">
                <Key>
                    <PropertyRef Name="ResolveStatusTypeReferenceId" />
                </Key>
                <Property Name="ResolveStatusTypeReferenceId" Type="Edm.Byte" Nullable="false" />
                <Property Name="Description" Type="Edm.String" />
                <Property Name="IsSystem" Type="Edm.Boolean" Nullable="false" />
                <Property Name="IsDefault" Type="Edm.Boolean" Nullable="false" />
                <Property Name="IsResolved" Type="Edm.Boolean" Nullable="false" />
                <Property Name="IsOpen" Type="Edm.Boolean" Nullable="false" />
                <Property Name="ColorId" Type="Edm.Int32" />
                <NavigationProperty Name="Color" Type="EntityModel.Color">
                    <ReferentialConstraint Property="ColorId" ReferencedProperty="ColorId" />
                </NavigationProperty>
                <NavigationProperty Name="Incident" Type="Collection(EntityModel.Incident)" />
                <NavigationProperty Name="Issue" Type="Collection(EntityModel.Issue)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
            </EntityType>
            <EntityType Name="SeverityTypeReference">
                <Key>
                    <PropertyRef Name="SeverityTypeReferenceId" />
                </Key>
                <Property Name="SeverityTypeReferenceId" Type="Edm.Int32" Nullable="false" />
                <Property Name="Name" Type="Edm.String" />
                <Property Name="Description" Type="Edm.String" />
                <Property Name="Active" Type="Edm.Boolean" Nullable="false" />
                <Property Name="ColorId" Type="Edm.Int32" />
                <Property Name="IsDefault" Type="Edm.Boolean" Nullable="false" />
                <Property Name="MinimumPriority" Type="Edm.Decimal" Nullable="false" />
                <Property Name="MidpointPriority" Type="Edm.Decimal" Nullable="false" />
                <Property Name="HasNoPriority" Type="Edm.Boolean" Nullable="false" />
                <NavigationProperty Name="Color" Type="EntityModel.Color">
                    <ReferentialConstraint Property="ColorId" ReferencedProperty="ColorId" />
                </NavigationProperty>
                <NavigationProperty Name="Incident" Type="Collection(EntityModel.Incident)" />
                <NavigationProperty Name="IncidentScenarioLink" Type="Collection(EntityModel.IncidentScenarioLink)" />
                <NavigationProperty Name="Issue" Type="Collection(EntityModel.Issue)" />
                <NavigationProperty Name="Scenario" Type="Collection(EntityModel.Scenario)" />
                <NavigationProperty Name="IncidentBreakdown" Type="Collection(EntityModel.IncidentBreakdown)" />
                <NavigationProperty Name="IssueBreakdown" Type="Collection(EntityModel.IssueBreakdown)" />
                <NavigationProperty Name="IncidentBreakdownDateTimeline" Type="Collection(EntityModel.IncidentBreakdownDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityTypeDateTimeline" Type="Collection(EntityModel.ExecutedScriptBySeverityTypeDateTimeline)" />
                <NavigationProperty Name="ExecutedScriptBySeverityType" Type="Collection(EntityModel.ExecutedScriptBySeverityType)" />
            </EntityType>
            <EntityContainer Name="Container">
                <EntitySet Name="IncidentBreakdowns" EntityType="EntityModel.IncidentBreakdown">
                    <NavigationPropertyBinding Path="ApplicationEntity" Target="ApplicationEntities" />
                    <NavigationPropertyBinding Path="Environment" Target="Environments" />
                    <NavigationPropertyBinding Path="FunctionalArea" Target="FunctionalAreas" />
                    <NavigationPropertyBinding Path="Incident" Target="Incidents" />
                    <NavigationPropertyBinding Path="Issue" Target="Issues" />
                    <NavigationPropertyBinding Path="ResolveStatusTypeReference" Target="ResolveStatusTypeReferences" />
                    <NavigationPropertyBinding Path="Rule" Target="Rules" />
                    <NavigationPropertyBinding Path="SeverityTypeReference" Target="SeverityTypeReferences" />
                </EntitySet>
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

I've only gone one level deep into the navigation properties.

Here's an example of a query that (for me) never completes:

POST IncidentBreakdowns/$query
Request Body: 
$apply=filter((Environment/EnvironmentId in [1,3,4,5,6]) and (false or SeverityTypeReferenceId in [5,4,2,3,1]) and (false or StatusType in [1,2,3,5]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name

If I remove just one expression (false or SeverityTypeReferenceId in [5,4,2,3,1]) then it completes no problem.

$apply=filter((Environment/EnvironmentId in [1,3,4,5,6]) and (false or StatusType in [1,2,3,5]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name

Now it sound like something is wrong with the expression that uses the SeverityTypeReferenceId property. However, if I leave that expression in place, and instead remove the expression and (false or StatusType in [1,2,3,5]) it also completes fine.

$apply=filter((Environment/EnvironmentId in [1,3,4,5,6]) and (false or SeverityTypeReferenceId in [5,4,2,3,1]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name
gathogojr commented 3 years ago

@cam-m Could try replacing the spaces on the query string in the request body with %20 and let us know how it behaves, i.e. $apply=filter([] eq [] or Property in []) => $apply=filter([]%20eq%20[]%20or%20Property%20in%20[])

cam-m commented 3 years ago

@gathogojr It makes sense url encoding might be required on a post body - however I haven't been doing that on any queries, and they all work perfectly when not accessed through VPN. I did however try 1) Uri encode spaces only, 2) encodeUriComponent() and 3) encodeUri() on the query string, and none fixed it.

The comment I made where I can remove two distinct parts of a query string to make the query work got me thinking the length of the query string is playing a part. I used simple and true expressions to gradually increase the length and I found a threshold for the above query.

This works (length=421):

$apply=filter((Environment/EnvironmentId in [1,3,4,5,6,7,8,9]) and true and true and true and true and true and (false or StatusType in [1,2,3,5]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name

I add one character and it fails. I change where I add the character in the query and it fails.

The following two queries add a single character in different locations to get a length of 422 and they both fail.

$apply=filter((Environment/EnvironmentId in [1,3,4,5,6,7,8,90]) and true and true and true and true and true and (false or StatusType in [1,2,3,5]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name
$apply=filter((Environment/EnvironmentId in [1,3,4,5,6,7,8,9]) and true and true and true and true and true and (false or StatusType in [10,2,3,5]))/groupby((FunctionalArea/FunctionalAreaId,FunctionalArea/Name),aggregate(IsUnclassified with sum as UnclassifiedTotal,IsLow with sum as LowTotal,IsMedium with sum as MediumTotal,IsHigh with sum as HighTotal,IsCritical with sum as CriticalTotal))&$orderby=FunctionalArea/Name

Does this make any sense?

cam-m commented 3 years ago

For now I have worked around this issue using the $batch option