jones.busy

technical musings of a caffeine converter

NAVIGATION - SEARCH

Adding Unique Constraint to column using EF Code First

First of all, it has been a very long time since this blog was last active. About 2 and a half years in all. That’s also the same age as my daughter. Spare time? What is that, exactly?!

Anyway, I have recently been working on my own project and decided to dive deeper into the Code First approach of Entity Framework. In particular, I do like the idea of being able to design your entities in an OO fashion and have that matched in the Database. This is more appealing when you can use the Fluent API to manage relational rules such as required properties without contaminating the core entity itself. I like it a lot.

However, I came a cropper the other day when I decided I wanted to add a Unique constraint on to the Name property of my Company table. Not an unusual request, so I was a little baffled as to why I did not have the following as an operation in Fluent API:

   1: public CompanyConfiguration()

   2: {

   3:     Property(x => x.Name).IsUnique();

   4:     Property(x => x.Name).IsRequired();

   5:     Property(x => x.Name).HasMaxLength(256);

   6: }

 

BTW, this is part of a Configuration class based on EntityTypeConfiguration<T>. The line that did not compile is the IsUnique() one.

Digging around on the web, I noticed that such functionality was planned for EF 5.0 but got pulled. My suspicions lie around the fact that this is a table constraint, rather than a field attribute so it would have more of an impact on how it is used. Either way, I had a look at how people worked around this and a common approach was to execute an “ALTER TABLE…..” command somewhere sensible such as the Database Initializer class. This was the approach I decided to take myself but I wanted to do so in a way that allowed a more DRY approach for any property in any class I wanted to set a Unique constraint on

The result was the following method in my DbContext Initializer class:

   1: private void AddUniqueConstraint<T>(Expression<Func<object>> expression) 

   2:             where T : DataEntityBase

   3: {

   4:    var columnName = String.Empty;

   5:  

   6:    if (expression.Body is MemberExpression)

   7:    {

   8:        columnName = ((MemberExpression)(expression.Body)).Member.Name;

   9:    }

  10:    else if (expression.Body is UnaryExpression)

  11:    {

  12:        columnName = ((MemberExpression)(((UnaryExpression)(expression.Body)).Operand)).Member.Name;                

  13:    }

  14:  

  15:    var script = "ALTER TABLE {0} ADD CONSTRAINT uc_{1} UNIQUE ({1})".FormatWith(Pluralizer.Pluralize(typeof(T).Name), columnName);

  16:    _context.Database.ExecuteSqlCommand(script);

  17: }

It’s not entirely tidy – you have to create an instance of the class in order to pass in the property name so perhaps someone out there can recommend a different approach – maybe using reflection?

In any case, it’s doing a job for me. For reference, GlobalContext is my DbContext class, DataEntityBase is an abstract class that holds common properties for classes to be added to the Database and FormatWith is simply a String extension method I use in preference to String.Format(“…”) etc.

note: my original approach to resolving the property name was flawed in so much as it would throw an exception if the expression.Body was a UnaryExpression, not a MemberExpression. I have since rectified this and updated the post accordingly.

Add comment