LINQPad 4 Driver for Dynamics CRM REST/Web API are available on CodePlex


I am pleased to announce that we released “LINQPad 4 Drive for Dynamics CRM REST and Web API (Preview)” on CodePlex.

For REST: https://crmlinqpadrest.codeplex.com/
For Web API (Preview): https://crmlinqpadwebapi.codeplex.com/

If you are interested in SOAP endpoint driver, please refer to previous blog.
LINQPad 4 Driver for Dynamics CRM is available on CodePlex

What’s LINQPad?

LINQPad (http://www.linqpad.net/) is a great tool which you can write and execute LINQ query against many data sources, like SQL Server, Oracle or Web Services. In addition to LINQ query support, the tool also supports C#/F#/VB expression, statement block or program, to write and execute the code and code snippet.

What can you do with CRM Driver?

By using CRM Driver, you are able to run LINQ queries against Microsoft Dynamics CRM REST/Web API endpoints on the fly. Follow the steps below to try it out!

Install Drivers to LINQPad

1. Download and install LINQPad if you don’t have it yet from http://www.linqpad.net/.

2. Go to REST Driver and Web API Driver and click “DOWNLOADS” tab, then download CRMLinqPadDriverREST.lpx and CRMLinqPadDriverWebAPI.lpx file.

3. Open LINQPad and click “Add connection” link on top left.

image

4. Click “View more drivers…” button at the bottom of the page.

image

5. Click “Browse” button in the bottom of the page.

6. Select downloaded lpx file, then click “Open”.

7. Click OK.

image

8. Do the same for another driver.

Use the REST Driver

Firstly, I explain how to use REST driver.

Create Connection

1. Select “Dynamics CRM REST Linq Pad Driver” in Choose Data Context and click “Next”

image

2. If you want to use your own ClientId, then uncheck “Register to Azure AD automatically” I will explain what it is later in this article. Click “Login to CRM”. If you want to run query against On-Premise server, (including IFD), you can leave the option as it is.

image

3. Login to your Organization at login screen.

image

4. Then it automatically starts doing its work and generates a DataContext from the selected organization. Wait until it’s done.

5. Click “Exit” button once loading completed. Then LINQPad starts loading the schema, which takes a bit of time. Wait until you see the schema information on the left pane like below screenshot.

image

Write LINQ query and Execute

1. Firstly, select added connection from “Connection” dropdown on the top right.

image

2. Enter following query to query window.

image

3. Click “Play” button or press F5 key to execute query. You will see the result in result pane.

4. Click SQL tab in result pane, where you can find OData query for REST endpoint.

image

Use the Web API (Preview) Driver

Next, Web API Driver. Please note that Web API is still preview and you have to use Dynamics CRM Online organization which 2015 Update 1 is applied and the feature is enabled.

Enable Web API (Preview)

1. Login to Dynamics CRM Online and navigate to Settings | Administration | System Settings.

2. Click “Previews” tab and enabled Web API.

image

Create Connection

1. Open LINQPad and click “Add Connections”. Select “Dynamics CRM Web API Linq Pad Driver” in Choose Data Context and click “Next”

image

2. If you want to use your own ClientId, then uncheck “Register to Azure AD automatically” I will explain what it is later in this article. Click “Login to CRM”. Login to the organization where you enabled Web API preview.

3. Then it automatically starts doing its work and generates a DataContext from the selected organization. Wait until it’s done.

4. Click “Exit” button once loading completed. Then LinqPad starts loading the schema, which takes a bit of time. Wait until you see the schema information on the left pane like below screenshot.

image

Write LINQ query and Execute

1. Firstly, select added connection from “Connection” dropdown on the top right.

image

2. Enter following query to query window.

image

3. Click “Play” button or press F5 key to execute query. You will see the result in result pane.

4. Click SQL tab in result pane, where you can find OData query for Web API endpoint.

image

Write Statements and Execute

1. To try functions, select “C# Statements” from Language and write Query like below.

image

2. SQL tab gives you OData query, too.

image

3. In addition to simple function, you are able to try more complex statements like below. This sample code creates an account, and create an opportunity by referecing the account as its parent.

// Create Account record.
DataServiceCollection<account> accounts = new DataServiceCollection<account>(this);
account myaccount = new account();
accounts.Add(myaccount);
// Specify field values
myaccount.name = “Test Account”;
// Then call SaveChanges to create it.
this.SaveChanges(SaveChangesOptions.PostOnlySetProperties);

// Create Opportunity record.
DataServiceCollection<opportunity> opportunities = new DataServiceCollection<opportunity>(this);
opportunity myopportunity = new opportunity();
opportunities.Add(myopportunity);
// Specify field values
myopportunity.name = “Test Opps”;
// Assign the account
myopportunity.opportunity_customer_accounts = myaccount;

this.SaveChanges(SaveChangesOptions.PostOnlySetProperties);

Console.WriteLine(“Opportunity Created”);

Azure AD Application Registration

For authentication, Dynamics CRM Online REST based web service endpoints only support OAuth 2. To use OAuth 2.0, you need to register your application (in this case, LINQPad driver) to Azure AD or AD FS (depending on your deployment), which typically requires you to perform manual work. (See Walkthrough: Register a CRM app with Active Directory for more details). To eliminate this manual work, these drivers have a built-in Azure AD Application Registration feature. If you are interested in how to do this, please see the source code.

By default, it uses your CRM login credential to register the application. However if the credential does not have enough privilege, you will be prompted to enter another credential. If you do not know about this and other people managing your Azure AD, you can ask them to register your application and get the ClientId. Once you get the ClientId, you can use it when adding the connection. Uncheck “Register to Azure AD” checkbox and enter ClientId manually.

image

This tool registers your application as follows. If the same application name already exists, then it reuses existing ClientId.

image

If you are using On-Premise Server for REST endpoint, the driver uses Windows Integrated Authentication, so it is skipping application registration part.

Ken
Premier Mission Critical/Premier Field Engineer 
Microsoft Japan


Comments (4)

  1. Nelson Johnson says:

    I tried to connect to CRM Online where I have a sandbox so I chose to display organizations. after I choose the organization, I am prompted to login to O365, and when that fails, I get into an endless loop of a dialog that says "you need to login as Company Admin or enter clientid manually"

  2. Thanks for your comment,

    This tool does register the application to Azure AD and you need to sign in as user who has Azure AD rights, which is usually signup user for Dynamics CRM. Could you try by signup new trial instance to see if it works?

    Ken

  3. LarsM says:

    This is a great initiative, but after having logged in, and pulled down the full list of Entities, I try a simple Take(100) on Accounts, and the following error:

    0234 The type or namespace name 'Description' does not exist in the namespace 'System.ServiceModel'  (Press F4 to check imported namespaces)

    Ideas?

  4. Thanks for feedback. It depends Endpoint if it supports Take(100).

    Could you let me know which driver of LINQPad you tried and which version of CRM you connected to?

    Ken

Skip to main content