Entity Framework CodeFirst Important Tip For SQL Execution Efficiency

 

Introduction

 

I’ve now been using Microsoft’s Entity Framework CodeFirst for a month or two and am very impressed with how easily it is to access data in a type safe way.  I also have noticed that I need to be extra careful about how I form my LINQ or I can accidentally cause the Sql Server to do tons of extra work (tons meaning lots, not actually tons).

I’ll basically explain this with actual code snippets to demonstrate my point and help you understand what I’m talking about. Let’s say you have a set of classes representing your model that basically have a master record and an associated detail record that contains an reference to a table that hold images.  That is, basically, something like this:

 

 public class User
    {
        [Key] 
        public long Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public virtual ICollection<AddressBookEntry> 
                        AddressBookEntries { get; set; } 
     }

 public class    AddressBookEntry
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public ImageDetail ImageDetail { get; set; }
    }

public class ImageDetail
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }

        [Required(ErrorMessage = "Image Without Data Means Nothing")]
        [Column(TypeName = "image")]
        public byte[] ImageDataBytes { get; set; }
    }

The Goal For Your Query

The goal for your EF query is you want to create a simple image viewer web page.  That is, you want to get the following information from your query that will list the address book of a given person.  The columns you want back for each address book entry (of a given user) are as follows:

    • AddressBookEntry.Id
    • ImageDetail.Id

You don’t actually want the blob data itself because you will let the html img tag actually get the data for you.

 

The Wrong Way

You are probably tempted to write a query that looks something like this:

var userAccount =
       db.Users.Where(a => a.Id == userIdIWant).
               Include(p => p.addressBookEntries.Select(o => o.ImageDetail).
                            FirstOrDefault();

The problem with this is if you have 4MB image in your ImageDetail table, it will all come down when you materialize your query.

 

 

The Right Way

 

The correct way is to create a resulting anonymous object something like this:

var userAccount =
      db.Users.Where(a => a.Id == userIdIWantd).
              Include(p => AddressBookEntries.Select(o => o.ImageDetails).Select(p=>new {
                 p.Id,
                 p.ImageDetails.Id
              }));

 

Conclusions

 

Sorry if the syntax here is not perfect.  I did not actually use these tables, my problem is a little different, but I just wanted to get my point across about how important making anonymous (or staticly typed) result sets are for performance and how easily you can make code that will kill the performance of your server.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Trackbacks

  1. […] Entity Framework CodeFirst Important Tip For SQL Execution Efficiency – Peter Kellner […]

Follow

Get every new post delivered to your Inbox

Join other followers: