In this post I thought I would give some simple, up-to-date examples of writing queries using the F# Power Pack and executing them via LINQ. The techniques described here also apply to querying any obejcts that support the IQueryable interface.

 

My aim here is not to give a complete guide to "doing everything you can do in SQL" or "everything you can do with LINQ and F#". Instead, I just want to bring some of the material you might find on the web up-to-date for the Beta1/Beta2 releases of the F# Power Pack. Also, note that the IQueryable support described here is not in F#, but in the F# Power Pack, which includes other components such as FsLex and FsYacc. The binaries and code for the F# Power Pack is included with the F# CTP release and can be used with Visual Studio 2010, and we’ll be looking to do regular CodePlex releases of the Power Pack as VS2010 settles down.

 

For those unfamiliar with Linq and IQueryable, here’s a recap: .NET defines an interface IQueryable for roots of queries made up of select/where/join/group/sort and a few other SQL-like operations. C# and VB have a syntax to write queries, and desugar this syntax to expressions and expression trees. Engines such as LINQ-to-SQL then execute expression trees in various interesting ways, for example as SQL. There are many other interesting and powerful engines that implement IQueryable support too.

 

In this post we’ll be looking at how you can leverage IQueryables by using the F# Power Pack. You can walk through everything here step by step in F# Interactive and the script is attached. First, however, some basics, so let’s define some in-memory data:

 

type Customer =

    { Name:string;

      UniqueID: int;

      Weighting:float;

      Preferences: int list }

let c1 = { Name="Don";    UniqueID=6; Weighting=6.2; Preferences=[1;2;4;8] }

let c2 = { Name="Peter";  UniqueID=7; Weighting=4.2; Preferences=[10;20;30;40] }

let c3 = { Name="Freddy"; UniqueID=8; Weighting=9.2; Preferences=[11;12;14] }

let c4 = { Name="Freddi"; UniqueID=9; Weighting=1.0; Preferences=[21;22;29;42] }

 

let data = [c1;c2;c3;c4]

 

Simple In Memory Queries

 

In F# we express many queries using simple F# sequence expressions. Here is a very simple F# in-memory query:

 

let q0 =

    [ for i in data do

          if i.Name.Length = 6 then

              yield (i.Name,i.UniqueID) ]

 

As expected, this generates a list of length 2, containing entries for Freddy and Freddi. You can use the same inner syntax for to generate an on-demand sequence (an IEnumerable<T>):

 

let q1 =

    seq { for i in data do

             if i.Name.Length = 6 then

                 yield (i.Name,i.UniqueID) }

 

When executed, this will produce an IEnumerable. We can compute the length of this sequence as follows:

 

q1 |> Seq.length

 

or we might convert it to a list:

 

q1 |> Seq.toList

 

One thing to note is that F# sequence expression syntax looks and feels a lot like regular F# code except enclosed with [ ... ] or seq { ... } and with a few yiields added (it is much like a C# iterator, except in expression form).  Sequence expressions are useful for many things, but they have their limits. As a result, it is quite common to follow a sequence expression by a pipeline of combinators, e.g.

 

    seq { for i in data do

             for j in data do

                if i.Name.Length = j.Name.Length && i.Name <> j.Name then

                   yield (i.Name,j.Name) }

    |> Seq.sortBy (fun (name1, name2) -> name1)

    |> Seq.toList

 

Here we did the sorting and conversion to a list just after the { ... } syntax.  The whole expression above is the “query”, but only one part of it uses the sequence expression syntax. This is quite common in F#. Also, there are other kinds of operators that can be considered queries, e.g. the functions Seq.pick, Seq.tryPick, Seq.find and Seq.tryFind are all queries for single elements. Queries for single elements should be written by applying one of these functions to a sequence or some other collection.

 

Using IQueryables with the F# Power Pack

 

Now, as we mentioned above, .NET 3.5 introduced nice machinery for executing queries “in other ways”, for example as SQL on a database. An indicator of this is when a data source supports the type IQueryable which can be thought of as a “super enumerable”. If you see an object supporting IQueryable, then it’s likely that something “smart” can potentially go on under the hood, e.g. your queries can be implemented through a translation or optimization of some kind. For example, the objects representing handles to SQL tables may implement IQueryable.

 

