EntityFramework Reverse POCO Code First Generator

Powerful (free) Entity Framework Tools

Julie Lerman

The Entity Framework is open source, so the development community can share code on entityframework.codeplex.com. But don't confine yourself to finding tools and extensions. There are other good business and community-driven tools that can help you achieve greater results with the Entity Framework. In this column, I want to highlight some of the tools and extensions from the community. They are free IDE extensions available through the Visual Studio Library, or free code libraries that can be obtained through NuGet. There are even members of the Entity Framework team from outside the special projects and official positions.

EntityFramework Reverse POCO Code First Generator

There are still many people who believe that the database first workflow is the only way to create an Entity Framework model from an existing database. This particular workflow creates an EDMX file that enables you to interact with the model in the designer. Using the designer also means that you rely on code generators to create entity classes. But with reverse engineering tools, you can also use EF Code first with an existing database. There are now three ways to do this. My favorite is this template: the EntityFramework Reverse POCO Code First Generator (efreversepoco.codeplex.com) developed by Simon Hughes. To be fair, the other two are also my widely used tools created by Microsoft. One is the functionality of EF Power Tools Beta. For the use of this feature, please refer to my May 2011 blog post at Bit.ly/1ky2saj. The other is the enhanced functionality of the Visual Studio EF Designer, which is included in the version of the Entity Framework 6.1 Tools for Visual Studio. Now the EF designer contains a visual method for creating POCO from an existing database and can solve a problem that has plagued me for a long time by selecting the tables to be included in the model. I demonstrated this feature in a February 2014 blog post with the URL bit.ly/1roquv4.

Hughes's template was first published in CodePlex in October 2012, and it can select a subset of databases from the outset. In fact, this tool seems to provide all the features I want to add or improve in the EP Power Tools or the updated RF6.1 designer. Not only did I have this feeling: entityframework Reverse POCO Code First generator has been downloaded nearly 77,000 times while I was writing this column.

You can install this template through the extensions and Updates feature of Visual Studio and support offline access to the template for future projects, or you can use the template directly from the Web in any case where you want to generate POCO from the database. Before you add a template, you should add the Entity Framework to your project and explicitly specify the database connection string in the configuration file, because the template relies on these conditions. Once you have the EF and connection strings, you can start the template by adding new items to the project in Visual Studio. If you have already installed this template, you can find it under installed, otherwise, open the online section. Note that in Figure 1 , I don't have a filter template, but in the list sorted by the most popular items, the EntityFramework Reverse POCO Code First generator is ranked top. I was really impressed by that position.

Figure 1 Getting entityframework Reverse POCO Code First Generator

Selecting this will generate a new database.tt template file in your project. This template has a large number of configurations that have the default settings applied. In Figure 2 , you can see a section of the template file that shows some simpler configurations that can be used to influence the generated code. The Elementstogenerate setting allows you to control the type of generated classes (POCO, context, Fluent configuration, unitofwork). One benefit of this configuration is that you can generate different items for different projects. You can control how namespaces and types are named. You can generate some basic Windows communication Foundation (WCF) classes, and so on. This configuration also allows you to specify which database tables should be included/excluded by specifying the table schema or view name by using Regex. For me, specifying an object is an important feature. However, since the EF6.1 designer supports the intuitive selection of database objects to create the Code first model, I find that in contrast, specifying object names in a. tt file is slightly clumsy.

Figure 2 Part of the EntityFramework Reverse POCO generator configuration file

Another notable benefit of the Reverse POCO Code first generator compared to the reverse engineering capabilities of EF Power Tools is performance. However, the new EF6.1 designer has significantly accelerated code generation, so this is no longer a determining factor for me. Nonetheless, the Reverse POCO Code First Generator has a lot of good features (for example, including the default values for columns as defaults) and methods for customizing how the class is generated (for example, specifying the type of collection used for navigation properties), and I still like it. I just want to be able to merge it with the EF designer.

