Entity Framework – Table Per Type – Gotcha

Entity Framework Table Per Type (TPT) implementation has a little gotcha ( little is really relative ). When we create table per type we will have orphan rows if the parent gets deleted and there will be orphan rows in the parent if the child gets deleted. This causes a funny picture to emerge with very bad consequences.

Gotcha

Sad Panda

Now, to get around this my simple workaround was to alter the relationships in my seed method.

context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Match] DROP CONSTRAINT [FK_ParikshaDev.Match_ParikshaDev.Questions_QuestionId]" );
context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Match] ADD CONSTRAINT [FK_ParikshaDev.Match_ParikshaDev.Questions_QuestionId] FOREIGN KEY (QuestionId) REFERENCES [ParikshaDev].[Questions] (QuestionId) ON DELETE CASCADE" );

context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Brief] DROP CONSTRAINT [FK_ParikshaDev.Brief_ParikshaDev.Questions_QuestionId]" );
context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Brief] ADD CONSTRAINT [FK_ParikshaDev.Brief_ParikshaDev.Questions_QuestionId] FOREIGN KEY (QuestionId) REFERENCES [ParikshaDev].[Questions] (QuestionId) ON DELETE CASCADE" );

context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Choice] DROP CONSTRAINT [FK_ParikshaDev.Choice_ParikshaDev.Questions_QuestionId]" );
context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Choice] ADD CONSTRAINT [FK_ParikshaDev.Choice_ParikshaDev.Questions_QuestionId] FOREIGN KEY (QuestionId) REFERENCES [ParikshaDev].[Questions] (QuestionId) ON DELETE CASCADE" );

context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Custom] DROP CONSTRAINT [FK_ParikshaDev.Custom_ParikshaDev.Questions_QuestionId]" );
context.Database.ExecuteSqlCommand( @"ALTER TABLE [ParikshaDev].[Custom] ADD CONSTRAINT [FK_ParikshaDev.Custom_ParikshaDev.Questions_QuestionId] FOREIGN KEY (QuestionId) REFERENCES [ParikshaDev].[Questions] (QuestionId) ON DELETE CASCADE" );

Fix!

Better Panda

There are things that can be better in this code. We can have the Schema name and the Table name statically typed with the help of the context. The relationship name was arrived at by making an educated guess about the relationships and then confirming them by having a look at the db that was generated previously.
I would consider this as a bug in EF. I didn’t see any way to configure this behaviour with the fluent api. I expected this to be clearly stated in the documentation or a flag or a switch be made available in the api for configuration.

Advertisements

Entity Framework 6 – Migrations

Migrations are needed in our day to day work and EF migrations story has become a lot better over the last year. When your database changes rapidly then migrations are needed to keep pace. Ofcourse this is nothing new, but it wasn’t available in EF before.

Enable Migrations

To enable the migrations go to Tools -> Library Package Manager -> Package Manager Console.
Type Enable-Migrations in the console.
-Force parameter is optionally available if you want to overwrite an existing migrations.

Creating a migration

Once the migrations are enabled, use the Add-Migrations command to add a migration. Give a logical name to the migration, attach a issue number if needed to the migration name like myawesomemigration-issue#1234. It really helps when you want to go up and down the migrations. When you choose to enable migrations, do it in the correct project if you have multiple projects in the solution.
Once the migration has been added, then you will see two new files within a new folder Migrations pop in. One of these is the migrations file which will have your nice name and the other one is Configuration.cs which will have your configuration. Your awesome migrations file will have all your create table syntax, this seems to be a DSL. You can do your funny bits here, add an Index tweak mania can begin here. Do read the file carefully though for the first few times. It should not be too dense and it should all make sense. If something looks off highly likely that your configuration in the model is fishy. It has two methods which allow you to move up and down between the migrations.

Configuration.cs is a useful class. It has the seed method with which you can seed you database. You can seed your db with test data which you will need for running the system tests (which you should with EF).

Updating your database

All said and done, time to update our database to the latest version. In the package manager console, write
Update-Database -Verbose. The verbose flag gives you back the sql as well for you to see. Options are available to script it out to a file as well if you want to hand it over to someone else.

As of this writing EF 6 beta has broken this bit bug
You should be safe on previous bits ( till alpha or EF 5 and below ).

