CRM 2011 sdk - quickest/most efficient way to get contact info + associated list IDs

Question

Our CRM 2011 database contains approx. 20000 contacts, and I need to loop through all of them using the SDK. Currently I'm finding the following linq query takes a very long time to execute:

Dim contactList = From c In orgService.ContactSet 
  Select New With {
    Key .ContactId = c.ContactId,
    Key .EMailAddress1 = c.EMailAddress1,
    Key .ListIds = From l In c.listcontact_association Select l.ListId
  }

As you can see, I just need a couple of fields from each Contact, and a list of associated Marketing List IDs. Perhaps it's taking a long time because it's doing an additional query (to get the list IDs) within each contact result?

I'm fairly new to Linq, so not sure how the above translates to actual FetchXML communication. Is there a more efficient way of getting that info which would result in shorter query run time?

More Info: I'm writing code to sync a CRM database with a CreateSend database. So I do need to go through all contact records, not only adding to the CS database, but also reflecting changes in list membership and updating activity or other info for each contact where needed. The sync process will eventually run nightly on the CRM server itself, so it's expected to take time to run, but of course I want to make it as efficient as possible.

What is taking a long time - the query to get all the contacts, or iterating through all 20,000 of them? — Peter Majeed

Peter Majeed Peter Majeed · Accepted Answer · 2012-06-26T16:36:37

In the absence of more information, it looks like your query is actually two - one query to get the list of contacts, and then another to get the list of list ids. This means SQL Server is returning the full list of 20,000 contacts, then for each contact, your code asks SQL Server for the list of associated list ids.

This means you are making 20,000 + 1 separate calls to SQL Server (though this is actually an abstracted total - the CRM SQL translator actually makes more than that, but it doesn't add significant overhead).

So what you really want to do is make just 1 query that gets you all your data, and then begin to work with it. I'm not proficient enough in VB.NET to make the translation, but the below C# code should get you there most of the way.

// Gets all contacts and list ids in a flat result set
var contacts = from c in orgService.ContactSet
               join lms in orgService.ListMemberSet on c.ContactId equals lms.EntityId.Id into leftJoinedContact
               from ljc in leftJoinedContact.DefaultIfEmpty()
               where ljc.EntityType == Xrm.Contact.EntityLogicalName
               select new
               {
                   c.ContactId,
                   c.EMailAddress1,
                   ljc.ListId
               };

// Calls .ToList() to put the result set in memory
var contactList = contacts.ToList();

// Manipulates the result set in memory, grouping by contact info
var newContactList = contactList.GroupBy(x => new {x.ContactId, x.EMailAddress1})
    .Select(x => new {x.Key.ContactId, x.Key.EMailAddress1, Ids = x.Select(y => y.ListId).ToList()})
    .ToList();

// var contactsWithListIds = newContactList.Where(x => x.Ids.Where(y => y != null).Any()).ToList();
// var contactsWithoutListIds = newContactList.Where(x => x.Ids.Where(y => y != null).Any()).ToList();

foreach (var contact in newContactList)
{
    throw new NotImplementedException();
}

CRM 2011 sdk - quickest/most efficient way to get contact info + associated list IDs

2 Answers