Walkthrough: Accessing a SQL Database by Using Type Providers and Entities (F#)

Article
04/28/2015

This walkthrough for F# 3.0 shows you how to access typed data for a SQL database based on the ADO.NET Entity Data Model. This walkthrough shows you how to set up the F# SqlEntityConnection type provider for use with a SQL database, how to write queries against the data, how to call stored procedures on the database, as well as how to use some of the ADO.NET Entity Framework types and methods to update the database.

This walkthrough illustrates the following tasks, which you should perform in this order for the walkthrough to succeed:

Create the School database.
Create and configure an F# project.
Configure the type provider and connect to the Entity Data Model.
Query the database.
Updating the database

Prerequisites

You must have access to a server that's running SQL Server where you can create a database to complete these steps.

Create the School database

You can create the School database on any server that's running SQL Server to which you have administrative access, or you can use LocalDB.

To create the School database

In Server Explorer, open the shortcut menu for the Data Connections node, and then choose Add Connection.

The Add Connection dialog box appears.
In the Server name box, specify the name of an instance of SQL Server to which you have administrative access, or specify (localdb\v11.0) if you don't have access to a server.

SQL Server Express LocalDB provides a lightweight database server for development and testing on your machine. For more information about LocalDB, see Walkthrough: Creating a Local Database File in Visual Studio.

A new node is created in Server Explorer under Data Connections.
Open the shortcut menu for the new connection node, and then choose New Query.
Open Creating the School Sample Database on the Microsoft website, and then copy and paste the database script that creates the Student database into the editor window.

Create and configure an F# project

In this step, you create a project and set it up to use a type provider.

To create and configure an F# project

Close the previous project, create another project, and name it SchoolEDM.
In Solution Explorer, open the shortcut menu for References, and then choose Add Reference.
Choose the Framework node, and then, in the Framework list, choose System.Data, System.Data.Entity, and System.Data.Linq.
Choose the Extensions node, add a reference to the FSharp.Data.TypeProviders assembly, and then choose the OK button to dismiss the dialog box.
Add the following code to define an internal module and open appropriate namespaces. The type provider can inject types only into a private or internal namespace.
```
module internal SchoolEDM

open System.Data.Linq
open System.Data.Entity
open Microsoft.FSharp.Data.TypeProviders
```
To run the code in this walkthrough interactively as a script instead of as a compiled program, open the shortcut menu for the project node, choose Add New Item, add an F# script file, and then add the code in each step to the script. To load the assembly references, add the following lines.
```
#r "System.Data.Entity.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
```
Highlight each block of code as you add it, and choose the Alt + Enter keys to run it in F# Interactive.

Configure the type provider, and connect to the Entity Data Model

In this step, you set up a type provider with a data connection and obtain a data context that allows you to work with data.

To configure the type provider, and connect to the Entity Data Model

Enter the following code to configure the SqlEntityConnection type provider that generates F# types based on the Entity Data Model that you created previously. Instead of the full EDMX connection string, use only the SQL connection string.
```
type private EntityConnection = SqlEntityConnection<ConnectionString="Server=SERVER\InstanceName;Initial Catalog=School;Integrated Security=SSPI;MultipleActiveResultSets=true",
                                                    Pluralize = true>
 >
```
This action sets up a type provider with the database connection that you created earlier. The property MultipleActiveResultSets is needed when you use the ADO.NET Entity Framework because this property allows multiple commands to execute asynchronously on the database in one connection, which can occur frequently in ADO.NET Entity Framework code. For more information, see Multiple Active Result Sets (MARS).
Get the data context, which is an object that contains the database tables as properties and the database stored procedures and functions as methods.
```
let context = EntityConnection.GetDataContext()
```

Querying the database

In this step, you use F# query expressions to execute various queries on the database.

To query the data

Enter the following code to query the data from the entity data model. Note the effect of Pluralize = true, which changes the database table Course to Courses and Person to People.

query { for course in context.Courses do
        select course }
|> Seq.iter (fun course -> printfn "%s" course.Title)

query { for person in context.People do
        select person }
|> Seq.iter (fun person -> printfn "%s %s" person.FirstName person.LastName)

// Add a where clause to filter results.
query { for course in context.Courses do
        where (course.DepartmentID = 1)
        select course }
|> Seq.iter (fun course -> printfn "%s" course.Title)

// Join two tables.
query { for course in context.Courses do
        join dept in context.Departments on (course.DepartmentID = dept.DepartmentID)
        select (course, dept.Name) }
|> Seq.iter (fun (course, deptName) -> printfn "%s %s" course.Title deptName)

Updating the database

To update the database, you use the Entity Framework classes and methods. You can use two types of data context with the SQLEntityConnection type provider. First, ServiceTypes.SimpleDataContextTypes.EntityContainer is the simplified data context, which includes only the provided properties that represent database tables and columns. Second, the full data context is an instance of the Entity Framework class ObjectContext, which contains the method AddObject to add rows to the database. The Entity Framework recognizes the tables and the relationships between them, so it enforces database consistency.

To update the database

Add the following code to your program. In this example, you add two objects with a relationship between them, and you add an instructor and an office assignment. The table OfficeAssignments contains the InstructorID column, which references the PersonID column in the Person table.

// The full data context
let fullContext = context.DataContext

// A helper function.
let nullable value = new System.Nullable<_>(value)

let addInstructor(lastName, firstName, hireDate, office) =
    let hireDate = DateTime.Parse(hireDate)
    let newPerson = new EntityConnection.ServiceTypes.Person(LastName = lastName,
                                                FirstName = firstName,
                                                HireDate = nullable hireDate)
    fullContext.AddObject("People", newPerson)
    let newOffice = new EntityConnection.ServiceTypes.OfficeAssignment(Location = office)
    fullContext.AddObject("OfficeAssignments", newOffice)
    fullContext.CommandTimeout <- nullable 1000
    fullContext.SaveChanges() |> printfn "Saved changes: %d object(s) modified."

addInstructor("Parker", "Darren", "1/1/1998", "41/3720")

Nothing is changed in the database until you call SaveChanges.

Now restore the database to its earlier state by deleting the objects that you added.

let deleteInstructor(lastName, firstName) =
        query {
            for person in context.People do
            where (person.FirstName = firstName &&
                    person.LastName = lastName)
            select person
        }
        |> Seq.iter (fun person->
            query {
                for officeAssignment in context.OfficeAssignments do
                where (officeAssignment.Person.PersonID = person.PersonID)
                select officeAssignment }
            |> Seq.iter (fun officeAssignment -> fullContext.DeleteObject(officeAssignment))

            fullContext.DeleteObject(person))

        // The call to SaveChanges should be outside of any iteration on the queries.
        fullContext.SaveChanges() |> printfn "Saved changed: %d object(s) modified."

deleteInstructor("Parker", "Darren")

Warning

When you use a query expression, you must remember that the query is subject to lazy evaluation. Therefore, the database is still open for reading during any chained evaluations, such as in the lambda expression blocks after each query expression. Any database operation that explicitly or implicitly uses a transaction must occur after the read operations have completed.

Next Steps

Explore other query options by reviewing the query operators available in Query Expressions (F#), and also review the ADO.NET Entity Framework to understand what functionality is available to you when you use this type provider.