Have a look at your database when the command finishes, your database should be updated.

Irritant

Next, I will write about a gotcha that made me think long and hard if I was very dumb.

Entity Framework 6 – What’s new ?

EF6 is still in alpha stages but you can go grab it now via nuget.
What is new ?
A whole lot find about it here EF6

Also, do read this post about migrating to EF6 if you are migrating an existing application.
EF6 Migration

I was able to use a few of the features that are new. I had earlier blogged about EF(EF 4.1) when V4.1 came out, I am basically using the same model to see if life is any easier with V6. The model is fairly simple. There are users, users can questions and questions are of different types. A user creates Test which is composed of many questions.

Earlier, there was no easy way of doing Enum in EF Code First. That limitation is no longer there.
Now, the questions can be of varying difficulty levels so now we can do the following.

    public enum Difficulty
    {
        Trivial = 1,
        Easy = 2,
        Medium = 3,
        Hard = 4,
        Difficult = 5
    }

    /// <summary>
    /// Question is the base type which describes a Question. Other types derive from Question.
    /// </summary>
    public class Question
    {
        /// <summary>
        /// Gets or sets the Difficulty
        /// </summary>
        public Difficulty Difficulty { get; set; }

....more properties 
    }

Similarly we can have UserRole as well.

EF now has the option to specify the default schema is also available now. It can be done as shown below.


modelBuilder.HasDefaultSchema("ParikshaDev");

EF now has the ability to provide custom conventions as well. The following conventions are available to us.

  • Lightweight Conventions
  • Configuration Conventions
  • Custom Attributes
  • Model-based Conventions

Read more about it here EF Conventions.
I will cross-post an important point about the order of execution. If you have many conventions then the order will become important. I haven’t written a lot of conventions but my guess is that this could get tricky if not done with care.

  1. IConfigurationConvention-based and lightweight conventions
  2. IEdmConvention-based conventions
  3. IDbConvention-based conventions
  4. IDbMappingConvention-based conventions

A conventions that I have used is Lightweight conventions. We sometimes want to append custom names to our table names as per the convention of our customers.


modelBuilder.Entities().Configure(_ => _.ToTable("CustomTable"+ _.ClrType.Name));

Similarly, other conventions are also available. The most common ones that I see being used will be Lightweight and Configuration Conventions.

Next, we will have a look at enabling migrations and an irritant that made me go nuts for a few months.

Entity Framework – Generic Repository Pattern – Part 2

In the previous post I wrote about an implementation of the Generic Repository pattern for EF. There is so much data on the web that it is difficult to understand what might actually work for you. All of this can drive you Crazy. Throw in my version of crazy as well Crazy2

Now that you have checked out the links and gone crazy reading how we all wanted a code review for this, it tells you the developer about the levels of confusion this pattern can create. One of my first thoughts was something like this Honestly, I don’t even remember what my exact thoughts were when I was prototyping the code.

In Part1 I did say that I will highlight some of the issues that I faced (remember YMMV). I didn’t do TDD for this ( sue me 🙂 ).

  • Testing is not as pain-free as it seems. What layers to test and how to test them ?
    After all of this code is for separating components and making life easier in the long run by increasing our confidence in the code.
    If EF is already a UoW and Repository then what are doing building a layer on top of this ?
  • We end up having a service layer anyway since our application logic is often non trivial and needs to be kept separate. Then the real fun begins with tests.
  • Mocking EF is a pain since you don’t have an IDbContext available. Get your fake data in there , then mock the repository and then mock the repository methods, then if you want to mock something like Includes() you will have some real fun. The idea is not mock EF, but even checking anything that sits on top of it can become an exercise in itself.
    After writing the tests, I can confidently say that my confidence in my code had not increased the manner I had expected (you bet this confidence thing is funny business) . I often had the feeling of buyer’s remorse.
  • It felt like I was on the wrong path. What do we really care about ? We need to get our data in and out of our database. That is it. How does making an abstraction help? It does not help that much. We are working exactly with the IQueryable that EF gives us back. Some implementations also have methods like FindById(..) , SortBy(..) etc…
    We need to concentrate on queries in our application. Make queries first class in your application.
  • Was all of this useless ? No, not quite. Implementing the Repository Pattern this way is, the pattern is not useless.
    We leak our data access technology Entity Framework into the Application Layer. A UnitOfWork driven implementation is actually very useful.
    I have not given that a go in code but here is something to start with Repository Pattern

