MS Dynamics 365 CRM online - dump entity

Question

I'm a newbie in Dynamics 365. We are using Dynamics 365 CRM online version. I need to dump a couple of entities to our local SQL server.

I've found many examples, but not with the entire entity (including lookup columns).

What is not working for me in this (most relevant) sample:

I need a list of all columns (Do I really need to specify the complete column list in new Contact{}?)

Get lookup values in result instead of IDs (guids)

             `var orgContext = new OrganizationServiceContext(service);
             // Retrieve the Contact records that we created previously.
             List<Contact> contacts = (from c in orgContext.CreateQuery<Contact>()
                                       where c.Address1_City == "New York"
                                       select new Contact
                                       {
                                           ContactId = c.ContactId,
                                           FirstName = c.FirstName,
                                           LastName = c.LastName
                                       }).ToList<Contact>();
             Console.Write("Contacts retrieved, ");'

Microsoft Dynamics 365 includes a whole line of ERP and CRM products, please be specific in indicating the one you are using. Many, if not all, have an online component/platform. Some products like Dynamics 365 Finance have built in support to export data entities to an external database (no custom code required). — Sander
The underlying Microsoft Dataverse (used to be named "Common Data Service" or "CDS") can also be leveraged to export data entities (source). This page lists an API that can be used if you want to code something yourself. — Sander
Our company uses a global on-line solution, with no chance to the admin part of Dynamics365. but we are allowed to code custom export code.. Sounds silly, but that the main set-up. I d love write service which uses CDS to dump (sync and delta sync) some Dynamincs Entities to local SQL server. What i really id love to find is a sample of entity (ideally Actitivity pointer) dump in c#. — Pavel Kohout

Arun Vinoth Arun Vinoth · Accepted Answer · 2020-12-07T23:40:40

Though what you’re doing is good practice, which is having defined list of attributes being mentioned in select list, you can select all the attributes by using below syntax. It’s worth to mention that this will have huge performance impact.

List<Contact> contacts = (from c in orgContext.CreateQuery<Contact>()
                                   where c.Address1_City == "New York"
                                   select c).ToList<Contact>();

Coming to your next question - getting lookup field, for that you can use join. For ex. Getting primary contact lookup from account using below syntax. This uses primary key and foreign key between entities to join.

var accountQuery = from account in context.CreateQuery("account")
                   join contact in context.CreateQuery("contact")
                   on account["primarycontactid"] equals contact["contactid"]
                   where account["name"] ==   "Some Company Name"
                    select new {
                           Name = account["name"],
                           Email = account["emailaddress1"],
                           Manager = contact
                      };

foreach (var account in accountQuery)
{
    Console.WriteLine(account.Name + " is run by: " + account.Manager["fullname"]);
}

MS Dynamics 365 CRM online - dump entity

2 Answers