Entity Framework – lazy loading properties
By Mirek on (tags: Entity Framework, lazy loading, shared primary key associations, table splitting, categories: architecture, code)In the Entity Framework we can easily enable lazy loading of navigation properties by marking them as virtual. This however applies only to properties that reference other related entity type. The framework does not support lazy loading of scalar or primitive properties. But there is a trick to fill this lack quite easily.
Let’s assume we would like to lazy load some properties because they are expensive to load and are required less often that the rest of the entity details. A good example is a representation of documents in database.
1: public class Document
2: {
3: public int Id { get; set; }
4:
5: public string FileName { get; set; }
6:
7: public DateTime SaveDate { get; set; }
8:
9: public int Size { get; set; }
10:
11: public byte[] Data { get; set; }
12: }
For the sake of simplicity we only have three properties and a binary content of the file. We would always retrieve FileName, SaveDate and Size, so we can, for instance display all available documents on the list in our application. However the binary data, which can be quite big, should be loaded from database only on user request, which means the user basically wants to view the file.
And here comes the solution. Since Entity Framework does not support lazy loading of primitive properties, the only thing we can do is to split the Document class and extract those properties we want to be lazy loaded into a separate entity. Let’s call it DocData.
1: public class DocData
2: {
3: [Key]
4: public int DocumentId { get; set; }
5:
6: public byte[] Data { get; set; }
7: }
Then we need to update the Document class as follows
1: public class Document
2: {
3: public int Id { get; set; }
4:
5: public string FileName { get; set; }
6:
7: public DateTime SaveDate { get; set; }
8:
9: public int Size { get; set; }
10:
11: public virtual DocData Data { get; set; }
12: }
As you probably noticed, the DocData entity does not have its own primary key property, but the foreign (by default convention) DocumentId instead, which is additionally marked as primary key. It is all because we want to keep the DocData entity totally integrated with a Document entity. In other words we want to have both those entities in one-to-one relationship. Let’s see how we can map it in to the database. Here we have two ways to go: table splitting and shared primary key association.
Table splitting
Table splitting , also called horizontal splitting, basically allows us to map multiple entities into a one table. In other words from the database perspective we have only one entity but we can instruct Entity Framework to split each row from this table into two separate entities. In our case it would be Document and DocData. Though it tends to be not recommended approach and rather preferred for existing databases when no other solution applies, it is quite interesting and I am going to show it.
1: protected override void OnModelCreating(DbModelBuilder modelBuilder)
2: {
3: modelBuilder.Entity<Document>().HasRequired(e => e.Data).WithRequiredPrincipal();
4:
5: modelBuilder.Entity<Document>().ToTable("Documents");
6: modelBuilder.Entity<DocData>().ToTable("Documents");
7: }
Here we instruct the framework that both ends of of the association are required and both entities must be mapped to the same table. Note that, though the instance of DocData will be required on Document entity it still have a byte array Data property optional. Indeed, this is how the Documents table is generated:
The Data column which holds our document content can have null values.
Shared Primary Keys Associations
As name suggests, this approach is based on sharing the values of primary keys in related tables. Basically the value of the primary key of one table is used as the value of the primary key in every related table. From the entity view there is no difference between this and splitting table approach. The only difference is that we change the mapping by fluent api.
1: protected override void OnModelCreating(DbModelBuilder modelBuilder)
2: {
3: modelBuilder.Entity<Document>().HasRequired(e => e.Data).WithRequiredDependent();
4: }
But opposite to table splitting we get now two tables DocDatas and Documents.
Having that in place we have our document content lazy loaded on explicit request only.
Cheers
Travis Parks
7/30/2015 12:46 AM
Got to say this blog was a lifesaver. Late in a project, I had the same situation where EF was bringing down byte[] columns even though they weren't needed. Of course this ran fine on my local machine because the database was right there. But once this was on actual servers, the transmission was costing us big time. It took us a while to figure out what was going on. I thought we had a serious rework on our hands, but then I stumbled on your post and was able to get everything working smoothly without altering my DB schema at all.U Janki Rao
8/6/2015 1:22 PM
In this scenario you can use sql server views with required columns(except the binary one) and generate model from the view. and use view instead of actual table.Alex
11/19/2015 5:34 PM
Thanks to this article I was able to get my treeview nodes that contained file data to load quite a bit faster.chris
7/13/2016 11:17 PM
How do you add a new one with this model?Seb
7/28/2016 8:03 AM
Nice one. In your example you are setting the Data to be Principal and Document to be Dependant. I suggest to do the opposite. In order for the delete to work I had to change the OnModelCreating method Document Entity to WithRequiredPrincipal and add WillCascadeOnDelete to false .