When we use LINQ To SQL in C#, the compiler doesn't generate IL to implement our query - we want Sql Server to execute the query, and so IL would not be much good. Instead the compiler creates data that describes our query, in the form of a System.Linq.Expressions.Expression. When the query needs to be executed, LINQ To SQL examines the expression and converts it to the equivalent T-SQL, and sends that to the database for execution.
It follows, then, that if we want to use LINQ To SQL in F#, we'd like to get the F# compiler to convert our code into a System.Linq.Expressions.Expression. The surprising news is, the F# compiler can't do this directly.
F# does have something equivalent; Quotations. If we place an expression within <@ and @>, we'll get back not an executable expression, but an object of type Microsoft.FSharp.Quotations.Expr<a'>. This needs to be converted to a Linq expression, and the function Microsoft.FSharp.Data.Linq.SQL will do this for us, but we have explicitly apply this function to the quotation.
Our query is eventually expressed in (at least) these 5 forms, in this order:
- F# Source
- Microsoft.FSharp.Quotatons.Expr
- System.Linq.Expressions.Expression
- T-Sql
- Sql Server Execution Plan
I said "at least" because there could will be intermediate forms, like the compiler's AST.
When you try and use this method, you're in for the next surprise. The supporting code for LINQ seems to exist only in the samples folders. As it's not compiled up by default, searching your entire drive for the "FSharp.Linq.dll" is both futile and frustrating. When I'd finished cursing whoever called this build a "release candidate", I copied the sample project over to the solution, deleted the usage sample files, and referenced this from the web service project.
OK, all the prep is done, let's implement the methods. Firstly, lets open the necessary namespaces:
open System.ServiceModel
open Microsoft.FSharp.Quotations.Typed
open Microsoft.FSharp.Data.Linq
open Microsoft.FSharp.Linq.SequenceOps
open Coyote.FSWCF.AWDC
open Coyote.FSWCF.AWEntity
Those final two Coyote namespaces contain the data context and the data entities.
Now let's define a useful interface:
[<ServiceContract(ConfigurationName = "ISimpleService", Namespace = "http://coyote-software.com/FSWCF/SimpleService")>]
type ISimpleService =
[<OperationContract>]
abstract GetContact: contactID:int -> seq<Contact>
[<OperationContract>]
abstract GetContacts: unit -> seq<Contact>
This is pretty much as we did before. The return types of seq<Contact> means, in standard .Net terms, IEnumberable<Contact>. WCF doesn't marshal this (by reference) back to the client, instead the serializer iterates though it and places each item into an Xml document that is returned.
Finally, the web service itself:
[<ServiceBehavior()>]
type SimpleService() =
let connString = "server=VMDBSERVER\\SQLEXPRESS;database=AdventureWorks;trusted_connection=True"
interface ISimpleService with
member x.GetContact contactID =
let awdc = new AdventureWorksDataContext(connString)
SQL <@ seq { for c in %awdc.Contacts when c.ContactID = %contactID -> c } @>
member x.GetContacts () =
let awdc = new AdventureWorksDataContext(connString)
SQL <@ seq { for c in %awdc.Contacts -> c } |> take 50 @>
This code works, but we aren't disposing the data context object. In fact, we can't dispose of it directly, because it will be needed when the serializer iterates the results. Therefore, a better implementation might be:
[<ServiceBehavior()>]
type SimpleService() =
let connString = "server=VMDBSERVER\\SQLEXPRESS;database=AdventureWorks;trusted_connection=True"
interface ISimpleService with
member x.GetContact contactID =
seq { use awdc = new AdventureWorksDataContext(connString)
yield! SQL <@ seq { for c in %awdc.Contacts when c.ContactID = %contactID -> c } @> }
member x.GetContacts () =
seq { use awdc = new AdventureWorksDataContext(connString)
yield! SQL <@ seq { for c in %awdc.Contacts -> c } |> take 50 @> }
This wraps one sequence in another, and will create and dispose of the data context for each iterator that's created.