dev

EF Code First & Generate SQL Inserts From Seed Method

Entity Framework Code First is a very popular way of developing ORM based systems these days. Creating and updating Db schema using migrations is quite simple (except situations when ypu are updating types of key colums or similar operations which are changes the schema a lot). Migrations are good for development, but when you are updation dbase on UAT or in production, it is not possible to simple connection to Db from Visual Studio. You have to generate migration Sql script. It can be simply done via EF too. But the problem are static data inserted into Db in the Seed method. This method is called after applying last migration by VS.

A simple example of Seed method content:

  protected override void Seed(SampleContext context)
        {
            //user are not filled automatically

            var roles = new List<Role>
            {
                new Role{Id=1, Name="Admin", Description="System administration"},
                new Role{Id=2, Name="BasicUser", Description="User with low rights"},
                new Role{Id=3, Name="AdvanceUser", Description="User with advance rights"}
            };

            roles.ForEach(e => context.Roles.Add(e));
            context.SaveChanges();

            var countries = new List<Country>
            {
                new Country{Id=1, Name="France"},
                new Country{Id=2, Name="Germany"},
                new Country{Id=3, Name="Austria"},
                new Country{Id=4, Name="Czech Republic"},
                new Country{Id=5, Name="Spain"},
                new Country{Id=6, Name="Norway"}
                //next dozens of countries
            };

            countries.ForEach(e => context.Countries.Add(e));
            context.SaveChanges();
        }

But it is not possible to generate Sql inserts like scripts for transformation.
One solution can be to define data in seed method and next define Sql inserts in a
separate file. But this is an error prone solution when you forget to update one
of these sources. Next problem can be rewiting hundreds of lines (e.g. list of all
countries on the world).
For this purpose in our project, I have created a solution base on Xml and T4
template system. You simply define all static data in an Xml file. You can read
this file in Seed method and T4 template will generate Sql inserts from the
same Xml file too – sample diagram is depicted in followinf figure.

diagram
Process diagram

Sample Xml file with static data. In table element you define name of the table
(used by T4). includeId attribute is used to check if Id column should be set
into insert or not (because of autoincrement possibility). Last special thing is
postfix -n used to signalize the type of the column (to set ” around the value
or not).

<?xml version="1.0" encoding="utf-8" ?>
<data>
  <table includeId="true" name="Roles">
    <item id-n="1" name="Admin" description="System administration" />
    <item id-n="2" name="BasicUser" description="User with low rights" />
    <item id-n="3" name="AdvanceUser" description="User with advance rights" />
  </table>
  <table includeId="false" name="Countries">
    <item name="France" />
    <item name="Germany" />
    <item name="Austria" />
    <item name="Czech Republic" />
    <!-- next dozens of countries -->
  </table>
</data>

Source of the T4 template:

<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="System" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.IO" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="System.Xml.Linq" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Xml.Linq" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".sql" #>

   <#       var path = Path.GetDirectoryName(Host.TemplateFile);
            path = Path.Combine(Path.GetDirectoryName(Host.TemplateFile),file);

            var doc = XDocument.Load(path);
            var root = doc.Element(dataElement);

            foreach (var table in root.Elements())
            {
                foreach (var item in table.Elements())
                {
                    var res = FormatInsertStatement(item, table.Attribute(nameAttribute).Value,
                                bool.Parse(table.Attribute(includeIdAttribute).Value));
                    #><#= res #><#
                }
                #><#= Environment.NewLine #><#
            }

        
    #>

<#+
        const string file = "DbData.xml";
        const string nameAttribute = "name";
        const string includeIdAttribute = "includeId";
        const string idAttribute = "id";
        const string dataElement = "data";
        const string attributeNumberType = "-n";
        const string attributeTypeDelimiter = "-";
        string idNumberAttribute = string.Format("{0}{1}", idAttribute, attributeNumberType);

        private Tuple<string, string> FormatAttribute(XAttribute att, bool isLast)
        {
            var name = att.Name.ToString();
            var value = att.Value;
            if (!name.EndsWith(attributeNumberType))
            {
                value = string.Format("'{0}'", value);

            }
            else
            {
                name = name.Remove(name.IndexOf(attributeTypeDelimiter));
            }

            if (!isLast)
            {
                value = value + ",";
                name = name + ",";
            }
            return new Tuple<string, string>(name, value);
        }

        private string FormatInsertStatement(XElement elem, string tableName, bool includeId)
        {
            var attrs = elem.Attributes().ToArray();
            var attrStr = new StringBuilder(string.Format("INSERT INTO {0}(", tableName));
            var valStr = new StringBuilder("VALUES(");

            for (var i = 0; i < attrs.Length; ++i)
            {
                var attr = attrs[i];
                var attrName = attr.Name.ToString().ToUpper();
                if (!includeId && (attrName.Equals(idAttribute.ToUpper()) || attrName.Equals(idNumberAttribute.ToUpper())))
                {
                    continue;
                }
                var res = FormatAttribute(attr, attrs.Length == i + 1);
                attrStr.Append(res.Item1);
                valStr.Append(res.Item2);
            }

            return string.Format("{0}) {1});{2}", attrStr.ToString(), valStr.ToString(), Environment.NewLine);
        }

#>

Result of the template applied on the sample Xml file:

INSER INTO Roles(id,name,description) VALUES(1,'Admin','System administration');
INSER INTO Roles(id,name,description) VALUES(2,'BasicUser','User with low rights');
INSER INTO Roles(id,name,description) VALUES(3,'AdvanceUser','User with advance rights');

INSER INTO Countries(name) VALUES('France');
INSER INTO Countries(name) VALUES('Germany');
INSER INTO Countries(name) VALUES('Austria');
INSER INTO Countries(name) VALUES('Czech Republic');

Finallz updated Seed method using Xml file with static data:

        private const string DataFile = "DbData.xml";
        private const string dataElement = "data";

        protected override void Seed(SampleContext context)
        {
            var doc = XDocument.Load(DataFile);
            var root = doc.Element(dataElement);

            //user are not filled automatically

            var roles = from elem in GetTableData(root, "Roles").Elements()
                        select new Role
                        {
                            Id = int.Parse(elem.Attribute("id-n").Value),
                            Name = elem.Attribute("name").Value,
                            Description = elem.Attribute("description").Value
                        };

            roles.ToList().ForEach(e => context.Roles.Add(e));
            context.SaveChanges();

            var countries = from elem in GetTableData(root, "Countries").Elements()
                        select new Country
                        {
                            Name = elem.Attribute("name").Value,
                        };

            roles.ToList().ForEach(e => context.Roles.Add(e));
            context.SaveChanges();
        }

        private XElement GetTableData(XElement root, string tableName)
        {
            return root.Elements().Single(e => e.Attribute("name").Value.Equals(tableName));
        }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s