Tag Archives: LINQ

Batch updating fields with crmsvcutil generated code

Scenario:

“I love early bound code because of compile time checking, LINQ query enablement, etc.  I want to batch update a bunch of records, but OrganizationServiceContext.SaveChanges() executes under the hood as one web service call per entity.  How do I batch update?  Also, how do I make sure that only the fields I change are updated?”

The answer is that you basically have to combine your crmsvcutil.exe generated code with ExecuteMultipleResults.  Here’s an example:

var conn = CrmConnection.Parse("USE A VALID connection string using appropriate format documented at https://msdn.microsoft.com/en-us/library/jj602970.aspx");
var ctx = new marcsctest1Context(new OrganizationService(conn));

// NOTE: The query below uses LINQ projection.  The CRM LINQ provider will translate this to a query that only returns ID & Name
var query = 
    from a in ctx.AccountSet
    select new Account
    {
        Id = a.Id,
        Name = a.Name
    };

var executeMultipleRequest = new ExecuteMultipleRequest()
{
    Settings = new ExecuteMultipleSettings(){
        ContinueOnError = false,
        ReturnResponses = true
    },
    Requests = new OrganizationRequestCollection()
};

foreach (var account in query)
{
    account.Name += " - Updated";
    //UpdateObject is required to set the EntityState to Changed because it is readonly through crmsvcutil generated entities
    ctx.UpdateObject(account); 

    executeMultipleRequest.Requests.Add(new UpdateRequest()
    {
        Target = account
    });
}

//Don't forget to check response.IsFaulted and handle appropriately
var response = ctx.Execute(executeMultipleRequest) as ExecuteMultipleResponse;

@devkeydet

Using LINQPad with the CRM Online OData service

I’ve been a longtime fan of LINQPad (www.linqpad.net) as a general purpose tool for executing LINQ queries with various LINQ enabled technologies as well as a general purpose scratchpad for testing out .NET code without having to fire up Visual Studio.  I’ve already blogged about how to use LINQPad to work with the CRM Online SOAP service:

http://blogs.msdn.com/b/devkeydet/archive/2012/12/04/linqpad-crm-2011-and-using-office-365-accounts.aspx

One thing that may not be obvious is how to use LINQPad to query the OData service that comes with Dynamics CRM Online.  Using the same basic concept that I showed with the SOAP service, I created a video walkthrough of how to interact with the OData service from LINQPad:

 

Here’s the LINQPad starter code I showed in the video:

var clientId = "[YOUR_CLIENT_ID]";
var resource = "https://[YOUR_INSTANCE].crm.dynamics.com";
var redirectUri = new Uri("http://linqpad"); //replace with your redirect uri

var authContext = new AuthenticationContext("https://login.windows.net/common/oauth2/authorize", false);
var authResult = authContext.AcquireToken(resource, clientId, redirectUri, PromptBehavior.Auto);
Util.ClearResults();

var ctx = new marcsctest1Context(new Uri(resource + "/XRMServices/2011/OrganizationData.svc/"));

ctx.SendingRequest2 +=
 (o, eventArgs) => eventArgs.RequestMessage.SetHeader("Authorization", "Bearer " + authResult.AccessToken);

var query = 
     from a in ctx.AccountSet
     where a.Name.Contains("sample")
     select new
     {
          a.AccountId,
          a.Name,
          a.Address1_City,
          a.Address1_StateOrProvince
     };

query.ToString().Dump("OData Query Url");
query.Dump("Query Results");

One of the things I did, after creating the video, was to write a reusable helper method in the LINQPad MyExtensions class so I can reuse the code to get the access token across multiple LINQPad query sessions.  Here’s the code for the extension:

void Main()
{
     // Write code to test your extensions here. Press F5 to compile and run.
}

public static class MyExtensions
{
     // Write custom extension methods here. They will be available to all queries.
     static string LINQPadAccessToken = null;
     
     public static string GetAccessToken()
     {
          if (LINQPadAccessToken != null)
          {
               return LINQPadAccessToken;
          }
          
          var clientId = "[YOUR_CLIENT_ID]";
          var resource = "https://[YOUR_INSTANCE].crm.dynamics.com";
          var redirectUri = new Uri("http://linqpad"); //your redirect uri

          var authContext = new AuthenticationContext("https://login.windows.net/common/oauth2/authorize", false);
          var authResult = authContext.AcquireToken(resource, clientId, redirectUri, PromptBehavior.Auto);
          Util.ClearResults();
          
          LINQPadAccessToken = authResult.AccessToken;
          
          return LINQPadAccessToken;
     }     
}

Here’s a version of the code in my query tab that is simplified, after writing and leveraging the helper method:

var ctx = new marcsctest1Context(
     new Uri("https://[YOUR_INSTANCE].crm.dynamics.com/XRMServices/2011/OrganizationData.svc/")
);
ctx.SendingRequest2 += 
     (o, eventArgs) => eventArgs.RequestMessage.SetHeader("Authorization", "Bearer " + MyExtensions.GetAccessToken());

var query =      
     from a in ctx.AccountSet
        where a.Name.Contains("sample")
     select new
     {
          a.AccountId,
          a.Name,
          a.Address1_City,
          a.Address1_StateOrProvince
     };

