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 using the following features:
- Authenticating External Applications with Dynamics 365
- Add Dependencies for ComponentOne Data Connectors
- Create the Dynamics 365 Database Context Class
- Add the Data Model
- Create the Connection String to Microsoft Dynamics
- Access and Query the Data
- Update the Data
- Insert and Delete Data
- Using LINQ with ComponentOne Data Connectors
The ComponentOne Data Connectors are included with the Service Components in the installer.
Ready to Get Started? Download ComponentOne's 30-day Free Trial Now!
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 is 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 tokens 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, we 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
We need to create classes for the CRM entities which we want to access or update. In the code below, we 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 the 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 will 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 the 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 is also straightforward. Just get the record using a unique key, delete it by calling the 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 of using the data connectors are available at the location below when you install the product:
~/Documents\ComponentOne Samples\Data Services\DataConnectors\CS
Ready to Get Started? Download ComponentOne's 30-day Free Trial Now!
Related Blogs
Try Our .NET UI Components & Datagrid
Getting Started With ComponentOne