In conclusion, it was both fun and enriching to hit roadblocks and see why the developers are moving to and from the Repository Pattern. I was working with EF6 and that came with its fair share of headaches. I will blog about that soon. Stay hungry, stay awesome.

Entity Framework – Generic repository Pattern – Part 1

The generic repository pattern has been a matter of discussion on the forums and SO. Developers are not convinced that it solves a problem, yet everyday SO has more questions about the pattern. Clearly, something is not right. I tried to implement the pattern in a way I thought fit. I changed my mind a million times during the process, that tells you that the process was not smooth. You can find several implementations of the pattern scattered all over the internet. I will not pass a opinion on the pattern. I would rather tell you what issues I faced and I how solved (or not) them.

First I created an IRepository and IUnitOfwork

   public interface IRepository
    {
        T Add(T entity);

        T Remove(T entity);

        T Update(T entity);

        IQueryable Query();        
    }

    public interface IUnitOfWork : IDisposable
    {
        void Commit();
    }

Next, let’s get the EF specific implementation up and running.


/// <summary>
    /// A EFRepository represents the repository for performing operations on the
    /// Entity using the EntityFramework.
    /// </summary>
    /// <typeparam name="T">T is the Entity</typeparam>
    public class EFRepository<T> : IRepository<T> where T : class
{
        /// <summary>
        /// This is set in the constructor and provides access to the underlying EntityFramework methods
        /// </summary>
        private DbSet<T> _dbSet;

        /// <summary>
        /// The context for working with the EntityFramework. This is set in the constructor.
        /// </summary>
        private DbContext _dataContext;

        /// <summary>
        /// Initialises a new instance of Repository for <see cref="T"/>
        /// </summary>
        /// <param name="unitOfWork">IUnitOfWork</param>
        /// <param name="dataContext">DbContext</param>
        /// <exception cref="ArgumentNullException">Throws ArgumentNullException if any of the arguments is null</exception>
        public EFRepository(IUnitOfWork unitOfWork, DbContext dataContext)
        {
            if (unitOfWork == null)
            {
                throw new ArgumentNullException("unitOfWork", "unitOfWork cannot be null");
            }

            if (dataContext == null)
            {
                throw new ArgumentNullException("dataContext", "dataContext cannot be null");
            }

            var EfUnitOfWork = unitOfWork as EFUnitOfWork;            
            _dataContext = dataContext;
            _dbSet = _dataContext.Set<T>();
        }

        /// <summary>
        /// Adds the specified Entity to the DbSet of the context.
        /// The Entity is inserted only when UnitOfWork is commited.
        /// </summary>
        /// <param name="item">The Entity to be added</param>
        /// <returns>The added Entity</returns>
        public T Add(T item)
        {
            return _dbSet.Add(item);
        }

        /// <summary>
        /// Removes the specified Entity from the DbSet of the context.
        /// The Entity is removed only when UnitOfWork is commited.
        /// </summary>
        /// <param name="item">The Entity to be removed</param>
        /// <returns>The Entity removed from the underlying DbSet</returns>
        public T Remove(T item)
        {
            return _dbSet.Remove(item);
        }

        /// <summary>
        /// Removes the specified Entity from the DbSet of the context.
        /// The Entity is removed only when UnitOfWork is commited.
        /// </summary>
        /// <param name="item">The Entity to be updated</param>
        /// <returns>the Entity removed from the underlying DbSet</returns>
        public T Update(T item)
        {
            var updated = _dbSet.Attach(item);
            _dataContext.Entry(item).State = EntityState.Modified;
            return updated;
        }

        /// <summary>
        /// Provides the caller with the underlying DbSet.
        /// </summary>
        /// <returns>An IQueryable to run queries against the underlying DbSet</returns>
        public IQueryable<T> Query()
        {
            return _dbSet;
        }

Follow this up with an implementation of EFUnitOfWork


/// <summary>
    /// Represents an IUnitOfWork for Entity Framework
    /// </summary>
    public class EFUnitOfWork : IUnitOfWork
    {
        /// <summary>
        /// The DbContext for the UnitOfWork
        /// </summary>
        private DbContext _context;

