Dynamics 365 v9 Virtual Entity using OOB OData v4 Provider - PART 1

Virtual entity is great new way of letting Dynamics 365 users to consume external data in read-only mode. In version 9.0, developers can develop custom data source providers to interact with external data sources rather than using the OOB OData v4 Data Provider.

The purpose of this post is to provide some guideline for setting up a virtual entity using the OOB OData v4 Data Provider by connecting it to a custom developed Web Api and see what we can do with the virtual entity in Dynamics 365. So, custom data source provider would be another post for another day.

At the time of writing this post, I am using Dynamics 365 version 1710 (9.0.2.2074) online for development and testing.

There scenario 

We have a list a customers (Accounts) in Dynamics 365, and we record customers' job data in another system. Users would like to see customers' job data in Dynamics 365 rather than open another application to find related jobs.

Job Data Model

Just for demonstration's sake, the schema for the job data is simple, it contains the following fields:

  • JobId: Guid - this is the primary key and it has to be Guid type for virtual entity to consume the data. You will get error in Dynamics 365 if the primary key is int or other date types.
  • JobName: string
  • ScheduledStart: DateTime
  • CustomerId: Guid? - the value of this property needs to be existing Account entity record's CRM Guid. It is used as a lookup field to Account entity in virtual entity. The data type is nullable, so that the property can be recognized as optional. If you use Guid, then the metadata will make the property non-nullable.
In Visual Studio 2017, we start by creating a ASP.NET Web Application (.NET framework) project. In the next screen, select Empty and tick Web API as shown below.


After the project is created, go to Manage NuGet Packages, and install Microsoft.AspNet.OData.

Add a new model class with name "Job.cs", and put the following code in. For demonstration's sake, I simply hard coded the job data being returned in the getJobs private method. If you just want to copy the code to test in your own environment, please update the CustomerId Guids to whatever Guids of your Account entity records.


namespace JobVirtualEntity.Models
{
    public class Job
    {
        public Guid JobId { get; set; }
        public string JobName { get; set; }
        public DateTime ScheduledStart { get; set; }

        public Guid? CustomerId { get; set; }
    }
}

Add a new controller class with name "JobsController.cs", and put the following code in.

namespace JobVirtualEntity.Controllers
{
    public class JobsController : ODataController
    {
        private bool ProductExists(Guid key)
        {
            return getJobs().Any(p => p.JobId == key);
        }

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
        }

        [EnableQuery]
        public IQueryable<Job> Get()
        {
            return getJobs();
        }

        [EnableQuery]
        public SingleResult<Job> Get([FromODataUri] Guid key)
        {
            IQueryable<Job> result = getJobs().Where(p => p.JobId == key);
            return SingleResult.Create(result);
        }