query.ToString().Dump("OData Query Url");
query.Dump("Query Results");

Note the call to MyExtensions.GetAccessToken() above.  You could obviously refactor the GetAccessToken() method more to make it even more reusable, but I will leave that as an exercise for you.

This approach will become even more powerful once Dynamics CRM introduces the new OData v4 service endpoint:

https://msdn.microsoft.com/en-us/dynamics/crm/webapipreview

As I mention in the video, once that happens, you’ll want to perform the codgen with the OData Client Code Generator instead of “Add Service Reference”:

http://blogs.msdn.com/b/odatateam/archive/2014/03/11/how-to-use-odata-client-code-generator-to-generate-client-side-proxy-class.aspx

@devkeydet

LINQPad, CRM 2011, and using Office 365 accounts

I recently discovered that the LINQPad Plugin for Microsoft Dynamics CRM 2011 doesn’t support authenticating using Office 365 credentials.  According to commenter EdWells, you can copy the latest version of crmsvcutil.exe and microsoft.xrm.sdk.dll over the version that ships with the LINQPad plugin as a workaround.  However, this didn’t work for meSad smile.  Have no fear, LINQPad allows you to reference external dlls.  So I just manually ran crmsvcutil.exe, compiled the output into an assembly using Visual Studio, and then referenced the dll + appropriate dependent assemblies.

clip_image002

You add References/Imports via the properties dialog:

clip_image004

MsftIanImportTest.dll is the assembly I put the crmsvcutil.exe code in.

clip_image006

Here’s a basic example to get it all wired up:

clip_image008

UPDATE: I use LINQPad as a way to do things like update multiple records, etc.  I have a tip to improve execution time performance here.

@devkeydet

Getting FetchXML from LINQPad

UPDATE: There’s a known issue with the LINQPad Plugin for Microsoft Dynamics CRM 2011 and Office 365 authentication.  I cover a workaround here.

Using FetchXML is necessary in many situations with CRM 2011.  Using FetchXML becomes even more necessary if you are taking a “design for online” approach to solution building.  I’m a big believer that everyone should approach solution design this way because then your solution will truly be portable between CRM Online and CRM OnPrem (i.e. the CRM you install yourself).  Typically, the need for FetchXML shows up first when you are building custom SQL Server Reporting Services (SSRS) reports.  That’s because FetchXML based reports are the only option in CRM Online.  I mentioned in my CRM Online myth busters post that I prefer to use LINQPad + LINQPad Plugin for Microsoft Dynamics CRM 2011 to compose my queries, then convert them to FetchXML.  This post is a walkthrough of how I do it.  I got the inspiration for the code sample I will present from the following two samples:

http://dkdt.me/JZiDsr

http://dkdt.me/JZiBAO

Once you’ve installed LINQPad + LINQPad Plugin for Microsoft Dynamics CRM 2011, you need to add some code to the “My Extensions” file in the “My Queries” pane:

image

Place the code I provide you below within the MyExtensions class under the comment that says to “write custom extension methods here.”  Here’s what the file looks like before you’ve added anything to it:

void Main()

{

    // Write code to test your extensions here. Press F5 to compile and run.

}

 

public static class MyExtensions

{

    // Write custom extension methods here. They will be available to all queries. 

}

 

// You can also define non-static classes, enums, etc.

Here’s the code you need to add under the comment:

public static string ToFetchXml(this IQueryable linqQuery, dynamic orgService)

{

    var queryExpression = GetQueryExpression(linqQuery);

 

    var expressionToFetchXmlRequest = new Microsoft.Crm.Sdk.Messages.QueryExpressionToFetchXmlRequest

    {

     Query = queryExpression

    };

 

    var organizationResponse = (Microsoft.Crm.Sdk.Messages.QueryExpressionToFetchXmlResponse)orgService.Execute(expressionToFetchXmlRequest);

 

    return organizationResponse.FetchXml;

}

 

private static Microsoft.Xrm.Sdk.Query.QueryExpression GetQueryExpression(IQueryable linqQuery)

{

    object projection = null;

    object source = null;

    object linkLookups = null;

    bool flag = false;

    bool flag2 = false;

 

    object[] arguments = new object[6];

    arguments[0] = (object)linqQuery.Expression;

    arguments[1] = (object)flag;

    arguments[2] = (object)flag2;

    arguments[3] = (object)projection;

    arguments[4] = (object)source;

    arguments[5] = (object)linkLookups;

 

    Microsoft.Xrm.Sdk.Query.QueryExpression query = (Microsoft.Xrm.Sdk.Query.QueryExpression)linqQuery.Provider.GetType().InvokeMember("GetQueryExpression", BindingFlags.InvokeMethod | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, linqQuery.Provider, arguments);

    return query;

}

In addition to adding the code, you need to add references to a few .NET assemblies.  From within the “My Extensions” file, press the F4 key to bring up the properties dialog.  Add references to the following assemblies (note your location for CRM 2011 SDK assemblies might be different):

image

Now, once you’ve written and executed the query that you want in LINQPad:

