Visualizing LINQ with GraphViz

by Pezi 17. January 2014 21:01

After my talk last night, several people expressed an interest in the script I was using to draw the LINQ expression trees. I have uploaded here on github.

This is just a script I use in development. It doesn't visualize every node by a long shot, and does a fair bit of name replacing to make some of the very long generic type names readable.  You will need GraphViz installed to use this script.  You might need to point the function that does the generation at the location where you've installed it.  

You can use it like this from FSI :

#load "GraphViz.fsx"
FSharp.Data.Sql.Common.QueryEvents.LinqExpressionEvent
|> Observable.add GraphViz.toGraph

Now, whenever you evaluate a query expression, it will pop up a bunch of images with whatever your machine uses to view SVG files, like this rather lovely one below.  Have fun.

edit; this will spam your temp directory with svg files!

 

SQL Provider - new stuff including PostgreSQL support and option types!

by Pezi 24. December 2013 09:21

The latest version of the SQL provider can be found on github or at nuget.   (EDIT:  MYSQL and Oacle are now a supported as well!)

PostgreSQL

The provider now has support for connecting to PostgreSQL databases! How exciting. Like SQLite, this new mode uses dynamic assembly loading and is based on the .NET connector libraries here.  

Once you have your Npgsql libraries you can connect yourself up easily as so:

type sql = SqlDataProvider< @"Server=localhost;Port=5432;Database=world;user=postgres;password=sekret;", 
                      Common.DatabaseProviderTypes.POSTGRESQL, @"F:\postgre\f#\Npgsql" >

let ctx = sql.GetDataContext()

// access individuals
let southend = ctx.``[public].[city]``.Individuals.``As name``.``486, Southend-on-Sea``

// get all cities
ctx.``[public].[city]`` |> Seq.toArray

// query some cities
query { for x in ctx.``[public].[city]`` do
        where (x.name =%"%s%")
        select x
        take 100 } |> Seq.toArray

The whole shooting match should mostly work, including relationship navigation, individuals and complex queries. Unfortunately the Ngpsql connector doesn't give any type mapping information so it had to be done the old fashioned way (e.g, me writing out long match expressions, which was fun). Postgre lets you have some really crazy data types so I would not be surprised if you don't get all your columns depending on what your database schema is like.

Option Types

There is a new static parameter called UseOptionTypes.  If you flip this little puppy to true, all columns in the database that are marked as nullable will now be generated as F# option types, woop!  This means that in your queries you will have to access the .Value property when supplying criteria and join information.  You can also quite handily use the IsSome and IsNone properties in a criteria expression, which will be translated to IS NOT NULL, IS NULL, or whatever the equivalent is for the specific database vendor you are using.

type sql = SqlDataProvider< @"Data Source=F:\sqlite\northwindEF.db ;Version=3", 
        Common.DatabaseProviderTypes.SQLITE, @"F:\sqlite\3", 1000, true >

let ctx = sql.GetDataContext()
query { for c in ctx.``[main].[Customers]`` do            
        // IS NOT NULL
        where (c.CompanyName.IsSome)
        // IS NULL
        where (c.CompanyName.IsNone)
        // Usual criteria is now specified with the Value property
        where (c.CompanyName.Value =% "%TESCO%")
        // obviously you will need to be careful in projections with option types..
        select (c.CompanyName, c.CompanyName.Value) } |> Seq.toArray

 

Documentation!

I still have not written any. Sorry.

 

Tags:

F# | type providers