Performance Tuning of Entity Framework Requests

Most of my large projects are using Entity Framework. And Entity Framework is known to sometimes create crazy sql requests. Or rather it easily allows developers to create crazy queries. It is really easy to throw couple .Include() into your query and then return objects as they come from EF:

var products = dbContext.Products.Include(p => p.Tags)
                                 .Include(p => p.Reviews)
                                 .ToList(); // first query
foreach(var prod in products)
{
    var reviewers = prod.Reviews.Select(r => r.Owner).ToList(); // N+1
}

The above code is fictional, but most likely will produce N+1 issue. N+1 issue is when you don’t just issue a SELECT query, but follow up every returned row by another SELECT query to retrieve related entries. This is caused by EF feature called Lazy Loading. Read better explanation about Lazy Loading problem here. Basically think that your first query for Products returns 1000 records. Then in case of N+1, operation inside of foreach loop will produce another thousand queries to your database.

Continue reading

How We Do Database Integration Tests With Entity Framework Migrations

There is a follow-up article about integration tests in 2016. Recommended for reading if you are using VSTS and looking at Cake build system

Unit tests are fine and dandy, but if you persist your data in database, persistence code must be tested as well. Some possible bugs will never be picked up only by unit tests. One of the issues I’ve been burned by is entity validation, another is a missing foreign key relationship.

Validation issues can burn you very easy. Look at this bit of code:

public class Person 
{
    [Key]
    public Guid PersonId { get; set; }

    [Required]
    public String FirstName { get; set; }

    [Required]
    public String LastName { get; set; }
}

public void CreatePerson(string firstName, string lastName)
{
    var newPerson = new Person();
    newPerson.FirstName = firstName;

    // dbContext was injected via constructor
    dbContext.Persons.Add(newPerson); 
    dbContext.SaveChanges();
}

NB: This is just a sample code. Nowhere in my projects we do such things.

Continue reading

Moving from Entity Framework 5 to Entity Framework 6 and back or “Could not determine storage version”

Recently we have upgraded our project to run on EF 6. And this is still in beta in Deb branch. So this has not been deployed to most of our customers. And today I had to solve a bug for older version of our system that is still on EF5.

Long story short: I’ve tried to connect to DB that already had migrations from EF6, but I was connecting with EF5. And cryptic error messages emerged, just like EF loves to throw cryptic error messages, this time it was no exception, excuse the pun.

This time the exception is saying: The provider did not return a ProviderManifest instance. with internal exception saying Could not determine storage version; a valid storage connection or a version hint is required. Which made no sense at that point. Until you spend couple hours trying to figure out what is happening.

Let me go into EF migration system details slightly. EF stores database state information in __MigrationHistory table that looks like this for EF5:

EF5_MigrationHistory

For EF6 the structure of the table have slightly changed:

EF6_MIgrationHistory

Notice on the second screenshot there is another column for DbContext type name used for the migration. This is a new feature in EF6 – allows more than one context to live in one project and you can run migrations for both of them. Have not tried this one yet, but time will come pretty soon!

Also once we have run migrations in EF6, the version recorded next to the migration record have changed from 5.xx to 6.xx – this is also visible on the last screenshot.

And if you try to connect to this database with older version of EF, table structure throws the system off it’s tracks. I’ve tried updating version number in data, but it did not change anything, do don’t bother.

I have not found the solution to this issue, other than re-create database migrations in EF5. So be careful when you update to EF6 and run migrations – there is no way back to EF5.

Test for default constructors in models for Entity Framework

Entity Framework relies on models to have a default constructor with no parameters. But if you are working with DDD often you would like to have some parameters in your constructors. And then slam a protected default constructor just for EF not to throw exceptions.

So you end up with code like this:

public class Product
{
    protected Product()
    {
        // This is required for EF
    }

    public Product(String name)
    {
        Name = name;
    }

    public String Name { get; set; }
}

The first constructor must not be used by developers, so you make it protected – this is good enough for EF.

Continue reading

DRY for lambda expressions

I’m working with Entity Framework a lot and sometimes you can get yourself tied into a knot so hard, you can’t figure out wtf is happening and why simple looking LINQ query leads to about 1K actual SQL requests. This is not good, so be careful with what you are doing there.

Today I had to untie one of these knots: a simple looking LINQ was producing horrible SQL requests and made many of them. Mostly due to poor legacy structure of the DB we have to battle at the moment. Anyway, it turned out that instead one massive LINQ to Objects, it was more performant to make 3 similar request to 3 similar tables in SQL Server. And all those requests were with similar .Where() conditions. And it looked like this:

var holidayMovements = this.HolidayMovements
    .Where (m => (startDate <= m.Start && m.Start <= endDate)
        || (startDate <= m.End && m.End <= endDate)     
        || (m.Start <= startDate && endDate <= m.End));

var absenceMovements = this.AbsenceMovements
    .Where (m => (startDate <= m.Start && m.Start <= endDate)   
        || (startDate <= m.End && m.End <= endDate)             
        || (m.Start <= startDate && endDate <= m.End));         

notice that in both cases .Where() clauses are identical? And I have 3 of these. I could live with 2 instances of the same condition, as later down the line it might turn up that 2 of the conditions are not really the same and you need to change one of them.. and if you DRY-it out into a method, you might end up with more crutches than you need without DRYing. But 3 instances are prime candidate for refactoring this out into a function/variable.

But how can you separate lambda expression into a separate variable? Like this:

Expression<Func<PersonnelMovementBase, bool>> timeBoundaryCondition = 
    m => (startDate <= m.Start && m.Start <= endDate)   
            || (startDate <= m.End && m.End <= endDate) 
            || (m.Start <= startDate && endDate <= m.End);

var holidayMovements = this.HolidayMovements
    .Where (timeBoundaryCondition);

var absenceMovements = this.AbsenceMovements
    .Where (timeBoundaryCondition);         

Now condition is the same in the separate queries and the LINQ query looks much more sane now. Quite simple to my mind, but it took me more than 3 minutes to figure out how to create a lambda expression outside of the .Where(), so here you go. Just don’t forget to look this up when you need similar thing again.

Rollback attribute for NUnit and Entity framework

For integration testing it is very useful to wrap the tests in database transaction, and at the end of the test you just rollback the transaction, so any changes happened inside of the test are not persisted. And the next test run will be running on untouched data.

This is how the attribute looks:

using System;
using System.Transactions;
using NUnit.Framework;

/// <summary>
/// Rollback Attribute wraps test execution into a transaction and cancels the transaction once the test is finished.
/// You can use this attribute on single test methods or test classes/suites
/// </summary>
public class RollbackAttribute : Attribute, ITestAction
{
    private TransactionScope transaction;

    public void BeforeTest(TestDetails testDetails)
    {
        transaction = new TransactionScope();
    }

    public void AfterTest(TestDetails testDetails)
    {
        transaction.Dispose();
    }

    public ActionTargets Targets
    {
        get { return ActionTargets.Test; }
    }
}

Nothing fancy, just used NUnit’s ability to create custom attributes.

The tests will look like

[Test, Rollback]
public void YourTest() {
    // test method body
}

Alternatively you can apply Rollback attribute on the test suite and every test will be wrapped into a transaction automatically. Save you littering Rollback attribute on all the test methods.

[TestFixture, Rollback]
public class ClassUnderTestTests{
   // Test methods
}

Enjoy!

Entity Framework, Update-Database command gives “Object reference not set” error

If Entity Framework (version below 5.1) on your attempt to Update-Database gives you error of “Object reference not set”, change the default start-up project to MVC project, not anything else. And try the same thing again.
This reported to be fixed in the latest 5.x version of EF, but it is a little annoyance just now.

p.s. This can be eased with parameter: -StartupProject “helloMVC” when running Update-Database command

EF 4.3 Database Migration Commands Cheatsheet

Code-Based Migrations

Original: http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-code-based-migrations-walkthrough.aspx

Enable-Migrations
Add-Migration – scaffold next migration based on changes made to the model.
Update-Database – update database model to the latest migration model
Update-Database -Verbose – update database model and output SQL generated
Update-Database -TargetMigration: “someMigration” – update database up (or down) to a “revision”
Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:”AddPostAbstract” Generate SQL script of the migrations
SQL(“update sometable set somefield=’hi'”) – can insert custom SQL into migration class.

Automatic Migrations

Original here: http://blogs.msdn.com/b/adonet/archive/2012/02/09/ef-4-3-automatic-migrations-walkthrough.aspx

Enable-Migrations -EnableAutomaticMigrations -Force – turn on automatic migrations

Basically does the same as code-based migrations, but before doing Database-update it automatically calls Add-Migration and gives it a name.