The Blog of Ian Mercer.

Generate a SQL Compact Database from your Entity Model (EDMX)

I recently switched from SQL Lite to SQL Compact for some of the databases in a system I'm building. If you are using SQL Compact make sure you have the Hotfix from Microsoft that fixes the Where() clause. The release that shipped as SP1 is utterly useless - can't even do a where clause on an nvarchar()! I don't know why they didn't recall it and replace it with the hotfix version!

Unfortunately SQL Server Management Studio doesn't support generating scripts from a SQL Compact database, so if you design your database there you are out of luck when it comes to creating the scripts to auto-generate it when you deploy your code.

Steve Lasker's Blog has a great article explaing how to embed these scripts as resources but it still doesn't solve the issue of how to generate the script in the first place.

There are a few articles that explain how to use T4 to generate stored procedures from the EDMX but I didn't see one to generate the actual database itself. So I built one and you can get it below.

To use this simply create a text file in your project called generate.tt, paste this text in. It will run and generate another file below itself containing the script to create each table it finds in each EDMX file in your project.

Limitations: Within the EDMX file there isn't enough information to fully create a database. While the code below can generate foreign keys and primary keys from Associations and Keys defined in your model it has no idea what indexes you might want. So for now it just generates an index for every field that looks vaguely indexable and it leaves it up to you to delete the ones you don't want.

    <#
    //
    // Generate SQL Server CE database tables from EDMX definition - suitable for some simple databases only
    // Creates tables, adds primary keys, adds an index for every conceivable field, adds foreign key relationships for all associations
    //
    #>

	<#@ template language="C#v3.5" debug="True" #>
	<#@ output extension=".sql" #>
	<#@ assembly name="System.Core" #>
	<#@ assembly name="System.Data" #>
	<#@ assembly name="System.Data.Entity" #>
	<#@ assembly name="System.Xml" #>
	<#@ assembly name="System.Xml.Linq" #>
	<#@ import namespace="System.Collections" #>
	<#@ import namespace="System.Collections.Generic" #>
	<#@ import namespace="System.Data.EntityClient" #>
	<#@ import namespace="System.Data.SqlClient" #>
	<#@ import namespace="System.Diagnostics" #>
	<#@ import namespace="System.IO" #>
	<#@ import namespace="System.Linq" #>
	<#@ import namespace="System.Text" #>
	<#@ import namespace="System.Text.RegularExpressions" #>
	<#@ import namespace="System.Xml.Linq" #>
	<#
	// This will generate the code to build SQL COMPACT tables for every EDMX in the current directory
	// Simply plug that code into SQL Server Management Studio or use it as a resource to build your database
	// Get the current directory from the stack trace
	string stackTraceFileName = new
	StackTrace(true).GetFrame(0).GetFileName();
	string directoryName = Path.GetDirectoryName(stackTraceFileName);
	#>

	--------------------------------------------------------------------------------
	--
	-- This code was generated by a tool.
	--
	-- Changes to this file may cause incorrect behavior and will be lost if
	-- the code is regenerated.
	--
	--------------------------------------------------------------------------------
	<#
	string[] entityFrameworkFiles = Directory.GetFiles(directoryName, "\*.edmx");

	foreach (string fileName in entityFrameworkFiles)
	{
	#>
	-- Creating TSQL creation script <#= fileName #>.
	<#
		DoExtractTables(fileName);
	}
	#>

	<#+
	XNamespace edmxns = "http://schemas.microsoft.com/ado/2007/06/edmx";
	XNamespace edmns = "http://schemas.microsoft.com/ado/2006/04/edm";
	XNamespace ssdlns = "http://schemas.microsoft.com/ado/2006/04/edm/ssdl";

	private void DoExtractTables(string edmxFilePath)
	{
	XDocument edmxDoc = XDocument.Load(edmxFilePath);
	XElement edmxElement = edmxDoc.Element(edmxns + "Edmx"); // element
	XElement runtimeElement = edmxElement.Element(edmxns + "Runtime"); // element

	XElement storageModelsElement = runtimeElement.Element(edmxns + "StorageModels"); // element

	XElement ssdlSchemaElement = storageModelsElement.Element(ssdlns + "Schema");

	string entityContainerName = runtimeElement
		.Element(edmxns + "ConceptualModels") // element
		.Element(edmns + "Schema") // element
		.Element(edmns + "EntityContainer") // element
		.Attribute("Name").Value;

	string ssdlNamespace = ssdlSchemaElement.Attribute("Namespace").Value;

	// Get a list of tables from the SSDL.
	XElement entityContainerElement = ssdlSchemaElement.Element(ssdlns + "EntityContainer");

	IEnumerable entitySets = entityContainerElement.Elements(ssdlns + "EntitySet");
	IEnumerable entityTypes = ssdlSchemaElement.Elements(ssdlns + "EntityType");
	IEnumerable associations = ssdlSchemaElement.Elements(ssdlns + "Association");

	string defaultSchema = entityContainerElement.Attribute("Name").Value;

	foreach (XElement table in entitySets)
	{
		string tableName = table.Attribute("Name").Value;

		#>

	DROP Table <#= tableName #>
	GO
	CREATE TABLE <#= tableName #> (
	<#+

	XElement entityType = entityTypes.First(et => et.Attribute("Name").Value == tableName);

	IEnumerable properties = entityType.Elements(ssdlns + "Property");
	int i = 0;
	int count = properties.Count();

	// GET THE PRIMARY KEY INFORMATION
	XElement key = entityType.Element(ssdlns + "Key");

	string pkstr = "";
	if (key != null)
	{
		pkstr = string.Join(",", key.Elements(ssdlns + "PropertyRef").Select(pk => pk.Attribute("Name").Value).ToArray());
		count++; // To add a comma on last field definition
	}

	// // Accumulate a list of indexes to add as we go along examining the
	properties

	// List indexesToAdd = new List();

	foreach (XElement property in properties)
	{

		bool last = (i == count-1);

		string propertyName = property.Attribute("Name").Value;

		string propertyType = property.Attribute("Type").Value;

		string nullField = "";

		if (property.Attribute("Nullable") != null && property.Attribute("Nullable").Value == "false")
			nullField = " NOT NULL";

		string maxLength = "";
		if (property.Attribute("MaxLength") != null)
			maxLength = "(" + property.Attribute("MaxLength").Value + ")";
		string comma = last ? "" : ",";

	#>
	<#= propertyName #> <#= propertyType #><#= maxLength #><#=nullField #><#= comma #>
	<#+

		i++;
	} // each property

	// Was their a primary key? If so, add it

	if (pkstr != "")
	{
	#>
	CONSTRAINT <#="PK\_"+tableName #> PRIMARY KEY ( <#= pkstr #> )
	<#+
	}
	#>
	)
	GO

	<#+
	// Now add all the indexes we might need ...

	foreach (XElement property in properties)
	{
		string comment = "";
		string propertyName = property.Attribute("Name").Value;

		if (pkstr.Contains(propertyName))
		{
			comment = "already in primary key, no need to index again";
		}

		int maxLength = -1;

		if (property.Attribute("MaxLength") != null)
			int.TryParse(property.Attribute("MaxLength").Value, out maxLength);

		if (maxLength > 256)
		{
			comment = "too long to meaningfully index";
		}

		string commentPrefix = comment != "" ? "-- " : "";

		// TODO: More rules here about what to index and whether it's unique or not ...

	// CREATE [UNIQUE] INDEX ...

		if (comment != "")
		{
	#>

	<#=commentPrefix #><#=comment #>

	<#+
		}
	#>
	<#=commentPrefix #>CREATE INDEX IX\_<#=tableName#>\_<#=propertyName #> ON <#=tableName #> (<#=propertyName
	#>);

	<#+
	} // each property
	} // Each table

	// Now add all the associations between tables ...

	foreach (XElement association in associations)
	{
		string associationName = association.Attribute("Name").Value;
		XElement referentialConstraint = association.Elements(ssdlns + "ReferentialConstraint").First();
		XElement principalRole = referentialConstraint.Element(ssdlns + "Principal");
		string thisTable = principalRole.Attribute("Role").Value;
		string thisField = principalRole.Element(ssdlns + "PropertyRef").Attribute("Name").Value;
		XElement dependentRole = referentialConstraint.Element(ssdlns + "Dependent");
		string otherTable = dependentRole.Attribute("Role").Value;
		string otherField = dependentRole.Element(ssdlns + "PropertyRef").Attribute("Name").Value;

	#>

	ALTER TABLE <#= otherTable #>
	ADD CONSTRAINT <#= associationName #>
	FOREIGN KEY (<#= otherField #>)
	REFERENCES <#= thisTable #> (<#= thisField #>)
	ON UPDATE CASCADE
	ON DELETE CASCADE
	GO
	<#+

	} // Each association
	} // Class

	#>

