Thursday, January 6, 2011

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
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:




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();


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=&quot;Data Source=|DataDirectory|\Chinook40.sdf&quot;

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:


Unknown said...

Hi ErikEJ,

I have been following your blogs on MS SQL Compact 4.0.

Please, I need your expert guide on the newly realeased SQL Compact 4.0. I wanted to generate a .dbml file using SQLMetal.exe for VS2010 desktop app built on .Net framework 4.0 in order to use Linq-to-SQL but it failed with the following error.

C:\>SqlMetal.exe myDatabase.sdf /dbml:myDataContext.dbml
Microsoft (R) Database Mapping Generator 2008 version 1.00.30729
for Microsoft (R) .NET Framework version 3.5 Copyright (C) Microsoft Corporation. All rights reserved.

Error : Incompatible Database Version. If this was a compatible file, run repair. For other cases refer to documentation. [ Db version = 4000000,Requested version = 3505053,File name = \\?myDatabase.sdf ]

Obviously, the SQLMetal.exe tool seems not to be supporting SQL Server Compact 4.0 even after installing Windows SDK version 7.1, which I think contains latest version of SQLMetal tool.

Could you guide me on how to overcome this hurdle and easily generate the needed .dbml file from SQL Server Compact 4.0 quickly?

Thank you in advance.

ErikEJ said...

Joset: Liqn to SQL is not supported with SQL Server Compact 4.0, you must use Entity Framework 4 instead (which now supports many features from LINQ to SQL)

Anthony Tarlano said...

Is this a permanent or temporary situation I also want to use linq-to-sql with ssce 4.0?

ErikEJ said...

This is a permanent situation, yes. Start learning Entity Framework!

ErikEJ said...

To follow up on Linq to SQL, this IS possible with SQL CE 4,0