Tag Archives: Entity Framework

Migrate Entity Framework migrations from automatic to code-based

Users of Entity Framework (EF) migrations must have noticed that there are two ways to perform migrations: automatic and code based.
With automatic migrations, EF compares the code-first model to the latest version that is stored in the __MigrationHistory system table in the database. After that, EF synchronizes the database with the code-first model and then stores a new model version in the __MigrationHistory table.
With code-based migrations, you have to explicitly specify all changes in code en EF doesn’t compare the models to detect changes.

In a project, we started using automatic migrations. Why automatic? Simply: it’s less work and at the time the drawbacks weren’t very clear. Unfortunately, after a year, we realized that this was the wrong choice. A new product version required changes that couldn’t simple be handled by automatic migrations without data loss. Time to move to code based migrations. This is easy with an existing database. Just create an initial migration with Add-Migration and the –IgnoreChanges flag and you have baseline. But this doesn’t work when you also a a requirement to create new databases from the migrations.

The solution is to script the database just before you’re moving to code-based migrations and execute this script in the Up() method of the initial migration. The key here is that this script has to check if database objects exist before creating them. This way it can also be used with existing databases.

Summary of migrations migration steps (SQL Server database):

  1. Before the migration, ensure that the database(s) and code-first model are in sync;
  2. Script the existing tables (in SQL Server Management Studio: Generate Scripts and then under Scripting Options –> Advanced, ensure that ‘Check for object existence’ is set to True). Add the script to your VS project that has the migrations. We need this later;
  3. In \Migrations\Configuration.cs turn off Automatic Migrations:
    internal sealed class Configuration : DbMigrationsConfiguration<PortalDbContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }
    }
    
  4. In the Package Manager Console enter ‘Add-Migration –IgnoreChanges’ and enter a name for the initial migration. This will create the baseline migration in the \MIgrations directory.
  5. Now the tricky bit: ensure that the script created in step 2 is executed in the Up() method of the initial migration. My Up() method looks like this:
    public override void Up()
    {
        // Run script that creates the tables while preserving
        // existing tables and data
        // The script is stored as embedded resource in the assembly
        var currentAssembly = Assembly.GetExecutingAssembly();
        using (var createScriptStream = 
            currentAssembly.GetManifestResourceStream("MyProject.Migrations.Scripts.CreateTablesFromAutomaticMigrations.sql"))
        {
            if (createScriptStream != null)
            {
                var createScript = new StreamReader(createScriptStream).ReadToEnd();
                // Get separate commands from the script (SQL Server uses GO as separator)
                var regex = new Regex(@"\r{0,1}\nGO\r{0,1}\n");            
                var commands = regex.Split(script);
                foreach (var command in commands)
                {
                    if (! string.IsNullOrEmpty(command))
                    {
                        Sql(command);
                    }
                }
            }
        }
    }
    
    

    In the Up() method you can execute arbitrary SQL commands with the Sql() method. Note that in the example, the script is read from an embbeded resource, but it’s also perfectly fine to store the script as a file somewhere and create a stream from that.

  6. Ready. Migrations are migrated.

Please leave a comment if you you know a better solution. I think this is still a little bit clumsy although it works for us.

Entity Framework 4 tips for NHibernate users

The last few weeks, I have worked on a project that uses Entity Framework 4 Code First (let’s call it EF from now). Normally, I use NHibernate for my OR-mapping needs and although EF works almost the same conceptually, there are differences. This post is about some of the issues that an NHibernate user can expect when working with EF.

Many to one associations and introduce a foreign key property

Let’s say it bluntly: EF supports Foreign Key associations  (foreign key id’s in your entities, besides many to one associations)  and you better use them, or you’ll experience a lot of pain. I started to model my entities with ‘proper’ object references but ran into 2 problems:

  • Filtering in collection properties causes select n+1 issues. So forget:
    Order.OrderLines.Where(ol => ol.Product.Id == productId)

    but do:

    Order.OrderLines.Where(ol => ol.ProductId == productId)

    to filter a collection property because the first will load all product instances;

  • When you override Equals() and GetHashCode()in your entities to use only the ID for object equality, you can assign an empty object with only the ID set as reference property in NHibernate but EF doesn’t fall for this trick. This is can be very useful, for example when setting references from a dropdownlist where you only have an ID of the referenced object.

