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