Sql4Cds icon indicating copy to clipboard operation
Sql4Cds copied to clipboard
verified publisher icon MarkMpn

[BUG] Entity not found exception on OUTER APPLY subquery

Open macco3k opened this issue 11 months ago • 3 comments

Describe the bug Not sure this is a bug. The db reader does not throw an exception, but the error below is being reported in the logs. The only thing we've added was an OUTER APPLY clause with an alias to the query.

The query also runs fine in Azure Data Studio.

To Reproduce Query to reproduce the behavior:

SELECT outer.id, alias.field OUTER APPLY (SELECT field FROM inner WHERE inner.parentid = outer.id) alias

Expected behavior No error message is present, or an exception is thrown if something is wrong with the query.

Screenshots No screenshot, but here's the full error message

[TerminalFailure] Failed to Execute Command - RetrieveEntity : RequestID=1595763f-7762-4715-ba89-539252bc26f5 : Execute (RetrieveEntity) request to Dataverse from IOrganizationService duration=00:00:00.1149950 ExceptionMessage = Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
Source: System.ServiceModel.Primitives
Method: HandleReply
DateUTC: 1/14/2025
TimeUTC: 4:01:37 PM
Error: Message: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
ErrorCode: -2147220969
Trace: 
Error Details   :
CallStack       :    at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.Pipeline.<>c__DisplayClass6_0.<RunStep>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
   at Microsoft.Crm.Extensibility.Pipeline.ExecuteStep(IPipelineStep step, PipelineExecutionContext context, Int32 stepCount, Int32 totalSteps, Boolean alternateKeySupportInPluginsEnabled, Boolean enableVerboseLogging)
   at Microsoft.Crm.Extensibility.Pipeline.ExecuteSteps(Collection`1 steps, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.MessageProcessor.RunStage(PipelineExecutionContext context, Int32 pipelineStage, Dictionary`2 pipelines)
   at Microsoft.Crm.Extensibility.MessageProcessor.Execute(PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.InternalMessageDispatcher.<>c__DisplayClass5_0.<Execute>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
   at Microsoft.Crm.Extensibility.ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, Guid callerRegardingObjectId, Int32 invocationSource, Version endpointVersion, OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.DispatchRequest(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest, SdkResponseDescription responseDescription, SdkPerformanceCounterContext sdkPerformanceCounterContext, IHttpContext httpContext)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentationOperation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.<>c__DisplayClass27_0.<ExecuteRequest>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, UserAuth userAuth, Guid targetUserId, Guid targetCallerRegardingObjectId, UserType targetUserType, OrganizationContext context, Boolean returnResponse, Boolean checkAdminMode, Dictionary`2 optionalParameters)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestInternal(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext, Dictionary`2 optionalParameters)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext, Dictionary`2 optionalParameters)
ApiExceptionSourceKey   : Plugin/Microsoft.Crm.Sdk.Metadata.MetadataServiceProvider
ApiSourceActivityKey    : 
ApiExceptionOwnerKey    : OwnershipNotFoundInExceptionOwnerSettings
ApiOriginalExceptionKey : Microsoft.Crm.BusinessEntities.CrmObjectNotFoundException: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614). ---> Microsoft.Crm.BusinessEntities.CrmObjectNotFoundException: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
   at Microsoft.Crm.Sdk.Metadata.MetadataReaderOptimized.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)
   at Microsoft.Crm.Sdk.Metadata.MetadataServiceProvider.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)
   --- End of inner exception stack trace ---
   at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.Pipeline.<>c__DisplayClass6_0.<RunStep>b__0()
ApiStepKey      : 38b005c5-b7f8-4c22-97ed-a7abc9c22b6c
ApiDepthKey     : 1
ApiActivityIdKey        : 16647d7a-48ec-4307-8443-c14678bf2d48
ApiPluginSolutionNameKey        : System
ApiStepSolutionNameKey  : System
ApiExceptionCategory    : ClientError
ApiExceptionMessageName : ObjectDoesNotExist
ApiExceptionHttpStatusCode      : 404

HelpLink Url: Not Provided
Stack Trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
   at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
   at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(MethodCall methodCall, ProxyOperationRuntime operation)
   at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(MethodInfo targetMethod, Object[] args)
   at generatedProxy_1.Execute(OrganizationRequest)
   at Microsoft.PowerPlatform.Dataverse.Client.Connector.OrganizationWebProxyClientAsync.<>c__DisplayClass52_0.<ExecuteCore>b__0()
   at Microsoft.PowerPlatform.Dataverse.Client.Connector.WebProxyClientAsync`1.ExecuteAction[TResult](Func`1 action)
   at Microsoft.PowerPlatform.Dataverse.Client.Connector.OrganizationWebProxyClientAsync.ExecuteCore(OrganizationRequest request)
   at Microsoft.PowerPlatform.Dataverse.Client.Connector.OrganizationWebProxyClientAsync.Execute(OrganizationRequest request)
   at Microsoft.PowerPlatform.Dataverse.Client.ServiceClient.Command_Execute(OrganizationRequest req, String errorStringCheck, Boolean bypassPluginExecution)
======================================================================================================================
Inner Exception Level 1 : 
==OrganizationServiceFault Info=======================================================================================
Error: Message: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
ErrorCode: -2147220969
Trace: 
Error Details   :
CallStack       :    at Microsoft.Crm.Sdk.Metadata.MetadataReaderOptimized.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)
   at Microsoft.Crm.Sdk.Metadata.MetadataServiceProvider.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)

Time: 1/14/2025 4:01:37 PM
ErrorCode: -2147220969
DateUTC: 1/14/2025
TimeUTC: 4:01:37 PM
HelpLink Url: Not Provided
Trace: Not Provided
======================================================================================================================

System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614). (Fault Detail is equal to Exception details: 
ErrorCode: 0x80040217
Message: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
StackTrace: 
   at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.Pipeline.<>c__DisplayClass6_0.<RunStep>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
   at Microsoft.Crm.Extensibility.Pipeline.ExecuteStep(IPipelineStep step, PipelineExecutionContext context, Int32 stepCount, Int32 totalSteps, Boolean alternateKeySupportInPluginsEnabled, Boolean enableVerboseLogging)
   at Microsoft.Crm.Extensibility.Pipeline.ExecuteSteps(Collection`1 steps, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.MessageProcessor.RunStage(PipelineExecutionContext context, Int32 pipelineStage, Dictionary`2 pipelines)
   at Microsoft.Crm.Extensibility.MessageProcessor.Execute(PipelineExecutionContext context)
   at Microsoft.Crm.Extensibility.InternalMessageDispatcher.<>c__DisplayClass5_0.<Execute>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
   at Microsoft.Crm.Extensibility.ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, Guid callerRegardingObjectId, Int32 invocationSource, Version endpointVersion, OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.DispatchRequest(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest, SdkResponseDescription responseDescription, SdkPerformanceCounterContext sdkPerformanceCounterContext, IHttpContext httpContext)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentationOperation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.<>c__DisplayClass27_0.<ExecuteRequest>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, UserAuth userAuth, Guid targetUserId, Guid targetCallerRegardingObjectId, UserType targetUserType, OrganizationContext context, Boolean returnResponse, Boolean checkAdminMode, Dictionary`2 optionalParameters)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.ExecuteRequestInternal(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext, Dictionary`2 optionalParameters)
   at Microsoft.Crm.Extensibility.OrganizationSdkServiceInternal.Execute(OrganizationRequest request, InvocationContext invocationContext, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext, Dictionary`2 optionalParameters)
TimeStamp: 2025-01-14T16:01:37.9903604Z
--
Exception details: 
ErrorCode: 0x80040217
Message: Could not find an entity with name capacity and id 00000000-0000-0000-0000-000000000000 (229556614).
StackTrace: 
   at Microsoft.Crm.Sdk.Metadata.MetadataReaderOptimized.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)
   at Microsoft.Crm.Sdk.Metadata.MetadataServiceProvider.RetrieveEntity(EntityFilters entityItems, String logicalName, Guid metadataId, Boolean retrieveAsIfPublished, ExecutionContext context)
TimeStamp: 2025-01-14T16:01:37.9903604Z
--
).

Environment (please complete the following information):

  • SQL 4 CDS edition: [ADO.NET provider]
  • Results of SELECT @@VERSION: Microsoft Dataverse - 9.2.24113.204 SQL 4 CDS - 9.3.0.0 Oct 25 2024 14:58:47 Copyright © 2020 - 2024 Mark Carrington

Additional context

We are using the plugin in our own dotnet project with a service client object created like so

ServiceClient(
    $"AuthType=ClientSecret;Url={dataverseSettings.Value.Instance};ClientId={dataverseSettings.Value.ClientId};Secret={dataverseSettings.Value.ClientSecret}",
    logger
);

The service client being part of the Microsoft.PowerPlatform.Dataverse.Client namespace.

Sponsorship If you find this tool useful, please consider sponsoring its development.

macco3k avatar Jan 14 '25 16:01 macco3k

I get a syntax error when trying to run your example query. I assume this is a modified version of the actual query you're using - can you supply the full query you're using please?

MarkMpn avatar Jan 22 '25 08:01 MarkMpn

Sure, here you go

SELECT  location.hit_leveradres,
                location.hit_nummerleveradres,
                location.hit_plaatsleveradres,
                location.hit_postcodeleveradres,
                location.hit_toevoegingleveradres,
                connection.hit_eancode,
                connection.hit_netbeheerder,
                connection.hit_nieuwmeetbedrijfname,
                connection.hit_oudmeetbedrijfname,
                connection.hit_slimmemetername,
                connection.hit_typeaansluitingname,
                contract.hit_begindatum,
                contract.hit_einddatum,
                contract.hit_leveranciername,
                contract.hit_soortgroenestroomname,
                contract.hit_tariefhoog,
                contract.hit_tarieflaag,
                contract.hit_tariefenkel,
                contract.hit_tariefkwhtlplateau,
                contract.hit_tariefkwhtldal,
                contract.hit_toeslaggroenestroomvaluta,
                contract.hit_vastrecht,
                contract.hit_verbruikenkel,
                contract.hit_verbruikhoog,
                contract.hit_verbruiklaag,
                contract.hit_totaalverbruik,
                purchase_profile.hit_inkoopprofieloptie,
                capacity.hit_gecontracteerdvermogenkw2,
                capacity.hit_zekeringwaardename
        FROM hit_energiealgemeen location
        JOIN hit_elektriciteitean connection ON connection.hit_energiealgemeen = location.hit_energiealgemeenid
        JOIN hit_elektriciteitscontract contract ON contract.hit_eancode = connection.hit_elektriciteiteanid
        LEFT OUTER JOIN hit_inkoopprofielvragenlijst purchase_profile ON purchase_profile.hit_inkoopprofielvragenlijstid = contract.hit_inkoopprofielvragenlijst
        OUTER APPLY (
            SELECT TOP 1 
                hit_gecontracteerdvermogenkw2,
                hit_zekeringwaardename
            FROM 
                hit_gecontracteerdvermogen 
            WHERE 
                hit_elektriciteitean = contract.hit_eancode
                AND hit_ingangsdatum <= contract.hit_einddatum
                AND statecode = @activeStateCode
            ORDER BY
                hit_ingangsdatum DESC
        ) AS capacity
        WHERE location.statecode = @activeStateCode
            AND connection.statecode = @activeStateCode
            AND contract.statecode = @activeStateCode
            AND (purchase_profile.statecode is null or purchase_profile.statecode = @activeStateCode)
            AND contract.hit_contractviahit = @contractViaHit
            AND location.hit_account = @organizationId

macco3k avatar Jan 23 '25 16:01 macco3k

I can't reproduce this error at the moment. Can you please try updating to the latest version of SQL 4 CDS and see if it still occurs? If so, can you please post a simplified query that demonstrates the issue using only standard tables?

MarkMpn avatar Apr 23 '25 19:04 MarkMpn