Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

As you may know by now, the major new feature of SQL Server Compact 4.0 is support for ASP.NET starter websites. In addition, the Entity Framework support has been improved with the following features:

- Support for Code First development (requires EF 4 Feature CTP 4), allowing you to create database and schema in code.

. Support for paging (can be implemented with Skip and Take in LINQ).

- Support for server generated keys (IDENTITY)

The purpose of this post is to show how to use Entity Framework 4 with SQL Server Compact 4.0 and ASP.NET without having the full support for SQL Server Compact 4.0 in VS 2010.

I will cover the following scenarios in the sample application:

1: Migrate data and schema for the solution from either SQL Server Compact 3.5 or SQL Server 2005 or later.

2: Create a Type-Per-Table Entity Data Model (EDM) based on a SQL Server Compact 4.0 database.

3: Create a layered application, where the UI has no knowledge of Entity Framework, in order to improve testability, minimize UI code and allow the Data Access layer to be replaced with another Data Access Technology (unlikely, I know )

4: Provide the initial implementation of a music track list displayed in a grid, which can be sorted and paged.

5: Provide access to invoice data in Excel

6: Allow easy management of lookup tables like Genre, Artist and MediaType

7: Use various extension methods, that make your life with Entity Framework smoother.

For this sample I am using the Chinook database from CodePlex, which is delivered in script format and as a 3.5 SDF file. The Chinook database schema represents a digital media store, including tables for artists, albums, media tracks (3500 tracks), invoices and customers.

Creating the database (optionally migrating from 3.5 or Server)

Depending on your starting point, there are many ways to get to a 4.0 database.

If you have a 3.5 database, you can use SqlCeCmd40.exe to upgrade a copy of this database to 4.0:

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –e upgrade

will do an in-place upgrade for you. (This requires 4.0 CTP2)

If you have a database script, either from a SQL Server database (created by Export2SqlCe.exe or from elsewhere), you can use SqlCeCmd40.exe to create an empty 4.0 database file and import the script:

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –e create

sqlcecmd40 –d”Data Source=C:\data\Chinook40.sdf” –i C:\x.sql

Or you can use WebMatrix to graphically create the database. (Support for doing this in Visual Studio will come with VS 2010 SP1).

Creating the Data Access Layer

As mentioned, I will create a database first EDM. Due to the missing Server Explorer / Data Sources integration with SQL Compact 4, you will have to do this by using a workaround. I know of 2:

Either by using the command line tools as described in the blog: http://getsrirams.blogspot.com/2010/10/adonet-entity-data-model-for-sqlserver.html or

Have a 3.5 database handy with the exact same table definition (schema) as the 4.0 database. After generating the EDM based on the 3.5 database, tweak the model to work with a 4.0 database. Let’s see how this is done:

Start by creating an empty solution, named Chinook, to hold our projects:

Then add a C# Class library project to the solution, and name it Chinook.Data:

Remove Class1.cs, then go to file system, and copy the Chinook.sdf (the 3.5 version) to the solution folder (C:\projects\chinook).

(I have also copied the 4.0 version, so I can easily find it later). Now we can add the EDM based on the 3.5 database file:

Name the model ChinookModel.edmx and click Add. In the next step, select Generate From Database, and click Next.

On the “Choose you data connection” step, create a new connection to the Chinook.sdf 3.5 version database, using the SQL Server Compact 3.5 provider, and click Next:

Then you get this question:

(I suggest you reply No)

Select all tables, select pluralize and click Finish. Now we have our Model:

Now, let’s make the Model work with our SQL Server Compact 4.0 file. First, modify the connection string in app.config as follows:

Then modify the emdx file (right click the file, and select Open With.. XML Editor).

Verify your changes by opening the edmx file in the visual designer.

Creating the POCO Entities for use with Repository

Now let us create a separate class library for our POCO entities based on the model, This will allow us to reference this Model project from both the data access layer and the UI layer. In order to do this, follow the steps in this EF Team blog post, and you will end up with a solution structure like this:

Notice the following change,that was made to ChinookModel.tt in order to have the entities in a separate project:

string inputFile = @"..\Chinook.Data\ChinookModel.edmx";

Also, a reference to the Chinook.Model project was added to Chinook.Data:

And the Custom Tool Namespace for the Chinook.Model.Context.tt file was set to Chinook.Model:

Creating the Repository

Now, add a Chinook.Repository project to the solution, to create the data access methods used by the UI. This project should reference the Chinook.Model and the Chinook.Data projects. As always, remove the Class1.cs file. Now, we want to create a method that allows the UI to get a page of Track data for display in a GridView. This is an initial implementation (notice the parameters, which are passed by default when using and ObjectDataSource from the UI):

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
    using (var context = new ChinookEntities())
    {
        return context.Tracks.Include("Albums").Include("Artists").Include("Genres").ToList();

    }
}

There are some things missing/not ideal with this:

- The Include statements are based on text strings.


- Sorting and paging is not implemented

In order to make the Include statement strongly typed, I have located an extension method from http://blogs.microsoft.co.il/blogs/shimmy/archive/2010/08/06/say-goodbye-to-the-hard-coded-objectquery-t-include-calls.aspx (Implemented in the ObjectQueryExtensions.cs file in the Chinook.Data project)

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
    using (var context = new ChinookEntities())
    {
        return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).ToList();
    }
}

That’s much better, and avoids nasty runtime errors.

The sortBy parameter from the ObjectDataSource is just a text string (field name), optionally followed by “DESC”. So to do sorting, we could write a long case statement to set the sortexpression, or use another extension method, which accepts a sort expression. I found one here: http://landman-code.blogspot.com/2008/11/linq-to-entities-string-based-dynamic.html

public List<Track> GetAll(string sortBy, int maximumRows, int startRowIndex)
{
    using (var context = new ChinookEntities())
    {
        if (string.IsNullOrWhiteSpace(sortBy))
        {
            sortBy = "Name";
        }
        return context.Tracks.Include(t => t.Album.Artist).Include(t => t.Genre).OrderUsingSortExpression(sortBy).Skip(startRowIndex).Take(maximumRows).ToList();
    }
}

In addition to the GetAll method, in order to support paging, the repository must also implement a GetCount method: 

public int GetCount()
{
    using (var context = new ChinookEntities())
    {
        return context.Tracks.Count();
    }
}

Creating the UI Layer

Now, add a ASP.NET Web Application project. Call it Chinook.UI. Add a “WebForm using Master”, and call it TrackList.aspx. Add references to Chinook.Model and Chinook.Repository.

Add this content to TrackList.aspx:

Notice the highlighted sections in the markup.

Add the connection string entry from the Data project to web.config.

Set the UI project as Start Project and the TrackList.aspx as start page.

Press F5 and see it run!

To display the Album title rather than the albumId, add a AlbumName readonly property to a new Track.Custom.cs POCO partial class, and display the Album.Name when requested:

//For display only
       public string AlbumName
       {
           get
           {
               if (this.Album != null)
               {
                   return this.Album.Title;
               }
               else
               {
                   return string.Empty;
               }
           }
       }

Then change the asp:boundfield from AlbumId to AlbumName in the TrackList.aspx file (you can do the same for other foreign key fields)

<asp:BoundField DataField="AlbumName" HeaderText="Album" 
  SortExpression="" />

Download the project so far from here.

In part 2, we will continue with the following additional features:

- Adding a Dynamic Data Project to quickly manage the lookup tables

- Creating an OData layer, allowing access from PowerPivot in Excel

- Deployment advice

(Thanks to Morten Hedekær Olsson for technical review)