F#: How to create a Deedle Frame with SQL data source

Question

I am trying to figure what is the best way in F# to create a Deedle Frame, when the data comes from an SQL server. I have tried things like the following.

#I "../packages/Deedle.0.9.12"
#load "Deedle.fsx"

#r "System.dll"
#r "System.Data.dll"
#r "System.Data.Linq"
#r "FSharp.Data.TypeProviders.dll"

open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Deedle

type dbSchema = SqlDataConnection<"Data Source=server;Initial     Catalog=database;Integrated Security=SSPI;">
let db = dbSchema.GetDataContext()

let fr = db.SomeTable |> Frame.ofRows

and a few other variants. But without luck. I am new to both F# and Deedle for that matter. I can see why the above does not work (the Frame.ofRows is not compatible with the argument) but I don't know what is the best way to proceed (or even how to proceed).

Answer 1

The Frame.ofRows function expects a sequence of series that represent individual rows of the frame. Similarly to Frame.ofColumns, this function is useful if you already have some series objects (or if you are creating everything from scratch). They take input of type seq<'TRowKey * ISeries<'TColKey>>.

When you're creating Deedle frame from some .NET data structure, you can use Frame.ofRecords which will work on any sequence and it will use reflection to get the names of the properties (and treat them as column names).

A long explanation, but just a few characters change in your code :-). I tested it with Northwind:

type Nwind = SqlDataConnection<"""Data Source=.\SQLExpress;
  Initial Catalog=Northwind;Integrated Security=SSPI;""">
let db = Nwind.GetDataContext()

// Create data frame from Products table (with appropriate column names)
let fr = db.Products |> Frame.ofRecords

The result is:

      ProductID ProductName                      SupplierID CategoryID QuantityPerUnit     UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued OrderDetails                               Categories Suppliers 
0  -> 1         Chai                             1          1          10 boxes x 20 bags  18.0000   39           0            10           False        System.Data.Linq.EntitySet`1[OrderDetails] Categories Suppliers 
1  -> 2         Chang                            1          1          24 - 12 oz bottles  19.0000   17           40           25           False        System.Data.Linq.EntitySet`1[OrderDetails] Categories Suppliers 
2  -> 3         Aniseed Syrup                    1          2          12 - 550 ml bottles 10.0000   13           70           25           False        System.Data.Linq.EntitySet`1[OrderDetails] Categories Suppliers 
3  -> 4         Chef Anton's Cajun Seasoning     2          2          48 - 6 oz jars      22.0000   53           0            0            False        System.Data.Linq.EntitySet`1[OrderDetails] Categories Suppliers 
4  -> 5         Chef Anton's Gumbo Mix           2          2          36 boxes            21.3500   0            0            0            True         System.Data.Linq.EntitySet`1[OrderDetails] Categories Suppliers 
(....)