Cool (and Free) Tools for Entity Framework

Julie Lerman

Now that Entity Framework is open source, the development community is able to contribute code to it at entityframework.codeplex.com. But don’t limit yourself to looking there for tools and extensions. There are other great tools, both commercial and community-driven, that can help you achieve more with Entity Framework. In this column, I want to highlight some of those tools and extensions that come from the community. They’re all free and available through the Visual Studio Gallery (for IDE extensions) or NuGet (for code libraries). There are even some from members of the Entity Framework team that are pet projects and external to the official bits.

EntityFramework Reverse POCO Code First Generator

There are still a lot of folks who think the Database First workflow is the only way to create a model for Entity Framework from an existing database. That particular workflow creates an EDMX file, which allows you to interact with your model in a designer. Using the designer also means you’re dependent on a code generator to create your entity classes. But it’s possible to use EF Code First with a pre-existing database, thanks to reverse-engineering tools. There are now three ways to accomplish this. My favorite is this template: EntityFramework Reverse POCO Code First Generator by Simon Hughes (efreversepoco.codeplex.com). To be fair, the other two are Microsoft-created tools I’ve also used extensively. One is a feature of the EF Power Tools Beta, which you can see in action in my May 2011 blog post at bit.ly/1ky2SAJ. The other is an enhancement in the Visual Studio EF Designer that was in the release of Entity Framework 6.1 Tools for Visual Studio. The EF Designer now includes a visual way to create POCOs from an existing database, and solves a problem that agonized me for a long time by providing the ability to select which tables to include in a model. I demonstrated this feature in my February 2014 blog post at bit.ly/1rOqUv4.

Hughes’ template was first released onto CodePlex in October 2012, and it had the ability to select subsets of a database from the start. In fact, the tool seems to provide all of the features I’ve hoped would be added or improved in the EF Power Tools and in the newer EF6.1 Designer. And I’m not the only one to feel this way: As I’m writing this column, there have been nearly 77,000 downloads of the EntityFramework Reverse POCO Code First Generator.

You can install the template through the Visual Studio Extensions and Updates feature and have offline access to it for future projects, or you can use it directly from the Web whenever you want to generate POCOs from a database. Before adding the template, you should add Entity Framework to your project and explicitly specify the database connection string in your config file, because the template relies on these. Once you have EF and your connection string, you initiate the template by adding a new item to a project in Visual Studio. If you’ve installed the template, you’ll find it under Installed; otherwise, open the Online section. Notice I haven’t filtered the templates in Figure 1, but the EntityFramework Reverse POCO Code First Generator is at the top of the list sorted by Most Popular items. I was certainly impressed by that position.

Figure 1 Getting the EntityFramework Reverse POCO Code First Generator

Selecting this item results in a new database.tt template file in your project. The template has a slew of configurations with default settings already applied. You can see a slice of the template file in Figure 2, which shows some of the simpler configurations you can use to impact the generated code. The ElementsToGenerate setting lets you control what types of classes are generated (POCOs, context, fluent configurations, UnitOfWork). One benefit of this configuration is that you can generate different items for different projects. You can control namespaces and how types are named. You can generate some basic Windows Communication Foundation (WCF) classes, and more. The configurations also let you specify what database tables should be included/excluded by using Regexes to specify the patterns of table or view names. Specifying objects has been an important feature for me. However, now that the EF6.1 Designer supports visually selecting database objects for creating a Code First model, I find that specifying the object names in the .tt file a bit clunky in comparison.

Figure 2 A Section of the EntityFramework Reverse POCO Generator Configuration File

Another significant advantage the Reverse POCO Code First Generator had over the reverse-engineer feature of the EF Power Tools was performance. However, the new EF6.1 Designer speeds up the code generation dramatically, so it’s no longer a determining factor for me. Nevertheless, the Reverse POCO Code First Generator has so many nice features (such as including default values for columns as default values for properties) and ways to customize how the classes are generated (such as specifying what type of collection should be used for navigation properties) that I still like it. I just wish I could merge it with the EF Designer.