In the rest of this post we’re going to look at how to write and execute queries that utilize IQueryables by using some simple functionality from the F# Power Pack.  (Note: Anything supporting IQueryable also supports IEnumerable, so you can use it just like an ordinary F# sequence, if the data source is small).

 

First, however, let’s look at the simplest form of IQueryable, which is a simple wrapper around some in-memory data – this is, in a sense, a “dumb” IQueryable.

 

#r "System.Core.dll"

open System.Linq

let db = Queryable.AsQueryable<Customer>(data)

 

We can invoke the IQueryable “way” of accessing this data as  follows:

 

#r "FSharp.PowerPack.Linq.dll"

open Microsoft.FSharp.Linq

open Microsoft.FSharp.Linq.Query

 

let q2 =

    query <@ seq { for i in db do

                      if i.Name.Length = 6 then

                         yield (i.Name,i.UniqueID) }  @>

 

That is, we take the original query, and put query <@ ... @> around it. Once the query is constructed, the result can be used as an ordinary IEnumerable, e.g.

 

        q2 |> Seq.toList

 

This will give the same result as before. The important thing is the above can be used with any IQueryable, and that the query is executed through the IQueryable machinery. This means the syntax tree for the query is passed, piece by piece, to the IQueryable. For “real” IQueryables this in turn translates the query to a form executed on a database or a remote server.

 

Example: Database Queries

 

So far, we’ve seen the basic philosophy for the IQueryable support in the F# Power Pack; for F# query expressions involving IQueryables, you can query <@ ... @> around the expression and it gets executed through the IQueryable machinery. We’ll go into what constitutes an F# query expression a bit later, but first let’s take a look at doing database queries via this mechanism. (A short digression before we start doing database queries; don’t forget you can also do queries using more fundamental mechanisms such as those used by ADO.NET, a topic well covered in books such as Foundations of F# and Expert F#)

 