Related Stories

My love/hate relationship with Stackoverflow

Stackoverflow is a terrific source of information but can also be infuriating.

Ian Mercer
Ian Mercer

Xamarin Forms Application For Home Automation

Building a Xamarin Forms application to control my home automation system

Ian Mercer
Ian Mercer

Websites should stop using passwords for login!

A slightly radical idea to eliminate passwords from many of the websites you use just occasionally

Ian Mercer
Ian Mercer

VariableWithHistory - making persistence invisible, making history visible

A novel approach to adding history to variables in a programming language

Ian Mercer
Ian Mercer

Neo4j Meetup in Seattle - some observations

Some observations from a meetup in Seattle on graph databases and Neo4j

Ian Mercer
Ian Mercer

Updated Release of the Abodit State Machine

A hierarchical state machine for .NET

Ian Mercer
Ian Mercer

My first programme [sic]

At the risk of looking seriously old, here's something found on a paper tape

Ian Mercer
Ian Mercer

Building a better .NET State Machine

A state machine for .NET that I've released on Nuget

Ian Mercer
Ian Mercer

A simple state machine in C#

State machines are useful in many contexts but especially for home automation

Ian Mercer
Ian Mercer

MongoDB Map-Reduce - Hints and Tips

Ian Mercer
Ian Mercer