        private IQueryable<Job> getJobs()
        {
            List<Job> list = new List<Job>();

            list.Add(new Job
            {
                JobId = Guid.Parse("a58ba76a-29e9-44be-93e7-b18672488214"),
                JobName = "Job 1",
                ScheduledStart = DateTime.Parse("2018-10-14"),
                CustomerId = Guid.Parse("99DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("86aa6981-2133-4ed1-aed2-31ded383966c"),
                JobName = "Job 2",
                ScheduledStart = DateTime.Parse("2018-10-20")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("23648105-2422-4bc6-b526-73e68196e29b"),
                JobName = "Job 3",
                ScheduledStart = DateTime.Parse("2018-10-22")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("e1af24d6-b551-47b2-a6a4-3cda7d57b162"),
                JobName = "Job 4",
                ScheduledStart = DateTime.Parse("2018-10-28"),
                CustomerId = Guid.Parse("99DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("8245a766-2c5c-420a-9aae-dd282dcb5e05"),
                JobName = "Job 5",
                ScheduledStart = DateTime.Parse("2018-10-30"),
                CustomerId = Guid.Parse("99DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("3e8a9f43-ec67-49c0-8049-b8b553055640"),
                JobName = "Job 6",
                ScheduledStart = DateTime.Parse("2018-11-05")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("55d96076-a583-4486-89cd-840b22d97b2d"),
                JobName = "Job 7",
                ScheduledStart = DateTime.Parse("2018-11-10")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("740e809e-4b1d-429c-b025-109540e71bcf"),
                JobName = "Job 8",
                ScheduledStart = DateTime.Parse("2018-11-15")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("5dc300f9-fe76-4941-a920-8521c0fb9ca6"),
                JobName = "Job 9",
                ScheduledStart = DateTime.Parse("2018-11-20")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("86b61b8c-d95e-4435-bd63-636ba90a621b"),
                JobName = "Job 10",
                ScheduledStart = DateTime.Parse("2018-11-22"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("d30dcb70-6c32-45c1-8519-2b951ce00a98"),
                JobName = "Job 11",
                ScheduledStart = DateTime.Parse("2018-11-26")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("44e8ff63-e7f6-4b12-8a8b-64c3233230d4"),
                JobName = "Job 12",
                ScheduledStart = DateTime.Parse("2018-11-30")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("b6e9ee72-970e-48dc-9550-62891aad55c0"),
                JobName = "Job 13",
                ScheduledStart = DateTime.Parse("2018-12-03"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("e9af6be9-37ce-4df1-bcb7-5789b97320f3"),
                JobName = "Job 14",
                ScheduledStart = DateTime.Parse("2018-12-06"),
                CustomerId = Guid.Parse("99DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("a80d8be2-fde9-49ee-acc8-22971d90e29a"),
                JobName = "Job 15",
                ScheduledStart = DateTime.Parse("2018-12-10")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("94cf76eb-d0a4-4a5e-99c5-704f0df34516"),
                JobName = "Job 16",
                ScheduledStart = DateTime.Parse("2018-12-14")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("81dd3cad-e2ef-4f3d-b240-946a23e6440a"),
                JobName = "Job 17",
                ScheduledStart = DateTime.Parse("2018-12-16"),
                CustomerId = Guid.Parse("A1DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("946695ca-e3d7-4e71-841d-37111bfb8f54"),
                JobName = "Job 18",
                ScheduledStart = DateTime.Parse("2018-12-19")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("97770787-1ac3-45e5-b8a3-9cb8df98ffca"),
                JobName = "Job 19",
                ScheduledStart = DateTime.Parse("2018-12-20"),
                CustomerId = Guid.Parse("A1DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("62b68958-69d0-480d-94e9-d0718726485e"),
                JobName = "Job 20",
                ScheduledStart = DateTime.Parse("2018-12-21"),
                CustomerId = Guid.Parse("A1DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("43e71551-92bd-438b-9c2f-19ba9ce26c3a"),
                JobName = "Job 21",
                ScheduledStart = DateTime.Parse("2018-12-25")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("08268c7c-6bf6-4e55-9632-4abeb14c0786"),
                JobName = "Job 22",
                ScheduledStart = DateTime.Parse("2018-12-25")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("a124cba7-ec67-45ce-abc7-0278fce7e0ef"),
                JobName = "Job 23",
                ScheduledStart = DateTime.Parse("2018-12-29")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("7f112e12-3041-4ebf-a47d-128816822c52"),
                JobName = "Job 24",
                ScheduledStart = DateTime.Parse("2018-12-30"),
                CustomerId = Guid.Parse("A1DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("2d5fed58-aafb-4e1b-b71c-d51e5ceafa1a"),
                JobName = "Job 25",
                ScheduledStart = DateTime.Parse("2018-12-31"),
                CustomerId = Guid.Parse("A1DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("7e082dd1-79ed-4cdb-b83e-06283796a2f7"),
                JobName = "Job 26",
                ScheduledStart = DateTime.Parse("2019-01-05"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("37eff45e-fb1d-4621-99db-fa9a1ede3616"),
                JobName = "Job 27",
                ScheduledStart = DateTime.Parse("2019-01-09"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("6bd74176-9206-499a-9471-4e4c2c8e2681"),
                JobName = "Job 28",
                ScheduledStart = DateTime.Parse("2019-01-12")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("54839eea-c7fc-4144-b081-8d30b3b2e0d0"),
                JobName = "Job 29",
                ScheduledStart = DateTime.Parse("2019-01-16"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            list.Add(new Job
            {
                JobId = Guid.Parse("77981dfe-d3ed-4a37-8098-eeb2dde1e4a3"),
                JobName = "Job 30",
                ScheduledStart = DateTime.Parse("2019-01-21"),
                CustomerId = Guid.Parse("95DCA5C9-A4AC-E811-A871-000D3AD07131")
            });

            return list.AsQueryable();
        }
    }
}


Update the WebApiConfig.cs file with the following code.


namespace JobVirtualEntity
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services

            // this line is to allow query methods.
            config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);

            ODataModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Job>("Jobs");

            config.MapODataServiceRoute(
                routeName: "ODataRoute",
                routePrefix: null,
                model: builder.GetEdmModel());

            config.EnsureInitialized();
        }
    }
}


That's all the code you need for an OData v4 Web API to work with virtual entity in Dynamics 365. As you might noticed, I hard coded the job data being returned. In real-life, replace it with your own data source.

Now you can build the project and deploy to Azure App Services, or any other platforms, as long as your Dynamics 365 server can access it.

After it is deployed, open a browser and navigate to the URL, such as https://*****app.azurewebsites.net. You should get the following.

And if you click the @odata.context Url, you should get the following.


In the next post, we will create the virtual entity in Dynamics 365 and use this Web API as data source.

Dynamics 365 v9 Virtual Entity using OOB OData v4 Provider - PART 2

Comments

Post a Comment

Popular posts from this blog

Dynamics 365 sub-grid add new and add existing

Image
In Dynamics 365, if you put a sub-grid to display related records in a parent, there is always a plus (+) sign to allow user either adding a new child record or an existing record. The default behavior when user clicks the plus (+) sign is to display a lookup box, user can then click the lookup to drop down a list of child records or click the New button at the bottom of the drop down list. However, sometimes we may want to force the plus (+) sign to open the new record form straight away without displaying the lookup drop down to users. For example, we have a parent entity called Project, which is a custom entity. And we create a second custom entity called Service Contract, which has N to 1 relationship to Project. The project form looks like below. For the purpose of the demo, we say the requirement is to always open the new service contract form when user clicks the plus (+) sign. The easiest way to do this is to change the Project lookup field in the Service Con...
Read more

Dynamics 365 Web API get entity using alternate key value that has apostrophe/single quote in it

In Dynamics 365, you can create alternate key for an entity using one of the attributes. Entity can be retrieved using alternate key via Web API. This MSDN article gives you an example on how to do that:  https://msdn.microsoft.com/en-us/library/mt607871.aspx#BKMK_UsingAltKeys I had an interesting error today when performing a query using alternate key. I tried to get an entity record from the User entity (systemusers) using an alternate key that is configured in the User entity. The alternate key uses the internalemailaddress attribute, which contains users' email addresses. A user's email address is firstname.O'lastname@domain.com, and as you can see, there is an apostrophe/single quote in the email address. When performing the query via Web API to get the user record, you can construct the below Url and simply paste into the browse to see the JSON response. In this case, the Url is: https://organization.crm6.dynamics.com/api/data/v8.2/systemusers(internalemailadd...
Read more

TLS 1.2 and PowerShell

To view security protocol that your PowerShell currently uses, use the below command: [Net.ServicePointManager]::SecurityProtocol By default, PowerShell doesn't use TLS 1.2. This causes some problems if you use PowerShell a lot to work with Dynamics 365 and other popular SaaS applications, due to more and more applications are now forcing TLS 1.2 for incoming connections. Taking Dynamics 365 as an example, you may now keep getting the error message "More then one org was returned, retry with an exact org name" when using Get-CrmConnection PowerShell command. This is due to Dynamics 365 now only accepts TLS 1.2 connections, and your PowerShell is not forcing that. To work around it, put the below PowerShell command before making the connection: [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]::Tls12; Just like doing the same in C# code to force the connection to use TLS 1.2, but this is in PowerShell. The Get-CrmConnection ...
Read more