locked
Most efficient way to retrieve an entity collection by a range of IDs RRS feed

  • Question

  • I have a List<Guid> object with a bunch of IDs in it.

    I would like to retrieve the records for the IDs in the list.

    I have thought that maybe I could use a Multiple Request or maybe I could use a Query Expression with a loop to set each ID in an OR statement.  Maybe there is another way I haven't seen before.

    Whatever I choose I would like it to be fast.



    Tuesday, August 5, 2014 8:52 AM

Answers

  • Hi,

    Dont use loop. 

    Use QueryExpression Condition Operator IN.

    With only one hit to the data base you will get all respective records.

    Something like this.

    Lets assume Guids are in 

    string[] ids;

    string valueNodeString = "";
                for (var i = 0; i < ids.Length; i++)
                {
                    valueNodeString += "<value>" + ids[i] + "</value>";
                }
                var fetchXml = @"<fetch version='1.0' mapping='logical'>
                                   <entity name='contact'>
                                      <attribute name='firstname' />
                                      <filter>
                                         <condition attribute='contactid' operator='in'>" +
                                            valueNodeString +
                                         "</condition>" +
                                    "</filter>" +
                                   "</entity>" +
                                "</fetch>";

    Hope this will help you.


    Thanks and Regards. Make sure to &quot;Vote as Helpful&quot; and &quot;Mark As Answer&quot;,if you get answer of your question.


    Tuesday, August 5, 2014 9:31 AM
  • Providing the maximum number of IDs is less than ~2000, the best performance would probably be to use a QueryExpression using the In operator, with the values passed as an array of Guid

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, August 5, 2014 9:28 AM
    Moderator

All replies

  • Providing the maximum number of IDs is less than ~2000, the best performance would probably be to use a QueryExpression using the In operator, with the values passed as an array of Guid

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Tuesday, August 5, 2014 9:28 AM
    Moderator
  • Hi,

    Dont use loop. 

    Use QueryExpression Condition Operator IN.

    With only one hit to the data base you will get all respective records.

    Something like this.

    Lets assume Guids are in 

    string[] ids;

    string valueNodeString = "";
                for (var i = 0; i < ids.Length; i++)
                {
                    valueNodeString += "<value>" + ids[i] + "</value>";
                }
                var fetchXml = @"<fetch version='1.0' mapping='logical'>
                                   <entity name='contact'>
                                      <attribute name='firstname' />
                                      <filter>
                                         <condition attribute='contactid' operator='in'>" +
                                            valueNodeString +
                                         "</condition>" +
                                    "</filter>" +
                                   "</entity>" +
                                "</fetch>";

    Hope this will help you.


    Thanks and Regards. Make sure to &quot;Vote as Helpful&quot; and &quot;Mark As Answer&quot;,if you get answer of your question.


    Tuesday, August 5, 2014 9:31 AM
  • Thanks.

    You have saved me a great deal of time and experimentation.

    Tuesday, August 5, 2014 10:23 AM