LINQPad + CRM = True

What is LINQPad? It is a scratchpad that instantly lets you evaluate and test code and expressions without the setup required for a big project (i.e. overhead). Ever needed to just see what a method returned in the .NET framework, or just test a regular expressions? How about querying a database and doing some aggregation with the results that SQL syntax just makes too complex? Go download LINQPad! It should be in your utility belt on every project where you touch code and databases. I have thrown my money at Joseph Albahari who created LINQPad and I think you should too (although it is free if you can manage without auto-completion).

https://www.linqpad.net/

I find LINQPad to be invaluable when working with CRM, and especially CRM online. In two-three lines of code, I have a connection to my CRM instance and can write queries, mix the data with SQL data from another source, and all without the overhead, runtime and "long life expectancy" of a project in Visual Studio. For CRM online it can give you back a lot of the control you lost from on-premise - an almost SQL like querying capacity and control of the data.

Now, demo time....

How would you solve the following examples in Visual Studio or SSMS, or both? How many lines of code? Sure, the examples can be solved without much work, but the LINQPad code below sure seems like a very minimal and viable approach for one time jobs and tests.

Give me all accounts in CRM that have field value X. For each of these, look up values Y using AccountId in a database, average the Ys found and update the account with the aggregate value using the web services.

// Connect to the DB (the LINQPad context is set to CRM)
var databaseContext = new MockDbml.MockTestDataContext("Data Source=.;Initial Catalog=Mock_Test;Integrated Security=True");

// Select and aggregate in one query
var valuesPerAccount = from dataPoint in databaseContext.Test_Datas  
                       group dataPoint by dataPoint.AccountId into g
                       select new {AccountId = g.Key, Average = g.Average(x => x.Value)};

// Update CRM with calculated values
foreach(var a in this.AccountSet.Where(i => i.IndustryCode == null))  
{
    var data = valuesPerAccount.FirstOrDefault(i=>i.AccountId == a.AccountId.Value);
    a.CreditLimit = new Money(data.Average);    
    UpdateObject(a);
}

this.SaveChanges();  

Run a regular expression check on a selected field for all contacts in CRM. Return the contact identifiers and fields that fail the test.

var results = from c in this.ContactSet.ToList()  
              where c.EMailAddress1 != null && !Regex.IsMatch(c.EMailAddress1, @"\A(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?)\Z")
              select new{c.ContactId, c.FullName, c.EMailAddress1};

results.Dump();  

Run a workflow for all accounts that match a criteria when there are less than 5000 accounts (if more, see usage of paging for retrieve requests).

var matchingAccounts = from a in this.AccountSet  
                       where a.ParentAccountId == null // TODO: insert more complex selector here
                       select a;

var workflowIds = from w in this.WorkflowSet  
                 where w.ActiveWorkflowId != null && w.StateCode.Value == WorkflowState.Activated && w.Name.Equals("Test Workflow")
                 select w.Id;                

var request = new ExecuteWorkflowRequest()  
                    {
                        WorkflowId = workflowIds.FirstOrDefault()                
                    };                            

foreach(var a in matchingAccounts)  
{
    try
    {
        request.EntityId = a.AccountId.Value;
        this.OrgServiceWrapper.Execute(request);
    }
    catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> ex)
    {
        (new{ex.Detail.Timestamp, ex.Detail.ErrorCode, ex.Detail.Message}).Dump();
    }
    catch(Exception e)
    {
        (new{e.Message, e.StackTrace, e.InnerException}).Dump();
    }
}

Some links for more reading:

• https://www.linqpad.net/CodeSnippetIDE.aspx
• http://www.devcurry.com/2011/08/50-linq-examples-now-in-linqpad.html
• http://dotnetsurfers.com/blog/2013/01/15/developer-tools-screencast-linqpad-part-1/
• http://dotnetsurfers.com/blog/2013/01/23/developer-tools-screencast-linqpad-part-2/

EndOfLine