Blog: Mr. Mathiesen

How to Daxif, delete all accounts (part 1)

March 23, 2016.

Background

A couple of weeks ago i overheard two of our consultants talking about deleting all the instance of an entity in a TEST environment. One of the two consultants is very skilled with F# so he just told the other one that he would create a script that would delete all the instances once he had the time to do it. The other one who is more in the role of a functional consultant, not to be confused with functional programming languages, wasn’t satisfied with the answer as he could see that if he wanted the job done here and now, he would have to rely on MS CRM user interface, which is to Advance Find the instances and delete them manually in batches of 250.

Note: The second approach actually happened, but only for a entity that had a few thousand instances. Still not optimal though.

So I thought to myself, well we already have the CrmData.CRUD module in Daxif, which we have kept internal to built higher order functions on top of it in order to implement some business logic. The point is that we moved the parts that we wanted to expose and we can now present two new modules for Daxif which are in the DG.Daxif.HelperModules.Common namespace:

  • CrmData.CRUD: Basic Create/Read/Update and Delete functions as well as the CRUD functionality but as Requests. Read How to Daxif, delete all accounts (part 2) for more info on why we also expose the Requests (next blog post).

  • CrmData.Metadata: Entity, Attributes and Relationship metadata.

Delete all accounts

At this point we assume that you already know how to setup Daxif. If not, please read the first blog post of How to Daxif, basic setup.

0) Pre-steps: Setup SDK Client/Proxy and create a lot of accounts

The first thing to do in your script is to create a target type with our XrmTypeProvider. The reason we use the TypeProvider when we work with data management is to avoid typing names of entities or attributes that doesn’t exist in MS CRM. For example if we look how Microsoft does something similar with their Microsoft.Xrm.Data.PowerShell module:

$accountId = New-CrmRecord -conn $conn -EntityLogicalName account -Fields @{"name"="Sample Account";"telephone1"="555-5555"}

It’s clear to see that the end user will have to either remember each logical attribute name for each of the entities or use some kind of copy/paste from another source.

Here is where the F# TypeProviders shine as they are able to create erased types, for non F#’ers, just replace erased-types with magick, on the fly from inside your IDE, in this case Visual Studio, ensuring that you are not typing an erroneous value. Another side-effect is that your script will not execute if somebody changes or deletes one of the attributes you are referring to as F# scripts are strongly typed, types are inferred before execution. Which means that you will have the F# compiler checking if everything complies before executing a single line, which is something I haven’t found in other scripting languages and in my book making it the best scripting language out there. It’s a Dane thing Janteloven, so I might have to express it like this instead: “Probably the best scripting language in the world”.

Now back to the example where we want to delete all the instances of our account entity. Once we have set up the TypeProvider, we will need to use the SDK Client in order to make the communications between our computer and the MS CRM server.

Note: As we will be using Parallelism, it’s important to instantiate all the proxies from the same Service Manager.

Finally, in order to delete, we must first create. Therefore I made a few functions for this. I created both a sequential and also a parallel version.

> Real: 00:00:21.414, CPU: 00:00:00.937, GC gen0: 0, gen1: 0, gen2: 0
> Real: 00:01:29.152, CPU: 00:00:07.656, GC gen0: 1, gen1: 1, gen2: 0

Note: It only takes 8.9 seconds to create 100 in parallel. It’s half the time of creating them sequential.

The final code snippet from this first step would look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

type target = XrmProvider<uri = cfg.wsdlDev, usr = cfg.usrDev, pwd = cfg.pwdDev>