image

…you can then wrap the query in parenthesis and call the ToFetchXml() extension method to get the FetchXML:

image

BAM!  This approach has really helped me be productive in composing FetchXML queries.  I use this mostly when writing SSRS reports, but also find it useful for other occasions where I need to use FetchXML.  See the LINQ Limitations section of the Use LINQ to Construct a Query documentation for more details.

@devkeydet

OQuery – A fluent API to build OData url queries sans LINQ

Yesterday, I blogged about a solution to compose OData / WCF Data Service queries using LINQ for situations where LINQ enabled client libraries don’t exist (i.e. JavaScript and Windows Phone 7).

http://blogs.msdn.com/b/devkeydet/archive/2011/02/02/data-services-odata-client-for-windows-phone-7-and-linq.aspx

The post is all about using LINQPad as a tool to write your LINQ queries, then using the feature in LINQPad that gives you the url query translation.  Well today, a new MSDN Code Callery project just popped up called OQuery that offers another approach that doesn’t require using an external tool such as LINQPad:

http://code.msdn.microsoft.com/oquery

Here’s the description from the project page:

“OQuery is a library which gives you a fluent style interface for building OData Url Fragments in javascript or C#.
Neither Javascript or Silverlight for WP7 support LINQ and so this library in those cases.”

What are you waiting for?  Go check it out!

Follow devkeydet on Twitter

Data Services (OData) Client for Windows Phone 7 and LINQ

 

The WCF Data Services Team released a client library to make it easier to interact with OData services from Windows Phone 7. 

http://bit.ly/wp7odatalib

The major caveat (and bummer) with the library is that “LINQ support in the client library has been removed as the core support is not yet available on the phone platform.”  I have a tip/trick for LINQ lovers like me that will allow you to still use LINQ query syntax to compose your query (kind/sorta).  A tool I find useful to when writing OData queries on Windows Phone 7 is LINQPad.  I use the tool to write LINQ queries against my data service, then get the url syntax query translated for me:

clip_image002

Once I get my LINQ query right, I just paste the http string into my app.  I’m still surprised about how many people aren’t aware of LINQPad.  You must check it out if you use LINQ in your day to day coding (which I am sure most of you do now).  You can learn more about using LINQPad against OData services (amongst many other uses) from the product site:

http://www.linqpad.net/

NOTE: This tip/trick is very useful when querying OData services from JavaScript as well.  Here are two helpful posts on using jQuery to interact with an OData service:

http://stephenwalther.com/blog/archive/2010/04/01/netflix-jquery-jsonp-and-odata.aspx

http://stephenwalther.com/blog/archive/2010/03/30/using-jquery-and-odata-to-insert-a-database-record.aspx

Hope this helps save you some time!

Follow devkeydet on Twitter

DevDinnerOnDemand: Overview of the .NET Framework 3.5 SP1

digg_url = “http://blogs.msdn.com/devkeydet/archive/2008/11/10/devdinnerondemand-overview-of-the-net-framework-3-5-sp1.aspx”;digg_title = “DevDinnerOnDemand: Overview of the .NET Framework 3.5 SP1”;digg_bgcolor = “#555555”;digg_skin = “normal”;http://digg.com/tools/diggthis.jsdigg_url = undefined;digg_title = undefined;digg_bgcolor = undefined;digg_skin = undefined;

With the .NET Framework 3.5 (the version native to Visual Studio 2008) coming up on its first anniversary, Microsoft is poised to release the first update to the framework in the form of Service Pack (SP1).  This Service Pack is unlike your standard Service Pack, in that it will introduce new features/capabilities to the .NET Framework.  Some of these features where originally planned to be in the initial release of the framework and others are features/capabilities added to enhance or further secure the core .NET Framework.  The August Developer Dinner is going to focus on some of the new features as well as a few of the new enhancements, to give you an introduction of the improved capabilities of the Microsoft Developer Platform.

What you will learn:

This evenings presentation will be a running stream of demonstration focusing on new features and functionality coming in the new Service Pack for several of the key areas of .NET Development today, including Web Development (ASP.NET), Database Development (ADO.NET), Web Services/SOA (Windows Communication Foundation) and User Experience (Windows Presentation Foundation).

You will see demonstrations that will include:

  • Making data access easier with the ADO.NET Entity Framework.
  • Exposing your data access layer using ADO.NET Data Services
  • Building “Data Entry” Web-based applications faster than ever using ASP.NET Dynamic Data.
  • Making AJAX Applications faster with script combining and easier with built in support for handling browser history (i.e. back/forward buttons).
  • How to achieve up to 40% faster startup performance for your WPF applications and further improve the startup experience using a splash screen.
  • Reducing the time it takes to deploy your WPF applications using the New .NET Framework Client Profile.
  • Build services faster using enhancements in WCF.

View Recording:

https://channel9.msdn.com/posts/keydet/US-Public-Sector-Developer-Dinner-for-Partners-NET-Framework-35-SP1/

The follow up post for this developer dinner is http://blogs.msdn.com/devkeydet/archive/2008/08/21/follow-up-developer-dinner-on-net-framework-3-5-sp1.aspx.