It seems that EF just doesn’t leverage proxies like NHibernate does so it needs to have the actual object instance to do things instead of just the proxy.

The good thing however is that when you’re having both a foreign key ID property and an object reference in EF, these are synced automatically and only one column will be generated in the database and the only real issue is a little pollution of your entity.

Object reference not set to an instance of an object

So you’ve created a nice object graph and saved it in the database. Then after fetching the object graph back, some of the referenced objects are null even when the ID’s of the references are nicely sitting there in the database. WTF???

Well, EF doesn’t automatically load a referenced object unless it’s marked as virtual (lazy) or loaded explicitly via Include(). If you forget both, you’ll end up with the NullReferenceExceptions.

No Dictionary collection mapping

This is something not many people complain about, but I really miss the option to map dictionaries like Map in NHibernate. Also, NHibernate’s List mappings are not supported.

Cascade is a big black box

EF doesn’t have a way to specify how changes in your entity are cascaded into related entities. I’m not sure if this is a good or a bad thing. On one hand I miss having control over this, but on the other hand EF seemed to do well without setting anything.

Out of the box, EF code first generates CASCADE  DELETE database constraints on required associations but when your model gets only a little bit complex, you’ll get errors like “Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths”. Fortunately, you can turn this off in your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Remove cascade delete convention because it causes trouble when generating the DB.
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

    base.OnModelCreating(modelBuilder);
}

Explicitly set state when attaching objects to the DbContext

In NHibernate you can simply update an object that isn’t associated with a Session via Session.Update(). EF can do the same via Attach() but you must not forget to explicitly set the object state to modified, otherwise no updates occur:

dbContext.Customers.Attach(customer);
dbContext.Entry(customer).State = EntityState.Modified;
dbContext.SaveChanges();

More?

Please leave a comment if you want to share your own experiences.

Entity Framework 4 Code-First demo updated to Feature CTP3

The EF 4 code-first demo has been updated to reference the EF4 Feature CTP3 dll at its default location. To run the demo, you have to make sure that the you have it installed. Download the EF 4 Feature CTP3 at http://www.microsoft.com/downloads/details.aspx?FamilyID=af18e652-9ea7-478b-8b41-8424b94e3f58&displayLang=en.

Although the demo was originally developed with VS 2010 Beta 2 everything works perfectly fine on VS 2010 RTM.

You can find the demo sources at github: http://github.com/martijnboland/EF4-codeonly-demo where it’s also possible to download the sources all at once.

Entity Framework 4.0: a fresh start (with demo application)

Edited 2009-11-26: removed EF4 Feature CTP from demo package and added some code examples.

So, Entity Framework 1.0 pretty much sucks (compared to alternatives), but I’m glad to see that things have improved a lot in version 4.0 (we’ll call that EF4 from now). To see how the improvements work out, I did a quick spike that also gave me the opportunity to test some new ASP.NET MVC 2 features that I might blog about later.

What’s in it?

  • Entity Framework 4 with POCO entity objects and code-only configuration (no edmx);
  • Data access abstracted via repository interfaces;
  • ASP.NET 2 MVC for the UI;
  • Validation with Data Annotations;
  • Castle Windsor IoC container to wire the various components together;

EF4 POCO

In EF4, it’s now possible to use entity classes that don’t have to inherit from EntityObject. This allows for better testability and separation of concerns. This is how an entity looks in the demo:

public class Course
{
    public virtual int Id { get; set; }

    [Required(ErrorMessage="Course title is required")]
    public virtual string Title { get; set; }

