Practical .NET

Splitting Tables To Improve Performance: A Designer-Based Solution

Peter returns to improve performance by splitting a single table into multiple entities, but this time, he implements his solution using the Entity Framework 6 designer.

• By Peter Vogel
• 09/22/2014

In an earlier column this month, I discussed how the Entity Framework (EF) default behavior can give you a performance problem if you have a table with binary large object (blob) columns or a table with hundreds of columns. The solution is to create two entity classes for that single table: one entity class holds the data you need all the time, and another entity class holds either the blob columns or the columns you don't need all the time. The trick is to join the two entities with an association that allows you to load the entity with the columns you want as soon as you touch the entity (eager loading), but to defer loading the columns you don't want until you need them (lazy loading).

In the earlier column, I walked through a code-first solution. This column walks through a database-first, designer-oriented, drag-and-drop solution. This designer-based solution will give you a couple of benefits that my code-first solution didn't: a collection that lets you access your lazy-loading entity directly, and a navigation property that leads from the lazy-loading entity back to the eager-loading entity. I could've had those features in my code-first solution, but it would've been more work -- this designer-based solution gives you both for free.

As with my previous solution, this one works with EF6, which you can add to your application using NuGet.

Splitting Tables with the EF Designer
For this case study, I've used the Customers table from the AdventureWorks database and added a blob column called Picture of type image to the table. Your first step in working with the EF designer is, of course, to generate your EF model from your database. By default, Entity Framework will generate a model with one entity class for every table in your database. In my case, that means a single Customer entity with a property for every column, including my new Picture column.

After generating your model from the database, your next step is to select the entity you want to split and copy it. Once you're done copying the entity, paste the entity back into the designer and give it a new name. By default, Entity Framework will just add a 1 to end of the entity's name: I ended up with entities called Customers and Customers1. You'll want to rename the new table (I called mine CustomerLazy). You don't need to rename the old table, but for this column, I did: I called my original entity CustomerEager.

Making sure you leave the key properties in both entities (Id in the Customer tables in my example), delete from each entity the properties you don't want. In my case, I deleted Picture from my CustomerEager entity class and everything but the Id (the key property) and Picture properties from CustomerLazy. If there are some columns you won't ever need, you can delete them from both entities.

The next step is to add the association between the eager entity and the lazy entity. To do that, first right-click in the designer on any blank area and select Add New | Association. This displays the Add Association dialog with entities from your EF model displayed on each side of the dialog. In the dialog, select the two entities you want to associate (CustomerEager and CustomerLazy, in my case) and set the multiplicity to 1 on both sides of the dialog. You don't have to change the name of the default properties that will manage the association, but, again, I did: I changed the name of the navigation property in CustomerEager to CustomerBLOBs. Click the OK button to generate your association and close the dialog.

Mapping the Entity and Defining the Association
Unfortunately, your new entity (CustomerLazy, in my case) isn't tied to any table in your database -- you'll need to fix that next. To map your lazy entity to your database table, right-click on any blank part of the designer and select Mapping Details from the popup menu. This will display the Mapping Details window below the Visual Studio editor window.

Still in the designer, select your lazy entity (CustomerLazy, in my case) so that you can map it to a table. In the Mapping Details window you'll find a row labeled <Add a Table or View>. Click on that row to display a dropdown list of tables in your database. Select the table your two entities will use from that list (Customer, in my case). You should find the properties that remain in your lazy table are automatically mapped to the appropriate columns in your table (if not, make the assignments yourself).

The association you added is also missing some information: You need to tie it to your eager entity. Back in the designer window, right-click on the association you created between your eager and lazy entities and select Properties. In the Property window, click in the Referential Constraints entry to display its builder button (the button with three dots). Click on the button to display the Referential Constraint dialog. In the Principal dropdown list at the top of the dialog, select your eager entity (CustomerEager, in my case) and click OK to save your changes.

You're now ready to retrieve data both eagerly and lazily.

Loading Eagerly and Lazily
With the changes in place, you can retrieve the blob in your lazy entity only when you need it. This code processes all of the customers, for example, and only occasionally retrieves the entity with the blob property:

Dim db As New AdventureWorksLTEntitiesRevised
For Each c As CustomerEager In db.CustomerEagers
  '...process CustomerEager properties...
  If c.LastName = "Vogel" Then
    '...process c.CustomerBLOBs.Photo
  End If
Next

If you know you need the data from both entities then you're better off to retrieve both entities eagerly by using the EF Include method when retrieving your eager entity. To do that, just pass the Include method the name of the navigation property that points to your lazy entity. This example retrieves both entities eagerly by passing the name of the CustomerBLOBs property to the Include method on my eager entity:

Dim cust = (From c In db.CustomerEagers.Include("CustomerBLOBs")
            Where c.LastName = "Vogel"
            Select c).First
...process both CustomerEager and CustomerLazy properties

You can now have eager loading of the data you want and lazy loading of data that might otherwise drag down your performance.