First let’s reference the SqlMetal generated interface to the famous NORTHWND database (Note: a version is in the zip attached to the blog, along with the northwnd.dll built by run SqlMetal.exe on this database and compiling the generated C# code to a DLL)

 

#r "System.Data.Linq.dll"

#r "northwnd.dll"

 

Assuming you have copied NORTHWND.MDF to your working directory, you can open the database using a version of SQL Express with this connection string:

 

let sqlServerInstance = @".\SQLEXPRESS"

let connString = @"AttachDBFileName='" + __SOURCE_DIRECTORY__ + @"\NORTHWND.MDF';Server='" + sqlServerInstance + "';user instance=true;Integrated Security=SSPI;Connection Timeout=30"

 

let db = new NORTHWND(connString)

 

It’s handy to set the log:

 

db.Log <- System.Console.Out

 

Let’s now define a simple query:

 

let q3 =

    query <@ seq { for i in db.Customers do

                      for j in db.Employees do

                         if i.Country = j.Country then

                            yield (i.ContactName, j.FirstName, j.LastName) } @>

 

We now recognize this as a query over an IQueryable (in this case the database tables are IQueryables). We can now execute this database query as SQL on the server and return the results, e.g.

 

        q3 |> Seq.toList

 

This will produce a list of 93 entries. Here’s a second example:

 

let q4 =

   query <@ seq { for c in db.Customers do

                     for e in db.Employees do

                         if c.Address.Contains("Jardim") &&

                            c.Address.Contains("rosas") then

                               yield (e.LastName,c.ContactName) } @>

 

// Now execute a query

q4 |> Seq.toList

 

And a similar example using sorting, within the query:

 

let q5 =

   query <@ seq { for c in db.Customers do

                     for e in db.Employees do

                         if c.Address.Contains("Jardim") &&

                            c.Address.Contains("rosas") then

                               yield e }

            |> Seq.sortBy (fun e -> e.LastName) @> 

 

q5 |> Seq.toList

 

Here the sortBy occurs within the <@ ... @> , indicating it is executed as part of the database query, rather than in-memory.

 

As indicated above, the aim of this blog post is not to show how to write every kind of SQL or LINQ query with the F# Power Pack. However, let’s take a quick look at the set of operators that you can use inside a query <@ ... @> . For starters, a query expression can be one of these forms,

 

     seq { seq-expr }

     [ seq-expr ]

     [| seq-expr |]

 

Where the sequence expression contains the usual sequence expression constructs,:

 

    seq-expr =

      | for pat in query-expr do seq-expr

      | if expr then seq-expr else seq-expr

      | yield! query-expr     

      | seq-expr; seq-expr     

      | yield expr     

      | ...

 

Next, a query expression can also be made up of uses of the following operators and syntactic forms, perhaps via pipelining:

 

      Seq.toArray

      Seq.toList

      Seq.length

      Seq.head

      Seq.find

      Seq.max

      Seq.min

      Seq.average

      Seq.averageBy

      Seq.sum

      Seq.exists  

      Seq.forall 

      Seq.sumBy

      Query.maxBy

      Query.minBy

 

      Seq.empty

      Seq.collect

      Seq.filter

      Seq.map

      Seq.take

      Seq.sort             

      Seq.distinct

      Seq.sortBy

      Query.contains

      Seq.append

      Query.groupBy

      Query.join

      Query.groupJoin

 

Not all combinations of these can be used together – for example, Seq.sortBy and several other operators must appear as the “outer” or “last” part of a query. Finally, a query expression can contain the following, which are useful for defining macros and shortcuts in the query:

 

      reflected-definition [optional-args]

      let v = e in qexpr-with-post-processing

      let f args = e in qexpr-with-post-processing

      if expr then qexpr1 else qexpr2

 

      expr (of type IQueryable<_>) // e.g. db.Customers, f x

 

The ability to use reflected definitions in queries gives an interesting and powerful way to reuse query fragments, something I plan to discuss in a future post.

 

For technical reasons, you must use the Query.* versions of some operators. For example, joins are expressed using the Query.join and Query.groupJoin operators.

 

query <@ Query.join db.Employees db.Customers

             (fun e -> e.Country)

             (fun c -> c.Country)

             (fun e c -> e) 

         |> Seq.length  @>

 

Though often similar queries can be written using a conditional:

 

query <@ seq { for e in db.Employees do

                  for c in db.Customers do

                     if e.Country = c.Country then

                         yield e } 

         |> Seq.length  @>

 

Note that when joins are expressed as combinators, they are a little more verbose than joins written in comprehension syntax such as in C#. 

 

Some final notes:

 

Ø  The above hasn’t touched on nullable values, which are, of course, an important issue in database work. In F#, you must unwrap nullables explicitly – on the whole no implicit lifting of operators is provided.

 

Ø  There are many things that can not be used inside queries, for example arbitrary imperative code. The "query" function will give a runtime failure if the query can't be translated. There are also some restrictions we plan to lift in future versions of the power pack, mostly around the use of tuples as replacements for anonymous types.

 

Ø  Due to an omission, Seq.sort isn’t in the list above, though we’ll be adding it in the next release of the F# Power Pack. You can use Seq.sortBy instead.

 

Ø  The code for the F# Power Pack is in the F# CTP release, so if you wish you can take a look at how all this is implemented. For example, if you’re interested in how F# quotations get translated to LINQ expression trees, then this is where you to look. There is also an interesting pre-processing phase that effectively walks the F# quotation tree looking for the Query DSL outlined above.

 

Ø  We’re planning a CodePlex release of the Power Pack under a more general license – more on that sometime soon!

 

Ø  The design goals for the F# Power Pack support for queries aren’t the same of the design goals for C# and VB’s LINQ support. For example, those languages had a strict goal to make the embedded LINQ DSL query language look and feel much like SQL, though with some variations in phrasing and ordering. The F# Power Pack design goals are to make it feasible to write the most common queries against the strongly typed ORM models provided by SqlMetal, building on the existing techniques and knowledge of the F# programmer. It does assume you know and are comfortable with F# programming.