        /// <summary>
        /// Private field to check if the context has been disposed
        /// </summary>
        private bool _disposed;

        /// <summary>
        /// Initialises a new instance of EfUnitOfWork <see cref="EFUnitOfWork"/>
        /// </summary>
        /// <param name="context">DbContex for the UnitOfWork</param>
        public EFUnitOfWork(DbContext context)
        {
            if (context == null)
            {
                throw new UnitOfWorkException();
            }

            _context = context;
        }

        /// <summary>
        /// Method to e called when a UnitOfWork is to be committed.
        /// </summary>
        public void Commit()
        {
            _context.SaveChanges();
        }

        // Implement IDisposable.       
        public void Dispose()
        {
            Dispose(true);

            // Take yourself off the Finalization queue to prevent finalization code for object from executing a second time.
            GC.SuppressFinalize(this);
        }
       
        protected virtual void Dispose(bool disposing)
        {
            // Check to see if Dispose has already been called.
            if (!_disposed)
            {
                // If disposing equals true, dispose all managed and unmanaged resources.
                if (disposing)
                {
                    // Dispose managed resources.
                    if (_context != null)
                    {
                        _context.Dispose();
                    }
                }             
            }

            _disposed = true;
        }
    }

    [Serializable]
    public class UnitOfWorkException : Exception
    {
        public override string Message
        {
            get
            {
                return "The parameter must be EFUnitOfWork";
            }
        }
    }

The above code is at Github
Are there alternatives ?
Oh yes plenty. Some just differ in implementation details , but others differ in philosophy altogether.
Next time I will blog about a different one that I thought of initially. Then later I will write about the problems we face.
Pick and choose what fits.

Entity Framework 4.1 Associations

Associations are the basic feature of any ORM framework and as such are fairly simple to understand and implement in Entity Framework.
First we will create a simple one -many bi-directional relationship. This relationship is created between two new classes Subject and Standard.

public class Standard
    {
        public int StandardId { get; set; }
        public String StandardName { get; set; }
	 public virtual ICollection<Subject> Subjects{ get; set;}
    }
public class Subject
    {
        public int SubjectId { get; set; }
        public String SubjectName { get; set; }
        public String SubjectCategory { get; set; }
        public int  StandardId { get; set; }
        public virtual Standard Standard { get; set; }
    }

The 1..M relationship between Standard and Subject is created by having an ICollection for the standard and a foreign key for the Subject entity.
We can change the name of the Foreign Key to deviate from the convention but then we will have to explicitly specify which property will serve as the Foreign Key for the navigation property.
The bi-directionality is established by having navigation properties in both the entities. These properties are declared as virtual to enable lazy loading.
Now, to configure this we will require the following mapping.

       modelBuilder.Entity<Subject>()
                         .HasRequired(s => s.Standard)
                         .HasMany( sd  => sd.Subjects)
                         .HasForeignKey(s => s.StandardId)
                         .WillCascadeOnDelete(true);


Figure 1 A One-Many relationship between Standards and Subjects

More complicated mapping can be done when we connect the different tables together.
Assume that we do not want the questions created by a user to be deleted when we delete the user. But if delete the subject then the questions related to that subject must also be deleted. First, we modify the POCO classes to establish a relationship and then map our requirement to the database.

public class QuestionDescriptor
    {
        public int QuestionDescriptorId { get; set; }
        public int Rating { get; set; }
        public int Difficulty { get; set; }
        public DateTime DateOfCreation { get; set; }
        public String QuestionText { get; set; }
        public String Answer { get; set; }
        public int UserDetailId { get; set; }
        public virtual UserDetail Creator { get; set; }
        public int SubjectId { get; set; }
        public virtual Subject Subject { get; set; }        
    }

The relationships are Uni-directional for now (can be made bi-directional for querying requirements).
We configure our requirements as follows.

    modelBuilder.Entity<QuestionDescriptor>()
                .HasRequired(qd => qd.Creator)
                .WithMany()
                .HasForeignKey(qd => qd.UserDetailId)
                .WillCascadeOnDelete(false);