Why don't you trust your build system?

Ian Mercer
Ian Mercer

Elliott 803 - An Early Computer

Ian Mercer
Ian Mercer

Continuous Integration -> Continuous Deployment

What is "quality" in terms of a released software product or website?

Ian Mercer
Ian Mercer

Making a bootable Windows 7 USB Memory Stick

Here's how I made a bootable USB memory stick for Windows 7

Ian Mercer
Ian Mercer

Tip: getting the index in a foreeach statement

A tip on using LINQ's Select expression with an index

Ian Mercer
Ian Mercer

SQL Server - error: 18456, severity: 14, state: 38 - Incorrect Login

A rant about developers using the same message for different errors

Ian Mercer
Ian Mercer

WCF and the SYSTEM account

Namespace reservations and http.sys, my, oh my!

Ian Mercer
Ian Mercer

Mixed mode assembly errors after upgrade to .NET 4 Beta 2

Fixing this error was fairly simple

Ian Mercer
Ian Mercer

Shortened URLs should be treated like a Codec ...

Expanding URLs would help users decide whether or not to click a link

Ian Mercer
Ian Mercer

Tagging File Systems

Isn't it time we stopped knowing which drive our file is on?

Ian Mercer
Ian Mercer

A great site for developing and testing regular expressions

Just a link to a site I found useful

Ian Mercer
Ian Mercer

Introducing Jigsaw menus

A novel UI for menus that combines a breadcrumb and a menu in one visual metaphor

Ian Mercer
Ian Mercer

Fix for IE's overflow:hidden problem

Ian Mercer
Ian Mercer

A better Tail program for Windows

A comparison of tail programs for Windows

Ian Mercer
Ian Mercer

Measuring website browser performance

Found this great resource on website performance

Ian Mercer
Ian Mercer

Amazon Instance vs Dedicated Server comparison

Some benchmark performance for Amazon vs a dedicated server

Ian Mercer
Ian Mercer

Agile Software Development is Like Sailing

You cannot tack too often when sailing or you get nowhere. Agile is a bit like that.

Ian Mercer
Ian Mercer

Javascript error reporting

Sending client-side errors back to a server for analysis

Ian Mercer
Ian Mercer

AntiVirus Software is the Worst Software!

When your anti-virus software starts stealing your personal data, it's time to remove it!

Ian Mercer
Ian Mercer

ASP.NET Custom Validation

How to solve a problem encountered with custom validation in ASP.NET

Ian Mercer
Ian Mercer

Optimization Advice

Some advice on software optimization

Ian Mercer
Ian Mercer

Google Chart API

Ian Mercer
Ian Mercer

Cache optimized scanning of pairwise combinations of values

Using space-filling curves to optimize caching

Ian Mercer
Ian Mercer

Threading and User Interfaces

A rant about how few software programs get threading right

Ian Mercer
Ian Mercer

Take out the trash!

Why Windows shutdown takes so long

Ian Mercer
Ian Mercer

Dell upgrades - a pricey way to go

Ian Mercer
Ian Mercer

Programming mostly C#

Ian's advice on programming

Ian Mercer
Ian Mercer