Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server
Tip/Trick

Entity and Mapping Class Generator for EF

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
29 Dec 2015CPOL5 min read 13.7K   498   11  
This tip provides a simple solution to automate the generation of entity and mapping classes for Entity Framework.

Introduction

Earlier this month, I published an article that describes standard naming conventions for tables and columns, and in the article, I provided SQL code that leverages the conventions to visualize dependencies and automatically validate and generate foreign key constraints.

With all of that in place, as a next step, you might want to automate the code generation for entity and mapping classes to support an implementation that uses Entity Framework.

Background

I take a relatively simple approach to code generation. After I run a code generator against a database, I might never run the generator again on the same database. Instead, I might simply modify and refactor the code just as I would any other code. In my view, the purpose of a code generator is not to automate the production of commercial-grade source code, but rather to give the developer a jumpstart in that direction. This is the purpose of the solution here.

There many different tools and techniques for generating Plain Old CLR Object (POCO) classes. To name just a few:

My solution is not better than any of these tools (or any others you might find). It is very simple - which is a double-edged sword, because it might not do everything (or anything) you want for your own project. However, if it does what you need, then it might be a great option.

I developed it mainly because I wanted to deepen my own understanding of Entity Framework, and also because I am a bit of a control freak when it comes to the data access layer... :)

The code generator here is a console application with a few basic settings to automate the generation of exactly (and only) the output I want. The outputs include:

  1. A C# class file containing an Entity class and a Mapping class for each table in the database. You might want these in separate files or even in separate projects; I write both classes to the same file for the sake of simplicity here.
  2. A database context class that inherits from System.Data.Entity.DbContext.
  3. A plain text file that summarizes the database schema, listing the column names in each table. It uses simple flags to identify required fields, optional fields, foreign keys, primary keys, and identity columns.
  4. A plain text file that contains the DOT syntax for a graph to visualize schema dependencies, and the image produced by GraphViz with this input.
  5. A plain text file that contains the DOT syntax for a graph to visualize table dependencies, and the image produced by GraphViz with this input.

Using the Code

There are a few things you'll need to do before you can run the code on your own database.

Step 1

First, and most important, your database must adhere to the naming conventions documented in the original article: SQL Server Table and Column Naming Conventions.

Step 2

Next, modify the script CreateMetadataObjects.sql so the MetaTable view includes the schemas in your database. You can use hexadecimal HTML color codes or GraphViz color names for your color-coding. (The source code includes a script to create a non-trivial sample database if you want a quick start using the code generator.) By default, the MetaTable view looks like this:

SQL
CREATE VIEW m.MetaTable
AS
    SELECT  TABLE_SCHEMA AS SchemaName
          , CASE TABLE_SCHEMA
              WHEN 'c' THEN 'Contact'
              WHEN 'e' THEN 'Email'
              WHEN 'm' THEN 'Metadata'
              WHEN 'o' THEN 'Content'
              WHEN 'w' THEN 'Workflow'
              WHEN 'x' THEN 'Extension'
              ELSE TABLE_SCHEMA
            END AS SchemaDescription
          , CASE TABLE_SCHEMA
              WHEN 'c' THEN 'Orange'
              WHEN 'e' THEN 'LimeGreen'
              WHEN 'm' THEN 'AntiqueWhite4'
              WHEN 'o' THEN 'DodgerBlue'
              WHEN 'w' THEN 'Crimson'
              WHEN 'x' THEN 'Purple4'
              ELSE 'Black'
            END AS SchemaColor
          , TABLE_NAME AS TableName
    FROM    INFORMATION_SCHEMA.TABLES
    WHERE   TABLE_TYPE = 'BASE TABLE'
            AND LOWER(TABLE_NAME) NOT IN (N'aspstatetempapplications',
                                          N'aspstatetempsessions',
                                          N'dtproperties', N'sysdiagrams');

Notice I have explicitly excluded tables that I don't want in my data model.

Step 3 (Optional)

Install GraphViz on your computer. GraphViz is an open source project for graph visualization. It is a great tool for representing structural information in diagrams.

Everything you need can be downloaded free from the project web site: www.graphviz.org

This web site provides a very good (and very simple) introduction to using the software: graphs.grevian.org

Step 4

