Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

We can now use two additional technologies, that both build on Entity Framework, to add some administrative features to our Chinook music shop, with minimal amount of coding effort:

- A website for administering the rarely used tables in the database (using a Dynamic Data website)

- Access to the Invoice data in Excel (using WCF Data Services)

Since we are not using the standard Entity Framework classes, but use a POCO based Data Access Layer, we need to accommodate slightly, as I will demonstrate.

Adding Dynamic Data website

Continuing where we left in Part One, add a ASP.NET Dynamic Data Entities Web Application to the solution:

Set this project as the Startup project. Add references to Chinook.Data and Chinook.Model from this project.

Modify the global.asax to refer to the ChinnokEntities ObjectContext:

public static void RegisterRoutes(RouteCollection routes)
{
    DefaultModel.RegisterContext(typeof(Chinook.Model.ChinookEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Also set ScaffoldAllTables = true for now – this means that admin pages are created for all the entities in the context.

Copy the connectionstrings section from web.config in the Chinook.UI project to the Chinnok.Admin project web.config file.

Try to run the site – you will get this error:

Could not find the CLR type for 'ChinookModel.Track'.

This is due to the fact that we are using POCO, so the Entity Framework initializer does not load the required metadata. The solution for this is here, so we add a new class to the Chinook.Data project (called ChinookEntities.Custom.cs), with the following contents:

namespace Chinook.Model
{
    public partial class ChinookEntities
    {
        //Dynamic Data
        public ChinookEntities(bool dynamicData)
            : base(ConnectionString, ContainerName)
        {
            var tracestring = this.CreateQuery<Genre>("ChinookEntities.Genres").ToTraceString();
        } 

    }
}
Then use this constructor in global.asax, and also set scaffold all tables = false:

DefaultModel.RegisterContext((() => new Chinook.Model.ChinookEntities(true)), new ContextConfiguration() { ScaffoldAllTables = false });

When you now run the site, you will get this error, as no tables are available:

There are no accessible tables. Make sure that at least one data model is registered in Global.asax and scaffolding is enabled or implement custom pages.

To include the two tables in question, we must add some partial classes to the Model project. Add a reference to System.ComponentModel.DataAnnotations in the Model project, and add to new classes with the following contents:

// Dynamic Data - add ref to System.ComponentModel.DataAnnotations
// and set scaffoldtable = true to using partial class
using System.ComponentModel.DataAnnotations;

namespace Chinook.Model
{
    [ScaffoldTable(true)]
    public partial class MediaType
    {
    }
}

Now you can manage the Genres and MediaTypes tables:

Adding WCF Data Service (OData)

The accounting department would like to access the Invoice data in our Music Shop from Excel. PowerPivot can connect to many data sources, but not directly to a SQL Server Compact database file, and access via the OLEDB provider is broken. But PowerPivot can access an OData feed, so let’s create one to expose the SQL Server Compact Invoice and InvoiceLine tables.

Add a WCF Data Service to the Chinook.Admin project:

Modify the AccountingService class as follow, and add a “using Chinook.Model” statement:

public class AccountingService : DataService<ChinookEntities>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("Invoices", EntitySetRights.AllRead);
        config.SetEntitySetAccessRule("InvoiceLines", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }

    protected override ChinookEntities CreateDataSource()
    {
        var context = new ChinookEntities(true);
        // Avoid dynamic proxies, as they cannot be serialized
        context.ContextOptions.ProxyCreationEnabled = false;
        return context;
    }
}
We only allow read-only access to the Invoices and InvoiceLines entities. In addition, we must override CreateDataSource, to solve the problem with metadata we also had with Dynamic Data and disable ProxyCreation.

You can now point PowerPivot to the AccountingService.svc URL and access the invoice data from Excel:

Deployment

Finally a few words on deployment and connection handling. As you may know, opening and closing connections to a SQL Server Compact file is a costly operation, and there is no concept of Connection Pooling with SQL Server Compact. A way to mimic a connection pool is to keep a dummy connection (that is not otherwise used) open for the duration of your application’s lifetime. For a web application, this can be achieved I global.asax, in the Application_Start event handler. In this sample, I have implemented a possible solution in the UI project, by calling the: Chinook.Repository.ContextHelper.Open();

The ContextHelper is implemented as follows:

public static class ContextHelper
{
    private static ChinookEntities context ;
    private static object objLock = new object();

    public static void Open()
    {
        lock (objLock)
        {
            if (context != null)
                throw new InvalidOperationException("Already opened");
            context = new ChinookEntities();
            context.Connection.Open();
        }
    }

}

Calling Open in this singleton class will keep a connection to the SQL Sever Compact file open for the lifetime of the application, and make subsequent  calls to Connection.Open fast.

For deployment, we must move the sdf file the App_Data folder, I have done this in the UI project, and also change the connection string in web.config as follows:

provider connection string="Data Source=|DataDirectory|\Chinook40.sdf"

Hope you found this walkthrough useful. For a deeper dive into Entity Framework 4, I highly recommend the Julie Lerman book.

You can download the full solution from here: