Entity Framework non null foreign key migration

Overview

This post gives a quick overview of how to use Entity Framework migrations and a detailed example of how to handle the addition of a new non null foreign keyed column to an existing table with data in it. Not a trivial process as you will see.
Full source code is provided, there are three versions of the same solution, one for each of the phases described below.

Introduction

Having source control over your SQL database has been a challenge for a while, I've seen companies with the attitude - the database is the source control, and they were not joking! I've seen a few companies use a dedicated SQL directory within their source code for table creation, stored proc and seeding scripts and this worked ok, but it seemed to put up a barrier between development and dbeng.

You can also go down the route of a dedicated piece of software that is supposed to manage it all for you but they are expensive, and in my experience, buggy.

Entity Framework Migrations

Enter Entity Migrations to fill the gap! It allows you to use your source code as the source control for your database! If you haven’t already worked with EF code first, you should probably stop here and review that topic. I've been using EF migrations for a while, there are plenty of tutorials out there but none deal with the scenario I’m handling.

The Problem

I have a Vehicle class, and a TyreType class. This is my starting structure, after my first migration I will have tables like those shown here. Assume that the application has been used for a while and there will be data in both tables.

Initial DB layout

Now I want to add a TyreColor class which will be referenced by the TyreType class.

public class TyreType
    {
        public int TyreTypeID { get; set; }

        //snip

        public int TyreColorId { get; set; }
        public virtual TyreColor TyreColor { get; set; }
    }

    public class TyreColor
    {
        public TyreColor()
        {
            this.TyreTypes = new List();
        }
        public int TyreColorId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public virtual ICollection TyreTypes { get; set; }
    }

There is also some plumbing in the context, you can see that in the source code provided.

This is a pretty standard one to many relationship. In the database it would look like -

DB layout after addition

The tricky part is dealing with the existing data in TyreType. I want to add a non null column, but I can’t because there are already rows of data.

The Solution

The quick answer is - firstly add a nullable TyreColorId column, then set some default value in all existing rows, then set the column to non null for future inserts. If that is enough to help you, good, if not, see the detailed explanation below.

Phase 1 - standard EF Migration creating the first two tables.

Step 1

Create the app, add the models, the context etc.

Go to the package manager console and type -

Enable-Migrations

Enable Migrations

This creates Migrations/Configuration.cs file, I'll come back to this shortly.

Step 2

Add a migration for the models in your project.

Back in the package manager type -

Add-Migration InitialCreate

This adds a file named something like - 201504280200272_InitialCreate.cs to the Migrations directory. Inside this file are Up() and Down() methods. The Up() will create the tables, set the primary key, create the foreign key and create an index.

And Down() drops the foreign key, index and tables.

Step 3

Before I push these changes to the database I will add some seeding code in Configuration.cs

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreType.AddOrUpdate(tt => new { tt.Name },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber" },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber" },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber" },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber" }
                 );
        }

Back in the package manager call -

Update-Database

The db and tables are created and the seeder run.

Tyre Type Table

Phase 2 – Adding the new table and foreign key fields


After creating this initial schema I added a TyreColor to the TyreType.

I want to make TyreColorId a required filed on TyreType and this is where everything starts to get complicated.
I’ve already seeded the TyreType with four rows and now I want to add a new column that is non null and will act as a foreign key. This is a problem, the existing four rows will have empty TyreColorId columns and SQL server won’t allow this.

To overcome this I first add the new column as a nullable, set a default value in the existing rows, and finally set the column to non null.

Step 1

Add TyreColorId and TyreColor to TyreType

  public class TyreType
    {
	   //snip
        public int TyreColorId { get; set; }

        public virtual TyreColor TyreColor { get; set; }
    }

In the package manger run -

Add-Migration AddingTyreColor

This adds a new migration, but I’m going to alter the Up() and Down() methods –

        public override void Up()
        {
            CreateTable(
                "dbo.TyreColor",
                c => new
                    {
                        TyreColorId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Description = c.String(),
                    })
                .PrimaryKey(t => t.TyreColorId);

            AddColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
           
            //Insert a default TyreColor
            Sql("Insert INTO dbo.TyreColor (Name, Description) VALUES ('Black', 'Very black')"); 
            
            //Set all null TyreType.TyreColorId to the default
            Sql("UPDATE dbo.TyreType SET TyreColorId = 1 WHERE TyreColorId IS NULL"); 

            AddForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor", "TyreColorId", cascadeDelete: true);
        }

        public override void Down()
        {
            DropForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor");
            DropColumn("dbo.TyreType", "TyreColorId");
            DropTable("dbo.TyreColor");
        }

See the inline notes for details.

I also updated the seed method

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreColor.AddOrUpdate(tc => tc.Name,
                 new TyreColor { Name = "Black", Description = "Black" },
                 new TyreColor { Name = "Gray", Description = "A little black" },
                 new TyreColor { Name = "White", Description = "Very white" },
                 new TyreColor { Name = "Black/White", Description = "A mix of black and white" }
                 );

            context.SaveChanges();
            
            // grab the one that was added
            TyreColor firstTyreColor = context.TyreColor.First();  
            
            context.TyreType.AddOrUpdate(tt => new { tt.Name, tt.TyreColorId },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber", TyreColorId = firstTyreColor.TyreColorId }
                 );
        }

In the package manager run,

Update-Database

The changes are applied and the seed method is called (I’ve changed the seed method a little, you can see that in the source code).

I now have very close to the table structure I want, I just need to set the TyreColorId column in TryeType to non nullable and add an index.

Phase 3 – Making the new column non null


In the package manger run -

Add-Migration AlterTyreColorToNonNull

This will create a new migration with empty Up() and Down() methods.
Add the following

        public override void Up()
        {
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: false));
            CreateIndex("dbo.TyreType", "TyreColorId"); 
        }
        
        public override void Down()
        {
            DropIndex("dbo.TyreType", new[] { "TyreColorId" });
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
        }

Now I have the structure I want

Data In Tables

And the data I want

Final Table Structure

Full source code is provided.

Mass Transit with RabbitMQ Hello World

Full source code is available here.

Below is an example of how to use RabbitMQ with Mass Transit service bus.

See here for instructions on how to install Rabbit MQ.

Mass Transit is installed via nuget, its current version is 2.9.9. You also need to add the RabbitMQ.Client, but stick with version 3.4.0 or less, there was a breaking change in subsequent versions that Mass Transit has not caught up with.

In simple terms Mass Transits lets you send messages from a producer to a consumer/worker. I’m not going into any more detail, there is documentation available elsewhere.

The Code

In the sender create a bus instance and connect it to RabbitMQ –

            var bus = ServiceBusFactory.New(cfg =>
            {
                cfg.UseRabbitMq();
                cfg.ReceiveFrom("rabbitmq://localhost/nodogmablog_queue_sender");
            });

Connect the worker to the bus –

            Bus.Initialize(sbc =>
            {
                cfg.UseRabbitMq();
                cfg.ReceiveFrom("rabbitmq://localhost/nodogmablog_queue_worker");

                cfg.Subscribe(subs => subs.Consumer<SimpleMessageConsumer>().Permanent());
            });

Send the message to the bus –

                var simpleMessage = new SimpleMessage() { Body = messageText };
                bus.Publish<SimpleMessage>(simpleMessage, pubContext =>
                {
                    //pubContext.SetHeader("Header1", "some value");
                    pubContext.SetDeliveryMode(DeliveryMode.Persistent);
                });

And finally the consume the message –

    public class SimpleMessageConsumer : Consumes<SimpleMessage>.Context
    {
        public void Consume(IConsumeContext<SimpleMessage> incomingMessage)
        {
            Console.WriteLine(incomingMessage.Message.Body);
        }
    }

You have to set SimpleMassTransitRabbitMQHelloWorld.Sender and SimpleMassTransitRabbitMQHelloWorld.Worker as start up projects by right clicking the solution and opening properties.

SetStartupProjects

That’s the basics of it, the full source code is available here.

Complex model validation using Fluent Validation

Full source code is available here.

A common problem is validating an object using a complicated set of rules.

I started using the Fluent Validation package some time back, it is commonly used with MVC and Web API applications but can be used with in any scenario that requires validation. It allows you to easily and quickly build flexible validation rules.

Of course it is possible to apply basic data annotations or build a custom validation attribute.

But out of the box Fluent Validation offers much more elaborate validators like, CreditCard, EmailAddress ExclusiveBetween and many more. It also has a Must validator that takes a predicate you define. This allows the creation of any complex rule.

For example, the validator can check that the person create request (shown below) has at least one active primary phone and at least one active primary email.

Fluent Validation can also be easily used in a console app, with data annotations you would have to jump through hoops to get validation working.

  public class PersonCreateRequestValidator : AbstractValidator<PersonCreateRequest>
    {
        public PersonCreateRequestValidator()
        {
            RuleFor(r => r.Firstname).NotEmpty();
            RuleFor(r => r.Lastname).NotEmpty();

            RuleFor(r => r.Addresses).NotNull();
            RuleFor(r => r.Addresses).NotEmpty();

            RuleFor(r => r.Addresses).Must(HaveAnActiveAndPrimary).WithMessage("One (and only one) address must be primary and active");
            RuleFor(r => r.Phones).Must(HaveAnActiveAndPrimary).WithMessage("One (and only one) phone must be primary and active");
        }

        private bool HaveAnActiveAndPrimary(IEnumerable<IActivePrimary> items)
        {
            if (items == null)
            {
                return false;
            }

            int activePrimary = items.Count(p => p.IsActive && p.IsPrimary);
            return (activePrimary == 1);
        }
    }

This a simple example of usage –

            var personCreateRequest = new PersonCreateRequest
            {
                PersonId = Guid.NewGuid(),
                Firstname = "Tom",
                Lastname = "Travers",
                Addresses = new List<Address>() { new Address { Street = "Main", IsActive = true, IsPrimary = false, }, new Address{Street = "Boylston", IsActive = true, IsPrimary = true} },
                Phones = new List<Phone>() { new Phone { PhoneNumber = "124",  IsActive = true, IsPrimary = false } }
            };

            var validator = new PersonCreateRequestValidator();
            ValidationResult result = validator.Validate(personCreateRequest);

Validating against multiple parts of the request

If you have the slightly more complicated scenario where you need either an active and primary phone or an active and primary address, add the following predicate –

        private bool AtLeastOneActiveAndPrimaryBetweenPhoneAndAddress(PersonCreateRequest p)
        {
            bool addressActiveAndPrimary = HaveAnActiveAndPrimary(p.Addresses);
            bool phoneActiveAndPrimary = HaveAnActiveAndPrimary(p.Phones);

            bool result = addressActiveAndPrimary || phoneActiveAndPrimary;

            return result;
        }

And call it like so –

        public PersonCreateRequestValidator()
        {
            RuleFor(r => r).Must(AtLeastOneActiveAndPrimaryBetweenPhoneAndAddress).WithName("request");

Of course you can’t now call Must(HaveAnActiveAndPrimary) on just the phones and addresses.

Models

  public class PersonCreateRequest
    {
        public Guid RequestId { get; set; }
        public Guid PersonId { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public List<Address> Addresses { get; set; }
        public List<Phone> Phones { get; set; }
    }

 

    public class Address : IActivePrimary
    {
        public string Street { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public bool IsPrimary { get; set; }
        public bool IsActive { get; set; }
    }

 

    public class Phone : IActivePrimary
    {
        public string PhoneNumber { get; set; }
        public bool IsPrimary { get; set; }
        public bool IsActive { get; set; }
    }

 

    public interface IActivePrimary
    {
        bool IsPrimary { get; set; }
        bool IsActive { get; set; }
    }

Full source code is available here.

Using JSON to store application preferences and configuration

Download full source code.

Storing configuration preferences in the database is not a hard task. It usually involves storing the name of the preference, the value and the type in the database. Then when retrieving the data you perform some sort of cast.

The casts tend to be a bit painful, but it works.

This is the call –

 
bool displayWidget = (bool) Convert.ChangeType(_preferenceManager.BadWayToCastToGetPreference("DisplayWidget"), typeof(bool));

And this is the called method –

 
        public object BadWayToCastToGetPreference<T>(string name)
        {
            var preference = Convert.ChangeType(_context.Preferences.SingleOrDefault(p => p.Name == name).Value, typeof(T));
            return preference;
        }

But this quickly falls apart when you want to store a list of something, this requires a custom way of generating a string that represents the list. A common way I’ve seen is to store the values as a pipe delimited list.

For example if you wanted to store three cities
Boston, New York and Seattle
they would become “Boston|New York|Seattle” when stored in the database.

Now you need a write code to turn that back into a list of strings.

The problem gets worse when you want to store some custom types, for example an emergency contact shaped like –

    public class EmergencyContact
    {
        public int Priority { get; set; }
        public string EmailAddress { get; set; }
    }

Or worse if you want to store a list of emergency contacts.

Rather than suffering all that pain, just use Json to serialize and deserialize the values. This makes life a lot easier.

I have a simple method to create the preference object that gets store in the database –

        private Preference CreatePreference(string name, string value, string type)
        {
            var preference = new Preference
            {
                Name = name,
                Value = value,
                Type = type,
                PreferenceID = Guid.NewGuid()
            };
            return preference;
        }

CreatePreference gets called like this for a simple string –

            string defaultEmail = "admin@example.com";
            Preference preference1 = CreatePreference("DefaultEmail", JsonConvert.SerializeObject(defaultEmail), defaultEmail.GetType().ToString());

And it’s the same when storing a more complex type.

            EmergencyContact[] secondaryEmergencyContacts = new[]
            {
                new EmergencyContact{ EmailAddress = "tom@example.com", Priority = 1},
                new EmergencyContact{ EmailAddress = "dick@example.com", Priority = 2},
                new EmergencyContact{ EmailAddress = "harry@example.com", Priority = 3},
            };
            Preference preference6 = CreatePreference("SecondaryEmergencyContacts",
                JsonConvert.SerializeObject(secondaryEmergencyContacts), secondaryEmergencyContacts.GetType().ToString());

To store these preferences in the database, its just simple Entity Framework.

            _context.Preferences.Add(preference1);
            _context.Preferences.Add(preference6);
            _context.SaveChanges();

And then to get the preferences back out of the database you call –

        public T GetPreference<T>(string name)
        {
            var preference =
                JsonConvert.DeserializeObject<T>(_context.Preferences.SingleOrDefault(p => p.Name == name).Value);
            return preference;
        }

Some might complain that it is slower than direct casts, and yes, it probably is. But if you haven’t measured it you shouldn’t optimize it. You can cache always cache preferences. And it is a lot neater than the alternative.

Download full source code.