PeterKellner.net

Being a relative newbie to Microsoft’s Entity Framework Code First, there are things that I don’t like, things that I don’t understand, and well, just things that really bug me.  The problem is I often find out that the things that I don’t like and bug me are often lack of knowledge and not lack of product (at least not easily discoverable in the product).  One of those things is that Code First makes up names for foreign keys that don’t happen to be the same name as I would use.  Since there are times when you will need to look at these names (and possibly code with them (for another post)), I would like those names to be my names.

Let’s say you have a simple relationship between two tables.  Let’s call it User and Address.  Let’s just say for simplicity a user has one address.  In CodeFirst, you would model it with tables like the following:

public class User
{
        [Key]
        public long Id { get; set; }
        public virtual Address Address { get; set; }
       // more details about user, name, etc.
}

public class Address
{
       [Key]
        public long Id { get; set; }
        public string City {get;set;}
        // more details about address, state,zip,etc.
}

Now, you will end up with an Address table that has a column named something like User_Id.   YUC!!!  Who puts underscores in column names anymore (well, certainly I don’t.

You may think all you need to do to solve this is put a foreign key column and attribute in the Address table as follows:

public class Address
{
     [Key]
     public long Id {get;set;}

     [ForeignKey]
     public long UserId {get;set;}

     public string City {get;set;}
}

But, when you do that, you will be greeted with an error that looks like the following:

System.Data.Entity.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role ‘EmailAccount_User_Source’ in relationship ‘EmailAccount_User’. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be ‘*’.

or

The ForeignKeyAttribute on property ‘UserId’ on type ‘EFCodeFirstLib.CodeFirstModels.EmailAccount’ is not valid. The navigation property ‘User’ was not found on the dependent type ‘EFCodeFirstLib.CodeFirstModels.EmailAccount’. The Name value should be a valid navigation property name.

Pretty clear what to do, huh?

Well, I suppose if you know EF EDMX and a whole bunch of other acronyms, you probably will figure it out, but if you don’t I’ll tell you.  You basically need to include the User property in your Address class.  It should then look like:

public class Address
{
  [Key]
  public long Id {get;set}

  public User User {get;set;}  // important!
  [ForeignKey]
  public long UserId {get;set;}

  public string City {get;set;}
}

And now, you will have a “properly” named foreign key column “UserId” with no underscores!

My 2Cents.

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.