Thursday, January 12, 2012

CRM 2011 LINQ - All Columns vs. Selected Columns



When creating LINQ queries in CRM 2011, it is easy enough to return the whole column set of the entity record without even thinking about the impact.  

Below is an example of querying all contacts from Chicago and returning all columns for each contact record.



This can be a big performance impact depending on the amount of columns that exist on the Contact entity and how many contact records exist in the system.  Another issue is that it could cause errors down the line if some of the attribute types are changed in the CRM system since the data is being bound to a model class that could be out-of-sync.

From the MSDN article on constructing LINQ queries (http://msdn.microsoft.com/en-us/library/gg328328.aspx), we can see that the select clause creates a column set:

The select clause defines the form of the data returned. The clause creates a column set based on the query expression results. You can also define an instance of a new object to work with. The newly created object using the select clause is not created on the server, but is a local instance.
select clause

Therefore we should change our LINQ query to specify only the columns we need so we are not returning all columns.




 This will provide better performance and be more stable for the future.

5 comments:

  1. Lets say you wanted to do this dynamically, i.e. you wanted to pass the columns you want to select on as a parameter, i.e. pass in a columnset or string array and then use LINQ to do that. Is that possible?

    ReplyDelete
  2. No, I don't believe that is possible.

    ReplyDelete
  3. Hi!

    And if you want to select columns which is in a view in crm?

    ReplyDelete
  4. It's a shame this doesn't work for all columns (e.g. CreatedOn, CreatedBy) since the properties in the generated class are read only.

    ReplyDelete
  5. Well, this helped me over two years after the posting date - thank you :-)

    ReplyDelete