Install-Package NY.Dataverse.LINQPadDriver -Version 1.6.9
dotnet add package NY.Dataverse.LINQPadDriver --version 1.6.9
<PackageReference Include="NY.Dataverse.LINQPadDriver" Version="1.6.9" />
paket add NY.Dataverse.LINQPadDriver --version 1.6.9
#r "nuget: NY.Dataverse.LINQPadDriver, 1.6.9"
// Install NY.Dataverse.LINQPadDriver as a Cake Addin #addin nuget:?package=NY.Dataverse.LINQPadDriver&version=1.6.9 // Install NY.Dataverse.LINQPadDriver as a Cake Tool #tool nuget:?package=NY.Dataverse.LINQPadDriver&version=1.6.9
Dataverse Driver for LINQPad 6
The original LINQPad Driver for Dynamics CRM (as Dataverse was known back then) was written by Kenichiro Nakamura. It used the SOAP endpoint along with CrmSvcUtil to generate the early bound classes needed to show the entities in LINQPad. It was a static driver, meaning that you had to regenerate the context if you had new metadata e.g. new fields, entities etc.
This new driver is a dynamic driver that uses Microsoft.PowerPlatform.Dataverse.Client assemblies which target .NET Core. The Tables (Entities) and associated metadata are regenerated everytime LINQPad is opened, so that you don't need to worry about keeping Dataverse Metadata and LINQPad context in sync.
The Microsoft.PowerPlatform.Dataverse.Client package is still in alpha, so there might be some naming changes/bugs which you can log in this repo for me to triage. I can then either fix it in the driver, or report it on the Microsoft.PowerPlatform.Dataverse.Client repo, if it is not a driver code issue.
You can install the driver from LINQPad from nuget. Click on View more drivers, and then choose "Show all drivers" and also select the Include Prerelease checkbox. Search for Dataverse and you should be able to see the driver and install it.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/newconnection.png" width="1024" alt="View more drivers" />
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/install.png" width="1024" alt="Install" />
Connecting to your Dataverse Environment
Microsoft.PowerPlatform.Dataverse.Client supports three kinds of authentication:
- Application Id/Secret
- Application Id/Certificate Thumbprint
After installing the driver from nuget, you can start using this driver by clicking Add Connection link on LINQPad. You will be presented with the dialog below.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/connection%20details.png" width="1024" alt="Connection Details" />
You can then choose the appropriate connection method and enter the connection details. Scott Durow has as great video on YouTube on how to do this, which you can use to setup your Application Registration and create the Application User. The redirect URL in the Application registration has to be http://localhost
If you don't want to set up a Azure AD App Registration, you can use the AppId provided by Microsoft for development and testing purposes, which is 51f81489-12ee-4a9e-aaae-a2591f45987d. Choose OAuth as the authentication method along with this AppId, and you would be presented with the standard OAuth login prompt and LINQPad can use the generated token from the browser.
Running LINQ Query
After entering the required details on the connection dialog, the context would be generated and you should see all the tables on the left hand side.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/entities.png" width="1024" alt="Tables" />
You can either write a new LINQ query on the query window, or right click on the table name, to see some quick suggestions.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/queryoptions.png" width="1024" alt="Query Options" />
LINQPad has a whole bunch of samples on how to craft your LINQ queries, in case you don't know how to query in LINQ and want to learn the syntax. LINQ is very similar to SQL in syntax, but more powerful than SQL.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/samples.png" width="1024" alt="Samples" />
I have also given five samples that illustrate the power of LINQPad and how you can use the driver to query Dataverse.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/dataverse%20samples.png" width="1024" alt="Dataverse Sample" />
Calling Dataverse API
You can use DataverseClient property to access the CdsServiceClient object. Once you have access to this object you can then basically do any operations that are supported by the client.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/dataverseclient.png" width="1024" alt="Dataverse Client" />
Getting FetchXML from LINQ
Click on the SQL tab to see the corresponding FetchXML to the LINQ query.
<img src="https://raw.githubusercontent.com/rajyraman/Dataverse-LINQPad-Driver/master/images/linq%20to%20fetch.png" width="1024" alt="LINQ to FetchXML" />
- Mark Carrington for FetchXML to WebAPI URL conversion logic
- Kenichiro Nakamura for writing the original driver, that I still use
- Gayan Perara for CRM Code Generator, which was the first one to use T4 templates for generating early bound classes
- Jo Albahari for creating LINQPad
- People who helped me with testing - Tae Rim Han
This package is not used by any NuGet packages.
This package is not used by any popular GitHub repositories.
1. Update MarkMpn.FetchXmlToWebAPI submodule
2. Update to Microsoft.PowerPlatform.Dataverse.Client 0.5.10
3. Fix type for BooleanManagedProperty
4. Fix reference to Microsoft.Xrm.Sdk.dll