    modelBuilder.Entity<QuestionDescriptor>()
                .HasRequired(qd => qd.Subject)
                .WithMany()
                .HasForeignKey(qd => qd.SubjectId)
                .WillCascadeOnDelete(true);

The default behavior of the Entity Framework is to have cascade delete as on for the relationships. We can turn this off as per our requirements. Also note how the Creator navigational property has been associated with the UserDetailId.


Figure 2 OnDeleteCascade in OFF state


Figure 3 OnDeleteCascade in ON state

Another requirement we often have is of many-many mapping between entities. To demonstrate this we can create an entity Test which will have many questions and a question can belong to more than one Test.

public class Test
    {
        public int TestId { get; set; }
        public DateTime DateOfCreation { get; set; } 
        public virtual ICollection<QuestionDescriptor> Questions {get;set;}
    }

In the configuration mapping we will configure it by assigning the keys and creating a table for the relationship.

modelBuilder.Entity<QuestionDescriptor>()
                .HasMany(qd => qd.Tests)
                .WithMany(t => t.Questions)
                .Map(mc =>
                         {
                             mc.MapLeftKey("QuestionDescriptorId");
                             mc.MapRightKey("TestId");
                             mc.ToTable("Test_Questions");});


Figure 4 Many – Many Relationship


Figure 5 The Database Diagram

Entity Framework Inheritance

Inheritance is the basic requirement when we go about making a model for our business. Inheritance in SQL based systems is restricted to “has a “relationships. In real world applications which are modeled in object oriented mechanisms this is the real paint point for the developers. The essence of ORM systems is to bridge the gap between these two worlds and allow a seamless layer of abstraction to exist for the database access.
Inheritance in general can be classified into 3 chief categories: –
1) Table Per Type (TPT)
2) Table Per Hierarchy (TPH)
3) Table Per Concrete Type (TPC)
Consider that following structure of inheritance.

public class QuestionDescriptor
    {
        public int QuestionDescriptorId { get; set; }
        public int Rating { get; set; }
        public int Difficulty { get; set; }
        public DateTime DateOfCreation { get; set; }
        public String QuestionText { get; set; }
        public String Answer { get; set; }        
    }
public class QuestionBrief : QuestionDescriptor
    {
        public bool Short { get; set; }
    }
public class QuestionImage : QuestionDescriptor
    {
       public String ImagePath { get; set; }
    }

Table Per Type represents the “IS A” relationship through Foreign Keys. This means that we need to map QuestionBrief and QuestionImage to separate tables in the database and have Foreign Key relations with the QuestionDescriptor table.
The mapping required is as follows: –

modelBuilder.Entity<QuestionBrief>().ToTable("Brief");
modelBuilder.Entity<QuestionImage>().ToTable("Image");

TPT strategy gives rise to a normalised database and the schema lends itself nicely to evolution. The primary drawback is that the queries formed are using Joins between table.

Figure 1 Table Per Type
This strategy involves creating a single table and having a discriminator column. Since discriminator is not the key and yet determines the values of the columns that belong to the subclasses, this strategy violates the third normal form. It also requires the properties of the subclasses to be nullable. However, it is the best performing strategy for both polymorphic (queries instance of a class and all instances of the sub-class) and non-polymorphic queries (queries returning only instances of a particular subclass).
This can be achieved with the following mapping: –

modelBuilder.Entity<QuestionDescriptor>()
                .Map<QuestionBrief>(m => m.Requires("QuestionType").HasValue("Brief"))
                .Map<QuestionImage>(m => m.Requires("QuestionType").HasValue("Image"));

The HasValue() method takes as a parameter an Object type so we can pass Boolean, integers etc. The type of parameter we pass to the HasValue() method will determine the datatype of the column in the database.

         
   modelBuilder.Entity<QuestionDescriptor>()
                .Map<QuestionBrief>(m => m.Requires("QuestionType").HasValue(1))
                .Map<QuestionImage>(m => m.Requires("QuestionType").HasValue(2));


Figure 2 Table Per Hierarchy
Table Per Concrete Type
Table per concrete type strategy pushes down the attributes of the base class to the sub-class. So, if we do not have many attributes in the base class and don’t care much for the polymorphism in our model we can also go for this strategy.
• TPC becomes tough when we have changes in the schema and the base class evolves to have more attributes and relationships.
• This approach also suffers with major ideological challenge in the sense that it discards polymorphism altogether and hence doesn’t support a dynamic environment with changing business requirements.
• This approach is recommended only when we are confident that the base class won’t change and we are not referring to the base class for multiple queries.