LinqToSalesforce

Using F# type provider

For the moment, the NugGet is only on my personal MyGet feed, which can be used as a package source: https://www.myget.org/F/romcyber/api/v2

The Salesforce.TypeProvider library can be installed from MyGet: 
PM> Install-Package Salesforce.TypeProvider

In this tutorial we will simply try to generate basic stats of our salesforce accounts

Open module and namespaces

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 

open Deedle
open System
open FSharp.Charting
open XPlot
open XPlot.Plotly
open System
open System.IO
open System.Net
open LinqToSalesforce
open SalesforceProvider
open Rest
open Rest.OAuth

We must specify TLS protocol versions to avoid misterious Salesforce authentication errors.

(May cause some problems with MONO :( )

1: 

ServicePointManager.SecurityProtocol <- SecurityProtocolType.Tls12 ||| SecurityProtocolType.Tls11

We can use a JSON file containing authentication informations.

This json contains something like:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 

{
    "Clientid":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "Clientsecret":"xxxxxxxxxxxxxxxxxxxx",
    "Securitytoken":"xxxxxxxxxxxx",
    "Username":"login@mail.com",
    "Password":"xxxxxxxxxxxxx",
    "Instacename":"eu11" // or "login" or "test"
}

If you don't know how to get those infos, then follow this tutorial: https://rflechner.github.io/LinqToSalesforce/getting_started_with_salesforce.html

1: 
2: 

Environment.CurrentDirectory <- __SOURCE_DIRECTORY__
let [<Literal>] authfile = @"C:\prog\LinqToSalesforce\src\Files\OAuth.config.json"

During the 'Design time' a type provider frequently discovering generated structures.

To reduce risks of exceeding maximum allowed requests per 24 hours, we will use a tiny cache stored in files and shared between 'Design time' and 'Runtime'.

1: 
2: 
3: 

let [<Literal>] cacheFolder = __SOURCE_DIRECTORY__ + "\\.cache"
// 200 minutes (if you don't change frequently your tables structures, then increase it)
let [<Literal>] slidingExpiration = 200.

Generate your type (could take 2 minutes the first time if your bandwith is not good)

1: 
2: 
3: 

type TS = SalesforceTypeProvider<
            authFile=authfile, instanceName="eu11", 
            cacheFolder=cacheFolder, slidingExpirationMinutes=slidingExpiration>

Now instanciate your generated type with authentication informations

1: 
2: 

let authJson = File.ReadAllText @"C:\prog\LinqToSalesforce\src\Files\OAuth.config.json"
let sf = TS(authJson, cacheFolder, (TimeSpan.FromMinutes slidingExpiration))

Exploring data

You can create a script like LinqToSalesforce.TpExample\Script.fsx.

I suggest you use it with Atom and ionide.io ( cf. http://tomasp.net/blog/2016/fslab-ionide/ )

 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: 

query {
  for a in sf.Tables.Accounts do
    select (a.Name, a.CreatedDate)
}
|> Seq.toList
|> List.groupBy(fun (_, date) -> date.ToString("MMMM yyyy", System.Globalization.CultureInfo.InvariantCulture))
|> List.map(fun (month, group) -> month, group.Length)
|> Chart.Line
|> Chart.WithLayout (Layout(title = "Accounts creation by month"))
|> Chart.WithLegend true
|> Chart.WithHeight 500
|> Chart.WithWidth 700

let industryCounts =
  query {
    for a in sf.Tables.Accounts do
      select a.Industry
  }
  |> Seq.toList
  |> List.groupBy (fun industry ->
        if String.IsNullOrWhiteSpace industry
        then "Unknown"
        else industry
    )
  |> List.map(fun (industry, group) -> industry, group.Length)

industryCounts
    |> Chart.Area
    |> Chart.WithLayout (Layout(title = "Accounts by industry"))
    |> Chart.WithLegend true
    |> Chart.WithHeight 500
    |> Chart.WithWidth 700

industryCounts
|> List.map (fun (name, count) -> name => count)
|> Series.ofObservations

The result is cool

typeprovider

Sending data to Salesforce

You can update an entity

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 

let account1 =
  query {
    for a in sf.Tables.Accounts do
      take 1
      select a
  }
  |> Seq.head

account1.Phone <- "1234548478"
sf.Save account1

You can also add a new one

1: 
2: 
3: 

let na = TS.AccountEntity.CreateNew()
na.Name <- "TP Account"
sf.Save na
namespace Deedle
namespace System
Multiple items
namespace FSharp

--------------------
namespace Microsoft.FSharp
namespace FSharp.Charting
namespace XPlot
namespace XPlot.Plotly
namespace System.IO
namespace System.Net
type ServicePointManager =
  static val DefaultNonPersistentConnectionLimit : int
  static val DefaultPersistentConnectionLimit : int
  static member CertificatePolicy : ICertificatePolicy with get, set
  static member CheckCertificateRevocationList : bool with get, set
  static member DefaultConnectionLimit : int with get, set
  static member DnsRefreshTimeout : int with get, set
  static member EnableDnsRoundRobin : bool with get, set
  static member EncryptionPolicy : EncryptionPolicy
  static member Expect100Continue : bool with get, set
  static member FindServicePoint : address:Uri -> ServicePoint + 2 overloads
  ...

Full name: System.Net.ServicePointManager
property ServicePointManager.SecurityProtocol: SecurityProtocolType
type SecurityProtocolType =
  | Ssl3 = 48
  | Tls = 192

Full name: System.Net.SecurityProtocolType
type Environment =
  static member CommandLine : string
  static member CurrentDirectory : string with get, set
  static member Exit : exitCode:int -> unit
  static member ExitCode : int with get, set
  static member ExpandEnvironmentVariables : name:string -> string
  static member FailFast : message:string -> unit + 1 overload
  static member GetCommandLineArgs : unit -> string[]
  static member GetEnvironmentVariable : variable:string -> string + 1 overload
  static member GetEnvironmentVariables : unit -> IDictionary + 1 overload
  static member GetFolderPath : folder:SpecialFolder -> string + 1 overload
  ...
  nested type SpecialFolder
  nested type SpecialFolderOption

Full name: System.Environment
property Environment.CurrentDirectory: string
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val authfile : string

Full name: Using_type_provider.authfile
val cacheFolder : string

Full name: Using_type_provider.cacheFolder
val slidingExpiration : float

Full name: Using_type_provider.slidingExpiration
type TS = obj

Full name: Using_type_provider.TS
val authJson : string

Full name: Using_type_provider.authJson
type File =
  static member AppendAllLines : path:string * contents:IEnumerable<string> -> unit + 1 overload
  static member AppendAllText : path:string * contents:string -> unit + 1 overload
  static member AppendText : path:string -> StreamWriter
  static member Copy : sourceFileName:string * destFileName:string -> unit + 1 overload
  static member Create : path:string -> FileStream + 3 overloads
  static member CreateText : path:string -> StreamWriter
  static member Decrypt : path:string -> unit
  static member Delete : path:string -> unit
  static member Encrypt : path:string -> unit
  static member Exists : path:string -> bool
  ...

Full name: System.IO.File
File.ReadAllText(path: string) : string
File.ReadAllText(path: string, encoding: Text.Encoding) : string
val sf : obj

Full name: Using_type_provider.sf
Multiple items
type TimeSpan =
  struct
    new : ticks:int64 -> TimeSpan + 3 overloads
    member Add : ts:TimeSpan -> TimeSpan
    member CompareTo : value:obj -> int + 1 overload
    member Days : int
    member Duration : unit -> TimeSpan
    member Equals : value:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member Hours : int
    member Milliseconds : int
    member Minutes : int
    ...
  end

Full name: System.TimeSpan

--------------------
TimeSpan()
TimeSpan(ticks: int64) : unit
TimeSpan(hours: int, minutes: int, seconds: int) : unit
TimeSpan(days: int, hours: int, minutes: int, seconds: int) : unit
TimeSpan(days: int, hours: int, minutes: int, seconds: int, milliseconds: int) : unit
TimeSpan.FromMinutes(value: float) : TimeSpan
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val a : obj
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
module Seq

from Microsoft.FSharp.Collections
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
Multiple items
module List

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
  interface IEnumerable
  interface IEnumerable<'T>
  member GetSlice : startIndex:int option * endIndex:int option -> 'T list
  member Head : 'T
  member IsEmpty : bool
  member Item : index:int -> 'T with get
  member Length : int
  member Tail : 'T list
  static member Cons : head:'T * tail:'T list -> 'T list
  static member Empty : 'T list

Full name: Microsoft.FSharp.Collections.List<_>
val groupBy : projection:('T -> 'Key) -> list:'T list -> ('Key * 'T list) list (requires equality)

Full name: Microsoft.FSharp.Collections.List.groupBy
val date : obj
Object.ToString() : string
namespace System.Globalization
Multiple items
type CultureInfo =
  new : name:string -> CultureInfo + 3 overloads
  member Calendar : Calendar
  member ClearCachedData : unit -> unit
  member Clone : unit -> obj
  member CompareInfo : CompareInfo
  member CultureTypes : CultureTypes
  member DateTimeFormat : DateTimeFormatInfo with get, set
  member DisplayName : string
  member EnglishName : string
  member Equals : value:obj -> bool
  ...

Full name: System.Globalization.CultureInfo

--------------------
Globalization.CultureInfo(name: string) : unit
Globalization.CultureInfo(culture: int) : unit
Globalization.CultureInfo(name: string, useUserOverride: bool) : unit
Globalization.CultureInfo(culture: int, useUserOverride: bool) : unit
property Globalization.CultureInfo.InvariantCulture: Globalization.CultureInfo
val map : mapping:('T -> 'U) -> list:'T list -> 'U list

Full name: Microsoft.FSharp.Collections.List.map
val month : key
val group : (obj * obj) list
property List.Length: int
type Chart =
  static member Area : data:seq<#seq<'a1 * 'a2>> -> PlotlyChart (requires 'a1 :> key and 'a2 :> value)
  static member Area : data:seq<#key * #value> -> PlotlyChart
  static member Area : data:seq<#value> -> PlotlyChart
  static member Bar : data:seq<#seq<'a1 * 'a2>> -> PlotlyChart (requires 'a1 :> key and 'a2 :> value)
  static member Bar : data:seq<#key * #value> -> PlotlyChart
  static member Bar : data:seq<#value> -> PlotlyChart
  static member Bubble : data:seq<#key * #value * #value> -> PlotlyChart
  static member Column : data:seq<#seq<'a1 * 'a2>> -> PlotlyChart (requires 'a1 :> key and 'a2 :> value)
  static member Column : data:seq<#key * #value> -> PlotlyChart
  static member Column : data:seq<#value> -> PlotlyChart
  ...

Full name: XPlot.Plotly.Chart
Multiple items
static member Chart.Line : data:seq<#seq<'a1 * 'a2>> -> PlotlyChart (requires 'a1 :> key and 'a2 :> value)
static member Chart.Line : data:seq<#key * #value> -> PlotlyChart
static member Chart.Line : data:seq<#value> -> PlotlyChart

--------------------
static member Chart.Line : data:Series<'K,#value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart (requires equality and 'K :> key)
static member Chart.Line : data:seq<#value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart
static member Chart.Line : data:seq<#key * #value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart
static member Chart.WithLayout : layout:Layout -> chart:PlotlyChart -> PlotlyChart
Multiple items
module Layout

from XPlot.Plotly

--------------------
type Layout =
  new : unit -> Layout
  member ShouldSerializeangularaxis : unit -> bool
  member ShouldSerializeannotations : unit -> bool
  member ShouldSerializeautosize : unit -> bool
  member ShouldSerializebargap : unit -> bool
  member ShouldSerializebargroupgap : unit -> bool
  member ShouldSerializebarmode : unit -> bool
  member ShouldSerializeboxmode : unit -> bool
  member ShouldSerializedirection : unit -> bool
  member ShouldSerializedragmode : unit -> bool
  ...

Full name: XPlot.Plotly.Layout.Layout

--------------------
new : unit -> Layout
Multiple items
static member Chart.WithLegend : enabled:bool -> chart:PlotlyChart -> PlotlyChart

--------------------
static member Chart.WithLegend : ?Enabled:bool * ?Title:string * ?Background:ChartTypes.Background * ?FontName:string * ?FontSize:float * ?FontStyle:Drawing.FontStyle * ?Alignment:Drawing.StringAlignment * ?Docking:ChartTypes.Docking * ?InsideArea:bool * ?TitleAlignment:Drawing.StringAlignment * ?TitleFont:Drawing.Font * ?TitleColor:Drawing.Color * ?BorderColor:Drawing.Color * ?BorderWidth:int * ?BorderDashStyle:ChartTypes.DashStyle -> ('a0 -> 'a0) (requires 'a0 :> ChartTypes.GenericChart)
static member Chart.WithHeight : height:int -> chart:PlotlyChart -> PlotlyChart
static member Chart.WithWidth : width:int -> chart:PlotlyChart -> PlotlyChart
val industryCounts : (string * int) list

Full name: Using_type_provider.industryCounts
val industry : string
Multiple items
type String =
  new : value:char -> string + 7 overloads
  member Chars : int -> char
  member Clone : unit -> obj
  member CompareTo : value:obj -> int + 1 overload
  member Contains : value:string -> bool
  member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
  member EndsWith : value:string -> bool + 2 overloads
  member Equals : obj:obj -> bool + 2 overloads
  member GetEnumerator : unit -> CharEnumerator
  member GetHashCode : unit -> int
  ...

Full name: System.String

--------------------
String(value: nativeptr<char>) : unit
String(value: nativeptr<sbyte>) : unit
String(value: char []) : unit
String(c: char, count: int) : unit
String(value: nativeptr<char>, startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int) : unit
String(value: char [], startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int, enc: Text.Encoding) : unit
String.IsNullOrWhiteSpace(value: string) : bool
val group : string list
Multiple items
static member Chart.Area : data:seq<#seq<'a1 * 'a2>> -> PlotlyChart (requires 'a1 :> key and 'a2 :> value)
static member Chart.Area : data:seq<#key * #value> -> PlotlyChart
static member Chart.Area : data:seq<#value> -> PlotlyChart

--------------------
static member Chart.Area : data:Series<'K,#value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart (requires equality and 'K :> key)
static member Chart.Area : data:seq<#value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart
static member Chart.Area : data:seq<#key * #value> * ?Name:string * ?Title:string * ?Labels:#seq<string> * ?Color:Drawing.Color * ?XTitle:string * ?YTitle:string -> ChartTypes.GenericChart
val name : string
val count : int
Multiple items
module Series

from Deedle

--------------------
type Series =
  static member ofNullables : values:seq<Nullable<'a0>> -> Series<int,'a0> (requires default constructor and value type and 'a0 :> ValueType)
  static member ofObservations : observations:seq<'c * 'd> -> Series<'c,'d> (requires equality)
  static member ofOptionalObservations : observations:seq<'K * 'a1 option> -> Series<'K,'a1> (requires equality)
  static member ofValues : values:seq<'a> -> Series<int,'a>

Full name: Deedle.F# Series extensions.Series

--------------------
type Series<'K,'V (requires equality)> =
  interface IFsiFormattable
  interface ISeries<'K>
  new : pairs:seq<KeyValuePair<'K,'V>> -> Series<'K,'V>
  new : keys:'K [] * values:'V [] -> Series<'K,'V>
  new : keys:seq<'K> * values:seq<'V> -> Series<'K,'V>
  new : index:IIndex<'K> * vector:IVector<'V> * vectorBuilder:IVectorBuilder * indexBuilder:IIndexBuilder -> Series<'K,'V>
  member After : lowerExclusive:'K -> Series<'K,'V>
  member Aggregate : aggregation:Aggregation<'K> * observationSelector:Func<DataSegment<Series<'K,'V>>,KeyValuePair<'TNewKey,OptionalValue<'R>>> -> Series<'TNewKey,'R> (requires equality)
  member Aggregate : aggregation:Aggregation<'K> * keySelector:Func<DataSegment<Series<'K,'V>>,'TNewKey> * valueSelector:Func<DataSegment<Series<'K,'V>>,OptionalValue<'R>> -> Series<'TNewKey,'R> (requires equality)
  member AsyncMaterialize : unit -> Async<Series<'K,'V>>
  ...

Full name: Deedle.Series<_,_>

--------------------
new : pairs:seq<Collections.Generic.KeyValuePair<'K,'V>> -> Series<'K,'V>
new : keys:seq<'K> * values:seq<'V> -> Series<'K,'V>
new : keys:'K [] * values:'V [] -> Series<'K,'V>
new : index:Indices.IIndex<'K> * vector:IVector<'V> * vectorBuilder:Vectors.IVectorBuilder * indexBuilder:Indices.IIndexBuilder -> Series<'K,'V>
static member Series.ofObservations : observations:seq<'c * 'd> -> Series<'c,'d> (requires equality)
val account1 : obj

Full name: Using_type_provider.account1
custom operation: take (int)

Calls Linq.QueryBuilder.Take
val head : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.head
val na : obj

Full name: Using_type_provider.na
F# Project
Fork me on GitHub