Getting weird behavior when retrieving data from Microsoft CRM using LINQ

Question

I'm having this problem accessing the Contact entity using LINQ.

I have the 2 functions below.

If I ran the 1st function and then call the 2nd one, I seemed to be missing a lot of fields in the 2nd query. Like firstname and lastname are not showing up. They just shows up as null values. If I ran the 2nd function on its own, I am getting the right data. The only fields that shows up correctly in both runs are Id, ContactId and new_username.

If I ran the 2nd function on its own, I am getting the right data.

Any ideas what am I doing wrong?

Thanks a lot

Here are the 2 functions

    public List<String> GetContactsUsernameOnly()
    {
        IQueryable<String> _records = from _contactSet in _flinsafeContext.ContactSet
                                       where
                                           _contactSet.new_FAN == "username"
                                       orderby _contactSet.new_username
                                       select _contactSet.new_username;

        return _records.ToList();
    }

    public List<Contact> GetContacts()
    {
        IQueryable<Contact> _records = from _contactSet in _flinsafeContext.ContactSet
                                       where
                                           _contactSet.new_FAN == "my-username-here"
                                       orderby _contactSet.new_username
                                       select _contactSet;

        return _records.ToList();
    }

score 6 · Accepted Answer · answered Jul 11 '14 at 07:32

It is because you are reusing the same CRM context when you call both methods (in your case _flinsafeContext)

What the context does is cache records, so the first method is returning your contact but only bringing back the new_username field.

The second method wants to return the whole record, but when it is called after the first one the record already exists in the context so it just returns that, despite only having the one field populated. It is not clever enough to lazy load the fields that have not been populated. If this method was called first, it doesn't exist in the context so will return the whole record.

There are 2 ways to get around this:

1) Don't reuse CRMContexts. Instead create a new one in each method based on a singleton IOrganizationService.

2) There is a ClearChanges() method on your context that will mean the next time you do a query it will go back to CRM and get the fields you have selected. This will also clear any unsaved Created/Updates/Deletes etc so you have to be careful around what state the context is in.

As an aside, creating a new CRM Context isn't an intensive operation so it's not often worthwhile passing contexts around and reusing them. It is creating the underlying OrganisationService that is the slowest bit.

This behaviour can be so painful, because it is horribly inefficient and slow to return the entire record so you WANT to be selecting only the fields you want for each query.

If I want to return a list of entites, I cannot be selective in the fields right? Thanks for the answer :) — mrjayviper, Jul 14 '14 at 23:42

score 0 · Answer 2 · answered Jul 11 '14 at 10:19

And here's how you return just the fields you want:

IEnumerable<ptl_billpayerapportionment> bpas = context.ptl_billpayerapportionmentSet
.Where(bm => bm.ptl_bill.Id == billId)
.Select(bm => new ptl_billpayerapportionment()
{
    Id = bm.Id,
    ptl_contact = bm.ptl_contact
})

This will ensure a much smaller sql statement will be executed against the context as the Id and ptl_contact are the only two fields being returned. But as Ben says above, further retrievals against the same entity in the same context will return nulls for fields not included in the initial select (as per the OP's question).

For bonus points, using IEnumerable and creating a new, lightweight, entity gives you access to the usual LINQ methods, e.g. .Any(), .Sum() etc. The CRM SDK doesn't like using them against var datasets, apparently.

Getting weird behavior when retrieving data from Microsoft CRM using LINQ

2 Answers2

Linked