    [Required(ErrorMessage = "Price is required")]
    [DataType(DataType.Currency)]
    [Range(10.00, double.MaxValue, ErrorMessage="The minimum price is {1}")]
    public virtual decimal Price { get; set; }

    public virtual ISet<Schedule> Schedules { get; set; }

    public Course()
    {
        this.Schedules = new HashSet<Schedule>();
    }
}

Note that all properties are marked virtual. This allows EF4 to do some magic with run-time proxy generation to allow lazy loading and change notification.

Also note that we can now easily add validation attributes (in this case from Sytem.ComponentModel.DataAnnotations). We don’t have to use those dreadful ‘buddy classes’ anymore to hold the meta-data.

Code-only configuration

EF4 now allows model-first design from the VS 2010 designer, but it can still get awkward with a lot of entities. Fortunately, the EF4 Feature CTP makes it possible to do everything in code.

So you now just create your POCO entity and add a mapping class that replaces the edmx designer:

public class CourseMapping : EntityConfiguration<Course>
{
    public CourseMapping()
    {
        HasKey(c => c.Id);
        Property(c => c.Id).IsIdentity();
        MapSingleType(c => new
        {
            courseid = c.Id,
            title = c.Title,
            price = c.Price
        }).ToTable("course");
        Property(c => c.Price).HasStoreType("money").HasPrecision(19, 4);
    }
}

Looks remarkably similar to Fluent NHibernate, doesn’t it? ;)

I’m not sure if this is the most optimal way to define the mapping, but it works. In the demo, there is a class CoursesContextBuilder that wraps the ContextBuilder from the EF4 Feature CTP where the mappings are added. It also serves as a factory for new ObjectContext instances () :

public class CoursesContextBuilder
{
    private ContextBuilder<ObjectContext> _builder;
    private string _defaultConnectionString;

    public CoursesContextBuilder(string defaultConnectionString)
    {
        this._defaultConnectionString = defaultConnectionString;
        this._builder = new ContextBuilder<ObjectContext>();
        ConfigureMappings();
    }

    private void ConfigureMappings()
    {
        _builder.Configurations.Add(new TeacherMapping());
        _builder.Configurations.Add(new CourseMapping());
        _builder.Configurations.Add(new ScheduleMapping());
    }

    public ObjectContext GetContext()
    {
        return GetContext(_defaultConnectionString);
    }

    public ObjectContext GetContext(string connectionString)
    {
        var context = _builder.Create(GetConnection(connectionString));
        context.ContextOptions.LazyLoadingEnabled = true;
        return context;
    }

    private DbConnection GetConnection(string connectionString)
    {
        // Hardcoded to SqlConnection for this demo.
        return new SqlConnection(connectionString);
    }
}

That’s all we need to get EF4 working. In theory, we can now do everything with the POCO classes and the ObjectContext that comes from the CoursesContextBuilder, but of course if we would use it this way, we are directly tied to EF again and there goes away our testability.

Repository implementation

To make sure our application code isn’t tied to EF directly, data access goes through Repository interfaces. In the demo app you can find a generic IRepository<T> interface that is implemented by an EfRepository<T> class. The EfRepository implementation uses the EF ObjectContext to perform queries and so on. Note that we added an extra IContextManager interface that the EfRepository depends on. The IContextManager is responsible for managing the lifetime of the ObjectContext that is obtained from the ContextBuilder. This way, the repository implementations don’t have to worry about creating and disposing ObjectContext instances. It’s just always always available.All specific Repository interfaces inherit from IRepository<T> and the specific implementations inherit from EfRepository<T>.

public interface ICourseRepository : IRepository<Course>
{
    void DeleteCourseWithSchedule(Course course);
    Ef4Poco.Domain.Course GetCourseWithSchedulesAndTeachers(int courseId);
    void RemoveScheduleFromCourse(Schedule schedule, Course course);
}
/// <summary>
/// Course-specific repository.
/// </summary>
public class CourseRepository : EfRepository<Course>, ICourseRepository
{
    public CourseRepository(IContextManager contextManager)
        : base(contextManager)
    { }