Update the application settings and the connection string settings in App.config to match your local environment. In particular:

  • Make sure the CodeGenerator.OutputPath references a valid physical location in your local file system.
  • Disable GraphViz if you skipped Step 3; otherwise, make sure GraphViz.DotExePath matches your installation.
  • Update the connection string so it references your database.

Step 5

Execute the code generator from a console window or from a PowerShell window. The application will create the required metadata objects by executing the CreateMetadataObjects.sql script, and then invoke the Run method on the Generator class. The code looks like this:

C#
public void Run(string script)
{
    _databaseName = ConfigurationManager.AppSettings["CodeGenerator.DatabaseName"]; ;
    _path = ConfigurationManager.AppSettings["CodeGenerator.OutputPath"]; ;
    _metadata = new Metadata(script);

    WriteContextClass();
    WriteEntityAndMappingClasses();
    WriteSummaryText();

    var graphvizEnabled = bool.Parse(ConfigurationManager.AppSettings["GraphViz.Enabled"]);
    if (graphvizEnabled)
    {
        var graphvizDotExePath = ConfigurationManager.AppSettings["GraphViz.DotExePath"];
        WriteSchemaGraph(graphvizDotExePath);
        WriteTableGraph(graphvizDotExePath);
    }
}

I won't dig into the rest of the source for the generator here - you can do that yourself. Fair warning: it is "utility" code only, and not production quality. There are no unit tests and the exception handling is far from bullet-proof. On the other hand, the entire solution contains less than 600 lines of code, so it should be very easy to modify and customize where you need to do so.

All output files are written to the location specified by the appSetting key:

C#
CodeGenerator.OutputPath

Here is an example of the class output:

C#
using System;
using System.Collections.Generic;
using System.Data.Entity.ModelConfiguration;
using DatabaseGeneratedOption = System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption;

namespace Snap.Domain
{
    public class Job
    {
        public int ID { get; set; }
        public int NotificationID { get; set; }
        public DateTime UtcCreated { get; set; }
        public DateTime? UtcQueued { get; set; }
        public DateTime? UtcCompleted { get; set; }

        public virtual Notification Notification { get; set; }

        public virtual ICollection<Recipient> Recipients { get; set; }

        public Job()
        {
            UtcQueued = DateTime.Now;
            UtcCompleted = DateTime.Now;

            Recipients = new List<Recipient>();
        }
    }

    public class JobMapping : EntityTypeConfiguration<Job>
    {
        public JobMapping() : this("e") { }

        public JobMapping(string schema)
        {
            ToTable(schema + ".Job");
            HasKey(x => new { x.ID } );

            Property(x => x.ID)
                .HasColumnName("JobID")
                .IsRequired()
                .HasColumnType("int")
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
                ;
            Property(x => x.NotificationID)
                .HasColumnName("NotificationID")
                .IsRequired()
                .HasColumnType("int")
                ;
            Property(x => x.UtcCreated)
                .HasColumnName("UtcCreated")
                .IsRequired()
                .HasColumnType("smalldatetime")
                ;
            Property(x => x.UtcQueued)
                .HasColumnName("UtcQueued")
                .IsOptional()
                .HasColumnType("smalldatetime")
                ;
            Property(x => x.UtcCompleted)
                .HasColumnName("UtcCompleted")
                .IsOptional()
                .HasColumnType("smalldatetime")
                ;

            HasRequired(a => a.Notification)
                .WithMany(b => b.Jobs)
                .HasForeignKey(c => c.NotificationID);
        }
    }
}

As mentioned, the source code for the generator includes a script to create a non-trivial sample database if you want a quick start using the code generator, and I have attached to the article a zip file that contains a sample of all output files for this database, so you can quickly see whether or not the output meets any of the requirements for your project.

Comments, questions, and feedback are welcome - let me know if you find this solution helpful.

History

  • December 29, 2015 - First draft
  • December 31, 2015 - Fixed minor typos; fixed a bug in the source code; attached sample output files

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer Shift iQ
Canada Canada
I have been building software systems for more than 20 years, working for organizations that range from small non-profit associations in my local community to global Fortune 500 enterprises.

I specialize in the design and implementation of online database solutions. My work-related research interests include software design patterns and information architecture.

Comments and Discussions

 
-- There are no messages in this forum --