// 0.a) Set up SDK Client
let auth = new AuthenticationCredentials()
do auth.ClientCredentials.UserName.UserName <- cfg.usrDev
do auth.ClientCredentials.UserName.Password <- cfg.pwdDev
let manager =
  ServiceConfigurationFactory.CreateManagement<IOrganizationService>
    (cfg.wsdlDev')
let token = manager.Authenticate(auth)
 
// 0.b) Define a SDK proxy function that is based on the Service Manager
//      (for parallelism purposes)
let proxy () = 
  let proxy' = new OrganizationServiceProxy(manager, token.SecurityTokenResponse)
  do proxy'.EnableProxyTypes()
  proxy'

// 0.c) Create a lot of accounts first
module Random =
  let private r = new Random()
  let bit () = r.Next(0,2).ToString()
  let hex () = r.Next(0,16).ToString("X")
  let number from ``to`` = r.Next(from,``to``).ToString()
let rand () = Random.number (1000*1000) (10*1000*1000) // 1.000.000 - 9.999.999

let account name = 
  let attribs = new AttributeCollection()
  attribs.Add(
    key = target.Metadata.Account.Name, 
    value = name)
  let entity = new Entity(entityName = target.Metadata.Account.``.LogicalName``)
  entity.Attributes.AddRange(attribs)
  entity

Seq.init 100 (fun _ -> account (rand())) // Slower
|> Seq.map(fun a -> CrmData.CRUD.create (proxy()) a (new ParameterCollection()))
|> Seq.iter(printfn "%A") // printfn isn't thread-safe

Array.init 1000 (fun _ -> account (rand())) // Faster with parallelism
|> Array.Parallel.map(fun a -> 
  CrmData.CRUD.create (proxy()) a (new ParameterCollection()))
|> Array.iter(fun guid -> Console.WriteLine(guid))

Output from evaluation the pre-step (ALT+ENTER):

--> Referenced 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.1\System.ServiceModel.dll' (file may be locked by F# Interactive process)

--> Referenced 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.1\System.Runtime.Serialization.dll' (file may be locked by F# Interactive process)

851910e8-d0f0-e511-80de-5065f38bc301
8a1910e8-d0f0-e511-80de-5065f38bc301
... (1097 other guids are omitted)
c90b1e17-d1f0-e511-80df-5065f38b6471

type target = DG.Daxif.XrmProvider<...>
val auth : Xrm.Sdk.Client.AuthenticationCredentials
val manager : Xrm.Sdk.Client.IServiceManagement<Xrm.Sdk.IOrganizationService>
val token : Xrm.Sdk.Client.AuthenticationCredentials
val proxy : unit -> Xrm.Sdk.Client.OrganizationServiceProxy
module Random = begin
  val private r : System.Random
  val bit : unit -> string
  val hex : unit -> string
  val number : from:int -> to:int -> string
end
val rand : unit -> string
val account : name:'a -> Xrm.Sdk.Entity

1) The Query (almost type-safe)

The query is just built with the SDK Query entity. Once again in order to make it almost type-safe we use our XrmTypeProvider:

1
2
3
4
5

let query = new QueryExpression(target.Metadata.Account.``.LogicalName``)
query.ColumnSet.AddColumn(target.Metadata.Account.AccountId)
let accounts () =
  CrmData.CRUD.retrieveMultiple
    (proxy()) target.Metadata.Account.``.LogicalName`` query

Output from evaluation the first step (ALT+ENTER):

val query : Xrm.Sdk.Query.QueryExpression
val accounts : unit -> seq<Xrm.Sdk.Entity>

2) Delete all the accounts (with helper module)

Now we can delete all the created accounts from the previous steps with the following snippet of code. I have created an expansion of the Sequence module in order to limit the chunks of data to be handled in parallel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

module internal Seq = // Helper module to split sequence for parallel purposes
  let split size source = 
    seq { 
      let r = ResizeArray()
      for x in source do
        r.Add(x)
        if r.Count = size then 
          yield r.ToArray()
          r.Clear()
      if r.Count > 0 then yield r.ToArray()
    }

accounts ()
|> Seq.split 1000
|> Seq.iter(fun xs -> 
  printfn "Chunks of 1000"
  xs
  |> Array.Parallel.map(fun a -> CRUD.delete (proxy()) a.LogicalName a.Id)
  |> Array.iter(fun guid -> Console.WriteLine(guid))
)

But it’s very easy to see that the deletion is done by:

1
2
3

  ...
  |> Array.Parallel.map(fun a -> CRUD.delete (proxy()) a.LogicalName a.Id)
  ...

where the script infers the type of a to be an Entity without having to specify it in the lambda function.

Output from evaluation the last step (ALT+ENTER):

> 
Chunks of 1000
00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
... (997 other guids are omitted)
00000000-0000-0000-0000-000000000000
Chunks of 1000
00000000-0000-0000-0000-000000000000
00000000-0000-0000-0000-000000000000
... (97 other guids are omitted)
00000000-0000-0000-0000-000000000000

module internal Seq = begin
  val split : size:int -> source:seq<'a> -> seq<'a []>
end
val it : unit = ()

Summary

The feedback from our functional consultant was that if I was able to hide some of the repetitive code (Client SDK and Proxy), he would be able to work with it and change the query as well as the CRUD operation.

Looking forward to receiving an e-mail where the consultant have made something useful based on the base script that I sent him. Actually, this is what is all about being a CTO or an Software Architect. You need to provide easy to use tools for others to use. And you know you are doing it right when you make non-developers (Business Analyst or Project Managers) execute and/or modify your scripts without your any help.

So for everybody out there, we all need developers, but what if you were able to invite other kind of people who normally don’t participate in this process by using something they can relate to? Think about that.

Note: There were no Account create functions or parallel helping modules in the snippet I sent to the consultant. These are pieces of code I added for this blog post in order to point out how ease it is to make things in F#.

You can see the full script in the following Gist:

Next time

In the second part of this blog post, I will go into why we expose the CRUD Requests functions. We can really increase performance if we combine them with MS CRM ExecuteMultiple. More on that next time.

More info:

References: