Linq LoadProperty Issue – Description
Recently I had to fix some legacy code (one of the worst code I have ever seen) and improve requests performance. When reviewing the code everything looks fine.
There was a OrganizationServiceContext generated by crmsvcutil. Because one of the property was N:N relation a LINQ LoadProperty method was used. Nevertheless business logic was not to associate record if the relation was already existing. But from time to time on live environment an exception has been thrown: Cannot insert duplicate key.
While debugging I have noticed that only one item had more that 5000 relation within N:N. But LINQ LoadProperty method limits total number of returned records. Of course 5k is a standard limit related to queries throttling and can be modified in DB and/or widows registry. But this is not the solution.
When reviewing MS documentation dedicated for that method (https://msdn.microsoft.com/en-us/library/gg309538.aspx) which says:
You can use the intersect entities to refine the result set in any query by using the IOrganizationService.RetrieveMultiple method or theRetrieveMultipleRequest message. However, you cannot retrieve the intersect entity records directly by using the QueryExpression class. To retrieve the records in an intersect entity, you must use the FetchExpression class.
WAT? Seriously – FetchExpression. Because I hate these expressions I have written a standard QueryExpression, run it within RetrieveMultiple request with paging operations.
And guess what? Is is working fine – even if MS documentation says something else.
Thanks to this method I was able to retrieve over 5000 (or any server limit that is currently ON) records without any problems.
Hope it helps 🙂
P.S. If you found this post useful please rate it.