There are too many features in this template, and I'm not going to list its features here, but to show you the document and the 30-minute demo video (reversepoco.com) so you can have an optimal early-bird experience with this template. You can also see that Hughes has accepted the content posted to the project on CodePlex. Keep in mind this important matter: the EntityFramework Reverse POCO Code First generator is currently available for SQL Server and SQL Server CE. The EF6.1 designer applies to these databases and any other updates to the database providers that are suitable for use with EF6.1. I heard that the next-generation major version of the Hughes Generator (v3) would reverse-engineer other databases such as Oracle, MySQL, and so on, like the EF6.1 designer.

Entity Framework Utility

The next library I'll focus on is the library (BIT.LY/UEWHGW) maintained by Mikael Eliasson on GitHub. You can search for efutilities on NuGet to get utilities in your project.

This set of utilities focuses on performance and adds functionality that has long been missed by Entity Framework developers: the ability to perform bulk INSERT, update, and delete operations in the database. The Efbulkoperation class in these utilities is for DbContext, so you cannot use it with ObjectContext. Although a lot of developers have overcome this problem, but I have known Mikael for some time, so it is natural to think of his products. There is also a discussion on the CodePlex website on the best way to get bulk operations from the Entity Framework in the local API.

This utility allows you to write LINQ queries on the Efbulkoperation class to perform related operations. As a test, I created a model from the ADVENTUREWORKS2012 database (using the EntityFramework Reverse POCO Code First Generator tool I just discussed). I then created a list of 1,000 instances of the businessentity type in the code:

var entitylist = new list<businessentity> (); for (int i = 0; i <; i++) {Entitylist.add (new businessentity{}) ;}

I use standard Entity Framework code to insert these instances, and then call SaveChanges:

using (var context = new Awmodel ()) {context.  Businessentities.addrange (entitylist); Context. SaveChanges ();}

This raises 1,000 individual INSERT statements to my SQL Server database, and the entire operation takes about 6 seconds on average, with some repetition. I've deducted EF's warm-up time from this timing.

Then I used the Efbatchoperation class to insert 1,000 identical businessentity objects:

using (var context2 = new Awmodel ()) {efbatchoperation.for (context2,     context2. businessentities). Insertall (entitylist);}

The average execution is approximately 12 milliseconds.

Keep in mind that in standard EF code, I have to add all instances to the context so that the change tracker determines which SQL to send to the server. On the other hand, the Efbatchoperation class does not rely on the EF change tracker to create SQL. Instead, it creates a DataReader class in efutilities and transmits it to the database using the. NET SqlBulkCopy class that was introduced earlier in ADO 2.0. I'm glad to see this because this is the path I usually recommend to solve this problem. Therefore, this approach not only forces the context to track 1,000 entities, it also sends a single command to the database and the binary stream to insert the data into. SQL Profiler does not display this stream, so what I see in SQL Profiler as the result of the Efbatchoperation.insertall method is this single command:

Insert Bulk businessentity ([rowguid] uniqueidentifier, [ModifiedDate] DateTime)

Here's a good protection, if you're using a database provider that doesn't have a bulk method, the utility reverts to the EF default method that handles the request. In this case, this means that 1,000 insert commands will be sent to the database.

Efbatchoperation also has a method for bulk update and bulk deletion, and for each of them the good news is that you don't have to load entities into memory or context as you would in EF. Instead, here's an example of how to use Efbatchoperation to delete a selection row from a database:

Efbatchoperation.for (context, context. businessentitycontacts). Where (p = = P.contacttypeid = 11). Delete ();

It sends the relevant SQL to the database:

DELETE from [person]. [Businessentitycontact] WHERE one = [Contacttypeid]

The bulk Operations command has provided significant performance gains, and now Eliasson is working on alternatives to the dbcontext.include approach to improve preload performance. The goal of the methods in the EF utility is to "move each of the included" collections into separate queries, and then manually fix those relationships. Sub-collection filtering, sorting, and paging, if possible, are also provided. Instead of tiling data or wasting resources on a large amount of redundant data in the results. You can learn more about this feature and pay close attention to its development on BIT.LY/1HBSGF5. Please do not neglect the Readme.md file for this project.

