LinqToSalesforce


Using F# type provider

For the moment, the NugGet is only on my feed. MyGet.org

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
namespace LinqToSalesforce
namespace SalesforceProvider
module Rest

from LinqToSalesforce
property ServicePointManager.SecurityProtocol: SecurityProtocolType
property Environment.CurrentDirectory: string
File.ReadAllText(path: string) : string
File.ReadAllText(path: string, encoding: Text.Encoding) : string
TimeSpan.FromMinutes(value: float) : TimeSpan
val a : SalesforceTypeProvider<...>.AccountEntity
property SalesforceTypeProvider<...>.Tables: SalesforceTypeProvider<...>.TablesType
property SalesforceTypeProvider<...>.TablesType.Accounts: SalesforceTypeProvider<...>.Account
property SalesforceTypeProvider<...>.AccountEntity.Name: string
property SalesforceTypeProvider<...>.AccountEntity.CreatedDate: DateTime
val toList : source:seq<'T> -> 'T list

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

Full name: Microsoft.FSharp.Collections.List.groupBy
DateTime.ToString() : string
DateTime.ToString(provider: IFormatProvider) : string
DateTime.ToString(format: string) : string
DateTime.ToString(format: string, provider: IFormatProvider) : 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
property List.Length: int
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
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
property SalesforceTypeProvider<...>.AccountEntity.Industry: string
String.IsNullOrWhiteSpace(value: string) : bool
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
static member Series.ofObservations : observations:seq<'c * 'd> -> Series<'c,'d> (requires equality)
val head : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.head
property SalesforceTypeProvider<...>.AccountEntity.Phone: string
SalesforceTypeProvider<...>.Save(entity: Entities.JsonEntity) : bool


Insert or update this entity into Salesforce
type AccountEntity =
  inherit JsonEntity
  member AccountNumber : string with get, set
  member AccountSource : string with get, set
  member Active__c : string with get, set
  member AnnualRevenue : string with get, set
  member BillingAddress : string with get, set
  member BillingCity : string with get, set
  member BillingCountry : string with get, set
  member BillingLatitude : float with get, set
  member BillingLongitude : float with get, set
  ...

Full name: SalesforceProvider.SalesforceTypeProvider,cacheFolder="C:\\prog\\LinqToSalesforce\\docs\\content\\.cache",slidingExpirationMinutes="200",authFile="C:\\prog\\LinqToSalesforce\\src\\Files\\OAuth.config.json",instanceName="eu11".AccountEntity
SalesforceTypeProvider<...>.AccountEntity.CreateNew() : SalesforceTypeProvider<...>.AccountEntity


Insert or update this entity into Salesforce
Fork me on GitHub