locked
Limit on quantity of <Link-Entity> in Fetch XML is 10? RRS feed

  • Question

  • Hi guys,

    Is there a limit on the number of <Link-Entity>s we can have in a single fetch XML query? The schema has a * on the multiplicity so it in theory there is no limit, but is there some practical limit I could be bumping up against? I beleive SQL has a limit of 256 joins so I don't think the underlying DB is holding this query back.

    I've got some sample queries, two that work, and one that doesn't... The query that does NOT work has 11 link-entities. If I remove one of the link-entities (from any level of the nesting), reducing my count of link-entities to 10, then the query works. Too strange. I can post a set of 3 sample queries to illustrate and let anyone confirm, but to save me the effort for now, they look like:

    Query A: (DOES NOT WORK; 11 link-entitys):
    Query B (WORKS: comment out a root-level link-entity; 10 link-entitys)
    Query C (WORKS: comment out a nested link-entity; 10 link-entitys)

    Does anyone have a query that works with more than 10 link-entitys?

    Surely I am missing something or need to change some setup parameter but I don't see anything in the documentation and I'm coming up blank when I google for any kind of limit... I am executing the query in VS2008-BIDS, perhaps there is a bug there? I have srsDataconnector and report extensions installed and updated running against CRM2011.

    Thanks,
    Ben

    Tuesday, February 8, 2011 3:30 PM

All replies

  • I'm not aware of such a limit, outside of that imposed by SQL Server.  It isn't just the Linked Entities that produce joins however.  Picklists for example will produce a join, as the reference data (value to text) is stored in a separate table.

    You can use SQL Server profiler to view the queries produced by a CRM web service call if you suspect that this is the issue.


    --pogo (pat)
    Tuesday, February 8, 2011 7:58 PM
  • Thank you, that is a helpful idea!

    I profiled against SQL and the query never gets there.

    The syntax is correct, and the <link-entity>s are correct, in short, the FetchXML is correct. Its the processing of the fetchXML that is failing. I guess this is a bug in the way sql is built from the fetchXML, either in BIDS or in general...

    I'll try to execute the fetchXML directly against the API instead of through BIDS and see what I get.

    Wednesday, February 9, 2011 3:17 PM
  • I was able to execute the fetchXML against the organization service and I get a error message (pasted below) saying I've exceeded the limit of link entities... Is this something I can configure or is the fetch XML parser not capable of this kind of complex reporting query? If it is not capable, why not? If it is, what is the reason for the limit?

    Also, the schema says unlimited.. Since the processing is limited it might be prudent to update the schemas to reflect this limit, even if just in a comment in the XSD? (because I dont think XSD has a notation for 'maximum of 10 of this element anywhere in the document').

    Any workaround suggestions would be appreciated as well.

    System.ServiceModel.FaultException`1 was unhandled
      Message=Number of link entities in query exceeded maximum limit.
      Source=mscorlib
      Action=http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/RetrieveMultipleOrganizationServiceFaultFault
      StackTrace:
        Server 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.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
           at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)
        Exception rethrown at [0]:
           at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
           at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
           at Microsoft.Xrm.Sdk.IOrganizationService.RetrieveMultiple(QueryBase query)
           at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.RetrieveMultipleCore(QueryBase query)
           at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.RetrieveMultiple(QueryBase query)

    Wednesday, February 9, 2011 3:30 PM
  • XSD does have a notion of cardinality via the minOccurs and maxOccurs attributes, but I see no attempt to limit it in the FetchXml schema either.

    The error message is pretty clear, but I've not come across it before nor could I find any reference to it elsewhere (other than a description of exactly that error message with its corresponding error code).  It doesn't seem to be documented anywhere, so if there is a way to increase the limit, it will likely take some digging.

    There are similar error codes for exceeding the limit of conditions and condition parameters but no indicate of what that limit might be.


    --pogo (pat)
    Wednesday, February 9, 2011 7:27 PM
  • I am having a similar problem as the original poster and have not been able to find any solid information on this issue, or a solution. 

    Has anyone made any progress with this? Do we know if there is a way around the 10 limit on link-entities? Do we know why this limit exists? 

    Many thanks for your assistance

    Kind regards 

     

    Monday, November 14, 2011 5:28 PM
  • Hi,

    This is an intentional product restriction. We cannot use more than 10 link entities. We'll try to get the SDK updated.


    Nabanita Majumdar
    Thursday, January 26, 2012 11:54 PM
  • Actually we were able to overwrite this behaviour by directly writing into MSCRM_CONFIG\DeploymentProperties table. You have to insert a row containing ColumnName = QueryLinkEntityLimit. Well, this is unsupported, makes your installation quite a bit slower and possibly won't be available starting w/ the next rollup. Nevertheless, it is possible :)

    • Proposed as answer by arcadeuk Thursday, December 6, 2012 4:18 PM
    Wednesday, February 1, 2012 10:20 AM