There’s so much in this template that, rather than listing all of its features here, I’ll point you to the documentation and 30-minute demonstration video (reversepoco.com) so you can get an excellent head start on using this template. You’ll also see that Hughes has been accepting contributions to the project on CodePlex. An important note to keep in mind is that the EntityFramework Reverse POCO Code First Generator currently works with SQL Server and SQL Server CE. The EF6.1 Designer works with these databases and any other database providers that have been updated to work with EF6.1. I’ve been told that the next major version (v3) of Hughes’ generator will be able to reverse engineer other databases, such as Oracle, MySQL and so on,  in the same way that EF6.1 Designer can.

Entity Framework Utilities

The next library I’m highlighting is maintained by Mikael Eliasson on GitHub (bit.ly/UeWHGW). You can get the utilities into your projects by searching for EFUtilities in NuGet.

This set of utilities focuses on performance and adds functionality for something long missed by Entity Framework developers: the ability to perform bulk operations—insert, update and delete—in the database. The EFBulkOperation class in these utilities works with DbContext, so you can’t use it with the ObjectContext. Although a number of developers have attacked the problem, I’ve known Mikael for a while so I naturally gravitated to his offering. There’s also discussion on the CodePlex site for Entity Framework about best approaches to getting bulk operations into the native API.

The utility allows you to write LINQ queries on the EFBulk­Operation class in order to execute the relevant operation. As a test, I created a model (using the EntityFramework Reverse POCO Code First Generator tool I just discussed) from the AdventureWorks2012 database. Then, in my code, I created a list of 1,000 instances of the BusinessEntity type:

var entityList = new List<BusinessEntity>();
for (int i = 0; i < 1000; i++)
{
  entityList.Add(new BusinessEntity{});
}

I inserted these using standard Entity Framework code and then called SaveChanges:

using (var context = new AWModel()) {
  context.BusinessEntities.AddRange(entityList);
  context.SaveChanges();
}

This caused 1,000 individual insert statements to be sent to my SQL Server database, and the complete operation took an average of about 6 seconds over a number of repeated executions. I have removed EF warm-up times from this timing.

Then I used the EFBatchOperation class to insert the same 1,000 BusinessEntity objects:

using (var context2 = new AWModel()) {
  EFBatchOperation.For(context2, 
    context2.BusinessEntities).InsertAll(entityList);
}

The average execution was about 12 milliseconds.

Keep in mind that in the standard EF code, I had to add all of the instances to the context so that the change tracker could determine what SQL needed to be sent to the server. The EFBatchOperation class, on the other hand, doesn’t rely on the EF change tracker to build the SQL. Instead, it creates a DataReader that’s a class in the EFUtilities, and streams that to the database using the .NET SqlBulkCopy class that was introduced way back in ADO.NET 2.0. I was happy to see this because it has always been the path I’ve recommended to solve this problem. So not only is this method not forcing the context to track 1,000 entities, it’s also sending a single command to the database along with the binary stream of the data to be inserted. SQL Profiler won’t display the stream, so all I’ll see in SQL Profiler as a result of the EFBatchoperation.InsertAll method is this single command:

insert bulk BusinessEntity
  ([rowguid] UniqueIdentifier,
  [ModifiedDate] DateTime)

A nice protection here is that if the database provider you’re using doesn’t have a bulk method, the utility will revert to the EF default way of handling the request. In this case, that would mean sending 1,000 insert commands to the database.

EFBatchOperation also has methods to do bulk updates and bulk deletes, and what’s nice about each of these is that you don’t have to load the entities into memory or the context in advance as you would with EF. Instead, here’s an example of how you’d delete a selection of rows from the database using EFBatchOperation:

EFBatchOperation.For(context, context.BusinessEntityContacts).Where(
  p => p.ContactTypeId == 11).Delete();

It sends the relevant SQL to the database:

DELETE FROM [Person].[BusinessEntityContact] WHERE 11 = [ContactTypeID]

The bulk operation commands already provide significant performance gains, and now Eliasson is working on a replacement for the DbContext.Include method to improve the performance of eager loading. Rather than flatten the data and waste resources on a lot of redundant data in the results, the goal of the method in EF Utilities is to “load every Included collection as a separate query instead and then manually fix the relations. The goal is also to provide child collection filtering, sorting and, if possible, paging.” You can read more about this feature and keep an eye on its progress at bit.ly/1hBsGf5. Don’t overlook the readme.md file for this project.

Entity Framework 6 Contrib

Entity Framework 6 Contrib is a project on CodePlex by Microsoft MVP Unai Zorrilla (ef6contrib.codeplex.com). Zorrilla has made a number of contributions to the Microsoft EF package, such as Custom Pluralization, AddRange and RemoveRange, and AddFrom­Assembly—all features I’ve written about in past columns or articles. Zorrilla has a handful of additional helpers he maintains on theef6contrib.codeplex.com site, including:

• A preconfigured Spanish pluralization service (which has been incorporated into the EntityFramework Reverse POCO Code First Generator project I discussed earlier).
• Two more-convenient methods for adding Fluent API configurations to a Code First model at run time.
• A number of operations you can use with Code First migrations that aren’t included in EF, such as DatabaseCollation, GrantTablePermission and RevokeTablePermission.
• An initializer that works with migrations called DropAndMigrateDatabaseToLatestVersion.
• A set of query analyzer types that leverage EF6 interceptors and dependency resolvers.

What I’ve always loved about Zorrilla’s additions to EF is that he takes code he’s written and used repeatedly in his own work and encapsulates it to share with others. The analyzers, in particular, are extra cool. You can set up the parameters by which you want to measure performance of the EF database execution and monitor how well your code aligns with your expectations. There’s also an analyzer that checks for non-parameterized queries. If you add the interceptor to the DbConfiguration class (another EF6 feature), it will analyze all of the commands from a given DbContext. The CodePlex project has a bunch of tests and samples that let you explore the analyzers, as well as the other tools included in Entity Framework 6 Contrib. Figure 3 shows a DbConfiguration class from the samples that adds the PerformanceInterceptor and then specifies it should check three of the four analyzers in the collection.

Figure 3 Configuring Some Analyzers from Entity Framework 6 Contrib

public class Configuration : DbConfiguration  {
    public Configuration() {
      this.AddDependencyResolver(new CustomResolver());
      this.AddInterceptor(new PerformanceInterceptor((msg =>
      {
        Console.WriteLine(msg.Message);
      })));
    }
    private class CustomResolver : IDbDependencyResolver{
      public object GetService(Type type, object key) {
        return null;
      }
      public IEnumerable<object> GetServices(Type type, object key) {
        // You can use here your preferred IoC container
        if (typeof(IPerformanceAnalyzer).IsAssignableFrom(type)) {
          return new List<IPerformanceAnalyzer>(){
             new ExecutionTimePerformanceAnalyzer(TimeSpan.FromSeconds(3)),
             new UnparametrizedWhereClausesPerformanceAnalyzer(),
             new TopSlowQueriesPerformanceAnalyzer(10)
          };
        }
        return Enumerable.Empty<object>();
      }
    }
  }

Based on the configuration, the console window will display any reports from the analyzers.

EF Extensions and Tools Galore

These are just a few of the tools I’ve found to be really beneficial to my work. There are others I want to make sure you don’t overlook, but I don’t have room to dig into all of them here. Erik Ejlskov Jensen has a list of some of the more notable extensions in his blog post: “Entity Framework 6 (& SQL Server Compact) (5)–Entity Framework 6 Extensions” (bit.ly/SxxPc5). The list includes an updated version of the EF Caching Provider, which enables second-level caching for EF6 (bit.ly/UePlmU); Trackable Entities, a replacement for the discontinued Self-Tracking Entities (trackable.codeplex.com); and some additional projects that resolve bulk operations functionality. Ejlskov Jensen is a SQL Server CE MVP and in addition to contributing lots of great SQL CE-related functionality directly to EF6, he has an extremely popular Visual Studio extension, SQL Server Compact Toolbox, that includes many features to help with EF. If you use SQL CE with or without EF, the toolbox is an essential extension. Finally, I want to add a shout-out about work that Jimmy Bogard has done in a series of blog posts he calls “Missing EF Feature Workarounds,” including one that extends EF6 to provide global filters to an EF Model and is available as a NuGet package. You can learn more about this in his blog post at bit.ly/1prZv0a.

---

Julie Lerman is a Microsoft MVP, .NET Framework mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other .NET topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework” (2010) as well as a Code First edition (2011) and a DbContext edition (2012), all from O’Reilly Media. Follow her on Twitter attwitter.com/julielerman and see her Pluralsight courses at juliel.me/PS-Videos.

Thanks to the following technical experts for reviewing this article: Mikael Eliasson, Simon Hughes and Unai Zorrilla