Entity Framework 6 Contrib

Entity Framework 6 Contrib is a project (ef6contrib.codeplex.com) carried out by the Microsoft MVP Unai Zorrilla on CodePlex. Zorrilla has published a number of things to the Microsoft EF package, such as "Custom Complex," AddRange and RemoveRange, and addfromassembly, and I've written all of these features in a previous column or article. Zorrilla has a handful of additional helpers maintained on the Ef6contrib.codeplex.com website, including:

• preconfigured Spanish plural services (this has been incorporated into the EntityFramework Reverse POCO Code First Builder Project I discussed earlier).
• Two more convenient ways to add the Fluent API configuration to the Code first model at run time.
• Some operations that are not included in EF that can be used with the Code first migration, such as Databasecollation, Granttablepermission, and Revoketablepermission.
• An initialization expression that is suitable for migration, called Dropandmigratedatabasetolatestversion.
• A set of query parser types that leverage EF6 listeners and dependency resolvers.

I've always loved Zorrilla. What's new for EF is that he works with code that he writes, uses it repeatedly, and encapsulates it with others. Especially the parser, very cool. You can set parameters, with parameters you can measure the performance of the EF Database execution and monitor how much code matches your expectations. There is also a parser that can check for non-parameterized queries. If you add a listener to the Dbconfiguration class (another EF6 feature), it parses all commands from a given DbContext. The CodePlex project has a number of tests and examples that let you study the analyzer, as well as other tools included in the Entity Framework 6 Contrib. Figure 3 shows a dbconfiguration class in the example of adding performanceinterceptor, and then specifies that it examines three of the four parsers in the collection.

Figure 3 Configuration Analyzer 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) {       //can use Here your preferred IoC container        if (typeof (Iperformanceanalyzer). IsAssignableFrom (type)) {&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&Nbsp;  return new list<iperformanceanalyzer> () {              new Executiontimeperformanceanalyzer (Timespan.fromseconds (3)),              new Unparametrizedwhereclausesperformanceanalyzer (),              new Topslowqueriesperformanceanalyzer (         };  )      }        return enumerable.empty<object> ( );     }   } }

Depending on this configuration, the console window will display all reports from the parser.

Rich EF extensions and tools

I find that only a small number of tools are really good for my job. I hope you haven't overlooked the other tools, but here I have no room to delve into all the tools. Erik Ejlskov Jensen has a more noteworthy list of extensions in his blog posts: "Entity Framework 6 (& SQL Server Compact) (5) –entity Framework 6 Extension S "(BIT.LY/SXXPC5). The list includes an updated version of the EF cache provider that enables level two caching (BIT.LY/UEPLMU) for EF6, a traceable entity that overrides the self-tracking entity (trackable.codeplex.com) that is stopped using, and other items that resolve the bulk operations feature. Ejlskov Jensen, the SQL Server CE MVP, has released a popular Visual Studio extended SQL Server Compact Toolbo, in addition to a number of powerful SQL CE related features directly to EF6. X, which includes many features to help with EF. Whether you're using SQL CE with EF or SQL CE without EF, this toolkit is an important extension. Finally, I would like to express my sincere thanks to Jimmy Bogard for his work in a series of blog posts called "Missing EF Feature Workarounds" (a workaround for missing EF features), including extending EF6 to provide global filters to the EF model Available as a NuGet package. You can find more details in his blog post at bit.ly/1przv0a.

Julie Lerman is the Microsoft MVP,. NET Framework Mentor and consultant, who lives in the mountains of Vermont State. You can see her demos of data access and other. NET topics in user groups and conferences around the world. She is the author of "Programming Entity Framework" (2010) and "Code First" (2011) and DbContext (2012) (all from O ' Reilly Media), and the blog URL is thedatafarm.com /blog. Follow her through her Twitter (URL: twitter.com/julielerman) and watch her pluralsight course on Juliel.me/ps-videos.

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

EntityFramework Reverse POCO Code First Generator