Linq to SQL in F#

In the entry titled Linq to XML in F#, I walked through an example of how to use Linq to XML in F# to retrieve customer information from an XML file. In this post, I will show how this same functionality can be achieved with a database using the "experimental" Linq to SQL (a.k.a. FLinq) support found in FSharp.Powerpack.FLinq.dll that was released in the Sept. 2008 CTP. Don Syme states that this "is labelled experimental because some advanced SQL concepts are not yet handled (e.g. left-outer-joins)" (post by Don - Note: This post also contains several FLinq examples).

Path to Completion:
Since basic Separation of Concern (SoC) concepts were originally applied, only the data access layer will need to be updated to support the change from an XML repository to a SQL repository.

The following steps will need to be completed to accomplish this goal:
1. Create the test.
2. Create the database.
3. Generate the object model.
4. Implement the functionality.

Creating the Test:

This test is going to be similar to that which was written for the Linq to XML example. It will simply retrieve a customer object based on a specified Customer id. For example purposes only, I will be adding these methods/functions into the same projects that were created in the Linq to XML example.

        
[TestMethod]
public void CanGetCustomerByIdFromDB()
{   
    FSharpMockExample.Data.ICustomerDao customerDao = new FSharpMockExample.Data.CustomerDao();   
    FSharpMockExample.Entities.ICustomer customer = customerDao.GetByIdFromDB(2);      
    Assert.AreEqual(customer.Id, 2);   
    Assert.AreEqual(customer.Name, "AABB, Inc");        
    Assert.AreEqual(customer.Balance, 30);
} 

Creating the Database:

The database for this example is very simple with a single table that contains three fields.

Here are the steps that were followed to create the database using the SQL Server 2005 Express Edition:

1. Create a database called FSharpSample.
2. Run the following script to create the table:

USE [FSharpSample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customers](
    [CustomerId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Balance] [decimal](18, 2) NOT NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED (    [CustomerId] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF 

3. Populate the table with the following script:

USE [FSharpSample]
GO
INSERT INTO dbo.Customers (CustomerId, Name, Balance)VALUES (1, 'ABC Company', 20.00)
INSERT INTO dbo.Customers (CustomerId, Name, Balance)VALUES (2, 'AABB, Inc', 30.00)
GO

Generating the Object Model:

The object model is generated using the code generation tool called SqlMetal.exe, which is often found in c:\program files\Microsoft SDKS\Windows\v6.0A\bin\. Since SqlMetal.exe does not yet support F#, the object model is generated in C#.

There are a lot of ways to generate the object model; however, for this specific example I did the following:

1. Open a command prompt and navigate to c:\program files\Microsoft SDKS\Windows\v6.0A\bin\.
2. Execute the following command: sqlmetal /server:".\SQLEXPRESS" /database:FSharpSample /namespace:FSharpSample /code:FSharpSampleDB.cs /language:csharp
3. This generates a file called FSharpSampleDB.cs and places it in the c:\program files\Microsoft SDKS\Windows\v6.0A\bin\ directory.
4. Create a new CSharp Class Library project within the solution and add FSharpSampleDB.cs to it.
5. Build the new project.

On to the F# Code:

The first step that is needed to support FLinq in a project is to add references to System.Data, System.Data.Linq, and FSharp.PowerPack.Linq. After that, it is simply a matter of adding a new member to the CustomerDao class.

Signature File:

The only change to the CustomerDao signature file is the addition of one new abstract member.

#light
namespace FSharpMockExample.Data
open FSharpMockExample.Entities
open System.Xml.Linq

type ICustomerDao = interface
    abstract GetById: int -> ICustomer
    abstract GetById: XElement * int -> ICustomer
    abstract GetByIdFromDB: int -> ICustomer
end

type CustomerDao = class
    new: unit -> CustomerDao
    interface ICustomerDao
end

Source File:

The first thing that should be noticed is the #nowarn "57" compiler directive. Since FLinq is still in the "experimental" phase, this directive is necessary to suppress the related warning. Next, you'll notice that the Microsoft.FSharp.Linq and FSharpSampleDB namespaces have been opened. The rest of the work is in the GetByIdFromDB member.

Here's a description of the GetByIdFromDB member:

1. The first identifier is set to the database connection string (note: This is hard coded for example purposes only. Normally the value would be stored in a config file.)
2. The second identifier is set to a new instance of the DataContext.
3. Finally we build and execute a Linq Query Expression. There are a few interesting things that should be noticed. First, the quoted expression identified by "<@" and "@>". These quotations cause the compiler to treat the syntax within them in a special way. Second, the Seq.hd function causes only the first element to be returned. Finally, the resulting object is cast to the ICustomerDao interface.

#light
#nowarn "57"
namespace FSharpMockExample.Data
open FSharpMockExample.Entities
open System.Xml.Linq
open Microsoft.FSharp.Linq
open FSharpSampleDB

module XLinqHelper = 
    let GetXName xname = XName.op_Implicit(xname)

type ICustomerDao = interface
    abstract GetById: int -> ICustomer
    abstract GetById: XElement * int -> ICustomer
    abstract GetByIdFromDB: int -> ICustomer
end

type CustomerDao = class
    new()={}
    interface ICustomerDao with
        member this.GetById id =
            let rawXml = "removed from this sample"
            let thisInterface = (this :> ICustomerDao)
            let xml = XElement.Parse rawXml
            thisInterface.GetById (xml, id)

        member this.GetById (xml, id) =
            let GetCustomer (customerElement:XElement) = 
                new Customer(int(customerElement.Attribute(XLinqHelper.GetXName "Id").Value), 
                    customerElement.Attribute(XLinqHelper.GetXName "Name").Value, 
                    decimal(customerElement.Attribute(XLinqHelper.GetXName "Balance").Value)) :> ICustomer

            xml.Elements() |> Seq.find(fun customer -> (int(customer.Attribute(XLinqHelper.GetXName "Id").Value) = id)) 
                           |> GetCustomer
        member this.GetByIdFromDB id =
            let connString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FSharpSample;Data Source=.\SQLEXPRESS"

            let db = new FSharpSampleDB(connString)

            Query.query <@ seq { for c in db.Customers do
                                 if id = c.CustomerId then
                                     yield (new Customer(c.CustomerId, c.Name, c.Balance))} 
                   |> Seq.hd @> :> ICustomer

end

Conclusion:

With the help of Flinq and some basic SoC principles, switching our data access layer from an XML repository to a SQL repository is trivial. While Flinq is not yet ready for production, full support is likely in the not too distant future. With Flinq, the future looks bright!