    public Course GetCourseWithSchedulesAndTeachers(int courseId)
    {
        // Wouldn't it be nice to have strong-typed includes?
        var query = from c in ObjectSet.Include("Schedules").Include("Schedules.Teacher")
                    where c.Id == courseId
                    select c;
        return query.Single();
    }

    public void RemoveScheduleFromCourse(Schedule schedule, Course course)
    {
        course.Schedules.Remove(schedule);
        CurrentObjectContext.DeleteObject(schedule);
        CurrentObjectContext.SaveChanges();
    }

    public void DeleteCourseWithSchedule(Course course)
    {
        // Howto configure cascade delete via code? This is a little cumbersome.
        var schedules = new List<Schedule>(course.Schedules);
        foreach (var schedule in schedules)
        {
            CurrentObjectContext.DeleteObject(schedule);
        }
        CurrentObjectContext.DeleteObject(course);
        CurrentObjectContext.SaveChanges();
    }
}

Consuming the data access interfaces

Now we have our data access interfaces in place, it’s time for consuming. This is plain simple. Below is an example of how a controller in the ASP.NET MVC app in the demo uses the interfaces:

public class TeachersController : Controller
{
    private ITeacherRepository _teacherRepository;

    public TeachersController(ITeacherRepository teacherRepository)
    {
        _teacherRepository = teacherRepository;
    }

    public ActionResult Index()
    {
        var teachers = _teacherRepository.Find().OrderBy(t => t.Name);
        return View(teachers);
    }

    [...snip other methods]
}

The demo app

You can download the demo to see what’s possible. It’s by no means a best practices example. Just a spike to test out various new technologies.

To run the demo, you’ll need Visual Studio 2010 Beta 2, the latest EF4 Feature CTP and SQL Server (Express).

Both, the web and the test project have a connection string in the config file that defaults to the .\SQLEXPRESS instance. Change that if you want to use a different instance. The database name doesn’t matter because a new database will be created the first time you run the application. Note that the reference to the EF4 feature CTP (Microsoft.Data.Entity.CTP.dll) points to C:\Program Files (x86)\Microsoft ADO.NET Entity Framework Feature CTP2\Binaries. On 32 bit machines, you’ll probably have to change that to C:\Program Files\…

Some observations

  • Entity Framework 4.0 is much, much better than version 1.0, especially for a model-first approach;
  • It’s not on par yet with NHibernate feature-wise (for example, I really miss cascade settings). Given the choice, I’d still opt for NHibernate, but it’s not a bad product anymore and I’d prefer it over LINQ to SQL at this time.

One year after the Entity Framework Vote of No Confidence…

About a year ago, a few people (called by some as ‘The NHibernate Mafia’) wrote a vote of no confidence against the Microsoft ADO.NET Entity Framework (EF), mainly because a big influential company like Microsoft was releasing an inferior tool set. See the text of the petition for the detailed reasoning.

My initial thoughts were ‘why worry, there are plenty alternatives like NHibernate, LLBLGen Pro or LINQ to SQL’ and although I agree with the content of the vote of no confidence, I decided not to sign. That was until realized that as a contractor, you often have to confirm to the technology that the customer has adopted, so it’s to be expected that a lot of future projects have EF as their O/R mapping layer because ‘Microsoft says it’s the way to do data access’. I signed and yes, it’s the selfish me that made me do it.

So now we’re one year later and I just started on a new contract where EF is being used and as I already feared one year ago things are not pretty. The previous developers seem to have struggled a lot, which resulted in poor performance and lots of workarounds.

And you know what: I don’t blame EF itself or the developers but only Microsoft marketing for labeling the Entity Framework as the ‘preferred magic way to do data access’ and all the surrounding blah of future products that will build on EF so people blindly take it as their weapon of choice.

Sorry for this somewhat negative post, but I had to write this off.