Entity Framework CodeFirst Important Tip For SQL Execution Efficiency

Updated: at 11:18 PM

 

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 = <span class="str">&quot;Image Without Data Means Nothing&quot;</span>)]
    [Column(TypeName = <span class="str">&quot;image&quot;</span>)]
    <span class="kwrd">public</span> <span class="kwrd">byte</span>[] ImageDataBytes { get; set; }
}</pre>


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.