Integrate Dynamics 365 CRM with .NET Using Entity Framework Core
There are various cases when you need to access Microsoft Dynamics 365 Sales data outside of the CRM in an external application. It might be for reporting purposes, an information dashboard, or integration with any data processing application.
ComponentOne provides a .NET Standard-based Dynamics 365 Sales data connector that you can use to access and update CRM data using known data access technologies such as ADO.NET or Entity Framework Core. In this article, we will discuss how to access data using Entity Framework Core, and we will see how to use LINQ to access and update a Dynamics 365 Sales entity.
The ComponentOne Data Connectors are included with the Service Components in the installer. Get the installer here.
Authenticating External Applications with Dynamics 365
Dynamics 365 supports OAuth authentication to access the common data service through which the CRM data is exposed. You will need to register an app with the Azure active directory to allow access to the data. Please refer to this tutorial to register your app and get OAuth credentials.
The ComponentOne Dynamics 365 data connector supports password credentials and the client credentials grant type. You can refer to the details about using both these grant types to authenticate in the documentation.
In this article, we will use the client credentials grant type to authenticate. Hence we need to get the following details beforehand:
- OAuth Token Endpoint: OAuth 2.0 token endpoint to be used for the authentication.
- OAuth Client Secret: The secret of the OAuth 2.0 Client to be used for authentication.
- OAuth Client Id: The id of the OAuth 2.0 Client to be used for the authentication. The client identifier issued to the client during the registration process.
- OAuth Extend Properties: This is designed to support any custom information users want to add to the connection string so that our library can obtain access token properly.
- We also need the Dynamics 365 instance URL
Next, we will create a .NET Core console application to demonstrate how to use Entity Framework Core to connect and access data.
Add Dependencies for ComponentOne Data Connectors
Next, add C1.EntityFrameworkCore.D365 package from the NuGet Gallery (nuget.org) to the project:
Create the Dynamics 365 Database Context Class
Now add a class inheriting from DbContext. In the code example below, I have created the ‘myD365CRM’ class with a constructor and OnConfiguring method.
public class myD365CRMContext : DbContext
{
public string ConnectionString;
public DbSet<Account> Accounts { get; set; }
public DbSet<Contract> Contracts { get; set; }
public myD365CRMContext(string connectionString):base()
{
ConnectionString = connectionString;
Database.AutoTransactionsEnabled = false;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseD365S(ConnectionString);
}
}
Add the Data Model
Now we need to create classes for the CRM entities which we want to access or update. In the code below, I have created model classes for the Account entity.
public class Account
{
[Key]
[Column("AccountId")]
public Guid AccountId { get; set; }
[Column("Name")]
public string Name { get; set; }
[Column("ExchangeRate")]
public decimal? ExchangeRate { get; set; }
[Column("ModifiedOn")]
public DateTimeOffset ModifiedOn { get; set; }
[Column("Address1_Composite")]
public string Address1_Composite { get; set; }
[Column("Description")]
public string Description { get; set; }
}
Create the Connection String to Microsoft Dynamics
The connection string can be configured as below using the URL, ClientSecret, ClientID, TokenEndpoint, and ExtendProperties that we have received from the CRM server.
const string urlDynamics = @"https://myD365CRM.api.crm8.dynamics.com/api/data/v9.1/";
const string ClientSecret = "password"
const string clientID = " 586e8e6f-7d54-4248-94a3-8257640737fa ";
const string tokenEnpoint = @"https://login.microsoftonline.com/common/oauth2/token";
const string extendProperties = @"{""resource"":""https://xxx.xxx.xxx.com/""}";
//Client credential in connection string
string connstr = $@"Url={urlDynamics};Use Etag=true;OAuth Client Id={clientID};OAuth Client Secret={cllentSecret};OAuth Token Endpoint={tokenEnpoint};OAuth Extend Properties={extendProperties};Max Page Size = 100";
The data connector supports caching data locally to enhance performance, details about configuring cache through connection can be found in documentation.
Access and Query the Data
Using the above-created connection string we can instantiate a new instance of myCRMDbContext in the Main method.
Now we are ready to query the Dynamics 365 Sales entities. In the code below, we use LINQ to get all Accounts where the Adddress_Composite field contains “NewYork”. Next we print the result.
var context = new myD365CRMContext(connstr)
var records =
from p in context.Accounts
where p.Address1_Composite.Contains("NewYork")
select p;
foreach (var account in records)
{
Console.WriteLine("{0} - {1} - {2} - {3} - {4}", account.AccountId, account.Name, account.Address1_Composite, account.ModifiedOn, account.Description);
}
Update the Data
How you update records through the ComponentOne Data Connectors is similar to Entity Framework. In the code below, we first get the Account record based on AccountId then set its name attribute to “GrapeCity”, finally we call the SaveChanges() method of context.
public void UpdateRecord()
{
var record = context.Accounts.Where(x => x. AccountId == "FA576e8e6f-7d54-4248-93a3-8257640737fa").FirstOrDefault();
record.name = "GrapeCity";
context.SaveChanges();
}
Insert and Delete Data
Inserting data is simple too. Just add the new record to the database context and then call the SaveChanges method.
Account account = new Account();
account.AccountId = new Guid();
account.Name = "Contosso Inc";
context.Add(account);
//Save changes to database
context.SaveChanges();
Deleting data also is also trivial. Just get the record using a unique key, delete it by calling Remove method, and finally call SaveChanges again.
Account account = context.Accounts.Where(x => x. AccountId == "FA576e8e6f-7d54-4248-93a3-8257640737fa").FirstOrDefault();
context.Accounts.Remove(account);
//Save changes to database
context.SaveChanges();
Using LINQ with ComponentOne Data Connectors
The ComponentOne Dynamics 365 data connector supports various LINQ queries. For example, we have already seen filter conditions like “where” and filtering can also be applied using “contains”. Additionally, the LINQ queries support returning a specified number of records using "take”, sorting using "orderby", and grouping using “group”. The data connector also supports “join” queries with LINQ. An example of getting results from multiple tables based on a common attribute is as follows:
using (var context = new myD365CRMContext())
{
var results = (from p in context.invoices
join e in context.invoicedetails on p.invoiceid equals e._invoiceid_value
join f in context.products on e._productid_value equals f.productid
where p.name == "Test"
select new
{
ID = p.invoiceid,
Name = p.name,
Invoicenumber = p.invoicenumber,
Productname = e.productname,
Productid_value = e._productid_value,
Productnumber = f.productnumber
}).ToList();
}
More details about LINQ queries can be found in the documentation.
More samples on using the data connectors are available at below location when you install the product:
~/Documents\ComponentOne Samples\ServiceComponents\DataConnectors\CS
Prabhakar Mishra