June 20, 2014

Entity Framework Headaches!

Filed under: .net — joel.cass @ 3:41 pm

I want to get away from query oriented code and especially the mess that can result from almost any ADO.Net implementation over time. I’ve been lucky to finally be able to work with the latest .net framework on a new project and thought I would give the Entity Framework (EF) a go. After all, it has worked quite well on other projects I have tried with MVC.

However, it seems that things are not so rosy when you come at it from a database-first perspective. Say that your database is designed by a proper DBA to have all the proper indexes, constraints, and data types in place, plus it’s locked down so you can’t manipulate with via code anyway. You would want to go database-first, right? Well, the tools we use should allow that.

I am using Visual Studio 2013, .net 4.5.1, and EF 6. And the experience has been anything but smooth.

First problem: Keys

Your Entity classes will not have keys. You will get the error “EntityType [Entity] has no key defined. Define the key for this EntityType”. But then you’re like, “the keys are there in the database! BLOODY HELL!”. So you look up, it seems you need to define the attribute [Key] above the field that represents the key in the database. Include the relevant namespace and you should be good to go, right? Wrong. Run a build and the class files are re-created, and changes are lost.

So what do you do? Open the edmx branch, find the relevant *.tt file, and:

Search for the string “simpleProperties”, then add the following like so (plus signs excluded):

var simpleProperties = typeMapper.GetSimpleProperties(entity);
    if (simpleProperties.Any())
        foreach (var edmProperty in simpleProperties)
+			if (ef.IsKey(edmProperty)) {
+				#>    [Key]
+<#		    }

..then, you will need to search for the method definition “UsingDirectives” and rewrite it as follows:

public string UsingDirectives(bool inHeader, bool includeCollections = true)
        return inHeader == string.IsNullOrEmpty(_code.VsNamespaceSuggestion())
            ? string.Format(
                "{0}using System;{1}{2}{3}",
                inHeader ? Environment.NewLine : "",
                Environment.NewLine + "using System.ComponentModel.DataAnnotations;",
                includeCollections ? (Environment.NewLine + "using System.Collections.Generic;") : "",
                inHeader ? "" : Environment.NewLine)
            : "";

Build your project, and hopefully the classes come out right this time.

Second problem: Performance on bulk actions

OK, so it’s running now. Say that you want to do a bulk delete on a table that you were using for temporary data. Well, EF is not good at that at all. Deleting 2,000 records takes about 3 minutes. What about 20,000? Don’t even bother. So you’ll need to hack around it:

                /* TOO SLOW!
                IEnumerable<TempRecord> aryRecords = objContext.TempRecords;
                foreach (TempRecord r in aryRecords)
                string strTableName = "TempRecord";
                DataContext.Database.ExecuteSqlCommand(String.Format("TRUNCATE TABLE {0}", strTableName));

So that works OK when connecting to the database, but what about when testing locally? Oops, next problem:

Second second problem: Database table names

When testing locally, the context will create a database using sqllocaldb.exe, that’s a nice idea. However this is where it fails: it creates the table names differently to the original schema. Say your table name was “TempRecord” (as some DB designers believe tables should NEVER be plurals), it will create the table in the temp database as “TempRecords”.

So begins the guessing game, as what makes it even worse is that the Entity Framework has NO METHOD FOR GETTING THE UNDERLYING TABLE NAME! DOUBLE BLOODY HELL!

So, what do you have to do? Run a fake query and then parse the SQL for the table name:

        public string GetTableName(DbSet dbset)
            string sql = dbset.ToString();
            Regex regex = new Regex("FROM (?<table>.*) AS");
            Match match = regex.Match(sql);

            string table = match.Groups["table"].Value;
            return table;

…and then update our preceding code:

                /* TOO SLOW!
                IEnumerable<TempRecord> aryRecords = objContext.TempRecords;
                foreach (TempRecord r in aryRecords)
                string strTableName = objContext.GetTableName(objContext.TempRecords);
                objContext.Database.ExecuteSqlCommand(String.Format("TRUNCATE TABLE {0}", strTableName));

Another problem solved.

Third problem: Schema changes

Finally, what happens when the schema changes? Easy, you just update the EDMX from the database. It all works, then you decide to run some tests and you get the error “Model backing [Context] context has changed since database was created” TRIPLE BLOODY HELL. So, you delete all the files you can find that reference the old model. But that’s not actually the problem, it’s the test database!

So what can you do? What makes it even worse is that Visual Studio does not expose this test database in any way, it’s like the localdb instance is a dirty little secret it does not want to give away. You have to open the command prompt (or powershell in my instance as the SQLLocalDB.exe was not in my cmd path), and run the following commands:

// list databases (in my case, it was using "v11.0")
SQLLocalDB info
// stop database
SQLLocalDB stop v11.0
// delete database
SQLLocalDB delete v11.0

…and then run your test. Hopefully, Success!


Even though this was horribly frustrating I feel that the EF is still the way to go. I just wish that Microsoft had spent that little bit of extra time QA’ing the database-first approach and the issues that arise in Visual Studio when testing using the local database. And it wouldn’t be a bad idea to have consistent underlying object (e.g. table) names, or at least expose them via the API somehow.

I don’t know where I’d be without the Internet, from which most of these problems were solved. It would be a long, difficult road otherwise. The Microsoft documentation leaves little to be desired from all fronts when it came to resolving these issues.