You read my last post and decided Microsoft Dynamics 365’s Virtual Entities (VE) may be the right solution for you. Welcome to Step 2: Building a custom OData API to leverage VE within Dynamics 365.
There are numerous tutorials for creating an OData API in .NET, but we’ll be focusing on how to ensure your OData API integrates with Dynamics with minimal headaches. The current dearth of VE documentation can be frustrating, so we did some homework to make your life easier. Here are our 4 main discoveries, shared to help you avoid shooting yourself in the foot:
- Overview of how Dynamics makes OData Calls
- Limitation of entity joins
- .NET Configuration
- Debugging
How Dynamics makes OData calls
When Dynamics makes a call for a virtual entity with no lookup, the request URL adheres to an expected OData syntax. Here’s what a call looks like to retrieve a VE Courses entity requesting only the Name column.
https://YourODataEndpoint/Courses?$top=50&$select=CourseId,Name&$count=true
$top=50
Refers to how many results are displayed on the page. If you have configured your CRM to display 250 records, the value would be $top=250.
&$select=CourseId ,Name
The columns you request to come back.
&$count=true
Conveniently, the total amount of records dynamically displays at the bottom of the window.
If you paginate to the next screen the query will be identical, except it will prepend $skip=50 to the beginning of the URL, as expected.
Related Records Oddity
OData provides an expand statement to retrieve related records, which would allow Dynamics to do joins between entities. However, Dynamics does not utilize this statement if you add a lookup to your query. For instance, if you have a view of Courses and include a Subject lookup, we would expect Dynamics to compose a query such as this one:
https://YourODataEndpoint/Courses?$top=50&$select=CourseId,Name&$expand=Subject…
Instead Dynamics makes multiple calls. The first is to get all Courses:
https://YourODataEndpoint/Courses?$top=50&$select=CourseId,Name&$count=true
Unexpectedly, instead of using the expand statement, it makes 3 calls to get the Subject:
https://YourODataEndpoint/Subjects?$filter=SubjectId eq ‘00000-00000-Subject1-Guid’ or Subjectid eq ‘00000-00000-Subject2-Guid’ eq ‘00000-00000-Subject3-Guid’ or Subjectid eq ‘00000-00000-Subject4-Guid’…
In plain English – “Give me all the subjects who have this ID or this one or this one, etc.”
It’s cryptic at first, but all the GUIDs you see reference a Subject that appears in the lookup from the original call to get the courses. It makes 3 calls because it limits the number of GUIDs to 20 per call. Since our original request asked for 50 courses, if each course has one subject, we would need 2 calls for the first 40 subjects and one more for the remaining 10.
On the Dynamics side if you’re trying to show the related fields of a lookup field, this feature is not supported. Further information is available in my last blog.
.NET Configurations
Since Dynamics requires GUIDs as primary keys, make sure your OData controllers accept a GUID as the identifier for a SingleResult request instead of an INT, which is the default.
You will also need to modify the WebApiConfig class to ensure methods such as “Count” are allowed. Make sure this line of code is included:
Debugging on the Dynamics Side
If you follow any Virtual Entities tutorial, you will undoubtedly misspell a property or entity name, and your query will result in the following ambiguous error:
The easiest way to see the actual error is to create a lookup on a working entity (virtual or non) to the Virtual Entity that is causing issues. When you open that working entity record, an error will pop up with a downloadable log file.
If you open the log file you should see the error in the stack trace:
Microsoft.Crm.CrmException: An external attribute named ‘1misspelled’ wasn’t found. Attribute Name: new_1misspelled
Conclusion
Dynamics is an ever-evolving product. The more we discover, the more we’ll share.
If you missed my first VE post, you’ll want to circle back around and read Virtual Entities: Are they a fit for your production environment?
If you still have questions – or if you’ve made your own discovery that could help fellow users maintain their/our sanity – send me an email. It takes a village!
About Sean
Sean Astrakhan is an educator by training (and nature). He’s lived and taught in Michigan, Colombia and China, but is happy to call Baltimore “home.” Being an extrovert with penchants for adventure and over analysis has led Sean to a career in technology consulting where he thrives on meeting new people and remedying their tech hardships.
When Sean isn’t coding or working with clients to make their lives easier, he’s getting schooled by Baltimore youth or falling on his face in adult gymnastics classes. But none of that bothers Sean because he never wants to stop learning and perfecting new skills. To paraphrase one of his favorite Beyoncé lyrics, “He grinds ‘til he owns it.”
If you need more Sean in your life, check out his burgeoning blog or, better yet, come work with him.
