Value cannot be null. Parameter name: constructor

Using Entity Framework I occasionally get the following error when reading from the database –
exceptionMessage=Value cannot be null.
Parameter name: constructor

My Person class looked like this –

    public class Person
    {
        public Person(string firstname, string lastname)
        {
             Firstname = firstname;
             Lastname = lastname;
        }

        public Guid PersonId { get; set; } 
        public string Firstname { get; set; } 
        public string Lastname { get; set; } 
        public DateTime SomeDate { get; set; }
     }

There is no way for Entity Framework to know how to instantiate this object because there is no empty constructor.

If you are following some of the principles of domain driven design you may not want a public empty constructor, no problem, make it private.

    public class Person
    {
        private Person(){} // Entity Framework queries will work now

        public Person(string firstname, string lastname)
        {
             Firstname = firstname;
             Lastname = lastname;
        }

        public Guid PersonId { get; set; } 
        public string Firstname { get; set; } 
        public string Lastname { get; set; } 
        public DateTime SomeDate { get; set; }
     }

Entity Framework Core and calling a stored procedure

I thought this would be easy, there is a method called ExecuteSqlCommand which takes the stored proc name and an array of parameters. I wanted to pass in two parameters and get one back, no problem, I’ve done this with ADO.Net and Entity Framework 6.

But holy s**t, two hours later and it was nowhere near working and I was very annoyed. ExecuteSqlCommand takes SqlParameters and the constructor for the output parameter is way more confusing than it needs to be. The input SqlParameter takes a name, a type and a value but the output SqlParameter needs eight more constructor params!!

Fortunately, EF Core still supports old style commands –

            DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

DbCommand still has the ExecuteNonQueryAsync method. Save yourself a major headache and use the cmd.ExecuteNonQueryAsync().

Here is a simple example passing in two parameters and getting one output back.

private async Task ExecuteStoredProc()
{
	DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

	cmd.CommandText = "dbo.sp_DoSomething";
	cmd.CommandType = CommandType.StoredProcedure;

	cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
	cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

	cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

	if (cmd.Connection.State != ConnectionState.Open)
	{
		cmd.Connection.Open();
	}

	await cmd.ExecuteNonQueryAsync();

	long id = (long)cmd.Parameters["@Id"].Value;
}

Entity Framework, checking the connection string of your context

Sometimes when using Entity Framework I want to verify that I’m connected to the database I think I’m connected to.

Here’s how to check in Entity Framework 5, Entity Framework 6 and Entity Framework Core 1 (EF 7)

//Entity Framework 5
myContext.Database.Connection.ConnectionString
//Entity Framework 6
myContext.Database.Connection.ConnectionString
//Entity Framework Core 1
myContext.Database.GetDbConnection().ConnectionString

Entity Framework with Proper Foreign Key Names

Full source code here.

One of the very nice features of Entity Framework is the navigational properties. But it is easy to set them up in a less that optimal way. If you have ever ended up with a foreign key column looking something like – TableName_TableNameId, for example Account_AccountId instead of just AccountId it’s probably because you missed a property on the related entity.

Here is the Account class with a collection of Phones and Addresses.

    public class Account
    {
        public Account()
        {
            Addressess = new List<Address>();
            Phones = new List<Phone>();
        }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid AccountId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        public virtual ICollection<Phone> Phones { get; set; }
        public virtual ICollection<Address> Addressess { get; set; }
    }

The Phone has a navigation property to Account, but it does not have a property for the for the AccountId, this results in a foreign key named Account_AccountId in the table. This works just fine, but doesn’t look quite right.

    public class Phone
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid PhoneId { get; set; }
        public string Number { get; set; }

        // No AccountId defined
        // Entity Framework will add a foreign key to Phones table called Account_AccountId

        // Navigation property
        public virtual Account Account { get; set; }
    }

The better way is to add a property for the AccountId to the linked entity, resulting in a foreign key named AccountId.

    public class Address
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid AddressId { get; set; }
        public string Line1 { get; set; }
        public string Line2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string ZipCode { get; set; }
       
        // This will be the name of the foreign key in the table
        public Guid AccountId { get; set; }
        
        // Navigation property
        public virtual Account Account { get; set; }
    }

Here are the created tables, as you can see the Phones table will has the poorly named foreign key and the Address table has the properly named foreign key.

Good And Bad Foreign Keys

Full source code here.

Entity Framework Migrations with an existing database

Introduction

If you read my post on Entity Framework migrations, you might be saying, “that’s amazing, but that’s all very well for new projects, but we’ve got eighty tables in place already and that code first migration ship has sailed”.
But no, you can still make use of Entity Framework migrations, it takes a some extra work, but it is well worth the effort.

If you already have POCOs that represent your databases tables and they really match your tables, you might be able to skip this step.

Reversing the Database

If your POCOs don’t match the database you can use the database tables to generate POCOs for you.

There are plenty of tutorials on Entity Framework’s reverse engineering tool. So I won’t go into too much detail.

Create a new solution with a console application as the startup project.
Set the connection string in your app.config to point to the your reference database (this is the one that has the all the tables you are currently using).

Grab the Entity Framework Power Tools Beta 4 (https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d/). Use this to reverse engineer the tables to POCOs. Review the outputted classes, mappings and context to make sure that they were generated the way you expect.

Notice that there is nothing on the POCOs to suggest where there should be indexes. Inferred primary key and foreign keys will be added in by the migration by default. But others will not.

First Migration

Go to the package manager and Enable-Migrations.
Enable-Migrations

Then add the first migration, Add-Migration InitalCreate, this is the migration that can be used to generate the tables.
Add-Migration

But, it is probably not complete, it’s going to be missing indexes as mentioned above, stored procs and more.

For example the Vehicle table looks like this
Table Layout

And has an indexes on VehicleID, TyreTypeIDa and Name, but the migration did not pick up the Name index.

Testing the migration

Change the connection string to point to new database.
And run Update-Database
Update-Database

You now have a new database based on the migration.
Open your SQL Server Management Studio, right click on the reference database and click Tasks, then Generate Scripts…
Generate-Script A

Click next and the choose the tables to script out.
6.Generate-Script B

Click next and then Advanced, scroll to the bottom of the list and turn on Script Indexes and any other feature you may need (eg, triggers).

7.Generate-Script C
Save the output to single file named ReferenceDB.sql

Follow the same steps to generate a script based on the new database.

Compare the files in a text comparison tool and you’ll see differences like this
Comparison

The index on the Name column is missing in the new database.

No problem. Go back to the migration and add it in.

Now you have two choices here, you can add a new migration to alter the existing table, or you can change the initial migration.
I suggest changing the InitalCreate migration, because you should try to get the this first migration to be as close as possible to the existing database.

Adding an index to a CreateTable call is as simple as adding one line at the end.

CreateTable( 
			"dbo.Vehicle", 
			c => new 
				{ 
					VehicleID = c.Int(nullable: false, identity: true), 
					Name = c.String(nullable: false, maxLength: 50), 
					Description = c.String(maxLength: 200), 
					MSRP = c.Double(nullable: false), 
					TyreTypeID = c.Int(nullable: false), 
				}) 
			.PrimaryKey(t => t.VehicleID) 
			.ForeignKey("dbo.TyreType", t => t.TyreTypeID, cascadeDelete: true) 
			.Index(t => t.TyreTypeID) 
                        
                         // this last line is the index I added by hand. 
			.Index(t => t.Name, name: "IX_Name");

Drop the new database the run Update-Database.

The Vehicle will now be created with the new index.
You could add the index to the POCOs, but you might want to consider how this would affect any move away from Entity Framework. See Code First Data Annotations for more.

For a full list of what you can do with migrations see DbMigration Methods.

Remember that you can run arbitrary sql with the Sql method or even from the SqlFile method. This gives you full flexibility when using Entity Framework migrations.

Entity Framework non null foreign key migration

Overview

This post gives a quick overview of how to use Entity Framework migrations and a detailed example of how to handle the addition of a new non null foreign keyed column to an existing table with data in it. Not a trivial process as you will see.
Full source code is provided, there are three versions of the same solution, one for each of the phases described below.

Introduction

Having source control over your SQL database has been a challenge for a while, I've seen companies with the attitude - the database is the source control, and they were not joking! I've seen a few companies use a dedicated SQL directory within their source code for table creation, stored proc and seeding scripts and this worked ok, but it seemed to put up a barrier between development and dbeng.

You can also go down the route of a dedicated piece of software that is supposed to manage it all for you but they are expensive, and in my experience, buggy.

Entity Framework Migrations

Enter Entity Migrations to fill the gap! It allows you to use your source code as the source control for your database! If you haven’t already worked with EF code first, you should probably stop here and review that topic. I've been using EF migrations for a while, there are plenty of tutorials out there but none deal with the scenario I’m handling.

The Problem

I have a Vehicle class, and a TyreType class. This is my starting structure, after my first migration I will have tables like those shown here. Assume that the application has been used for a while and there will be data in both tables.

Initial DB layout

Now I want to add a TyreColor class which will be referenced by the TyreType class.

public class TyreType
    {
        public int TyreTypeID { get; set; }

        //snip

        public int TyreColorId { get; set; }
        public virtual TyreColor TyreColor { get; set; }
    }

    public class TyreColor
    {
        public TyreColor()
        {
            this.TyreTypes = new List();
        }
        public int TyreColorId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public virtual ICollection TyreTypes { get; set; }
    }

There is also some plumbing in the context, you can see that in the source code provided.

This is a pretty standard one to many relationship. In the database it would look like -

DB layout after addition

The tricky part is dealing with the existing data in TyreType. I want to add a non null column, but I can’t because there are already rows of data.

The Solution

The quick answer is - firstly add a nullable TyreColorId column, then set some default value in all existing rows, then set the column to non null for future inserts. If that is enough to help you, good, if not, see the detailed explanation below.

Phase 1 - standard EF Migration creating the first two tables.

Step 1

Create the app, add the models, the context etc.

Go to the package manager console and type -

Enable-Migrations

Enable Migrations

This creates Migrations/Configuration.cs file, I'll come back to this shortly.

Step 2

Add a migration for the models in your project.

Back in the package manager type -

Add-Migration InitialCreate

This adds a file named something like - 201504280200272_InitialCreate.cs to the Migrations directory. Inside this file are Up() and Down() methods. The Up() will create the tables, set the primary key, create the foreign key and create an index.

And Down() drops the foreign key, index and tables.

Step 3

Before I push these changes to the database I will add some seeding code in Configuration.cs

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreType.AddOrUpdate(tt => new { tt.Name },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber" },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber" },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber" },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber" }
                 );
        }

Back in the package manager call -

Update-Database

The db and tables are created and the seeder run.

Tyre Type Table

Phase 2 – Adding the new table and foreign key fields


After creating this initial schema I added a TyreColor to the TyreType.

I want to make TyreColorId a required filed on TyreType and this is where everything starts to get complicated.
I’ve already seeded the TyreType with four rows and now I want to add a new column that is non null and will act as a foreign key. This is a problem, the existing four rows will have empty TyreColorId columns and SQL server won’t allow this.

To overcome this I first add the new column as a nullable, set a default value in the existing rows, and finally set the column to non null.

Step 1

Add TyreColorId and TyreColor to TyreType

  public class TyreType
    {
	   //snip
        public int TyreColorId { get; set; }

        public virtual TyreColor TyreColor { get; set; }
    }

In the package manger run -

Add-Migration AddingTyreColor

This adds a new migration, but I’m going to alter the Up() and Down() methods –

        public override void Up()
        {
            CreateTable(
                "dbo.TyreColor",
                c => new
                    {
                        TyreColorId = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Description = c.String(),
                    })
                .PrimaryKey(t => t.TyreColorId);

            AddColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
           
            //Insert a default TyreColor
            Sql("Insert INTO dbo.TyreColor (Name, Description) VALUES ('Black', 'Very black')"); 
            
            //Set all null TyreType.TyreColorId to the default
            Sql("UPDATE dbo.TyreType SET TyreColorId = 1 WHERE TyreColorId IS NULL"); 

            AddForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor", "TyreColorId", cascadeDelete: true);
        }

        public override void Down()
        {
            DropForeignKey("dbo.TyreType", "TyreColorId", "dbo.TyreColor");
            DropColumn("dbo.TyreType", "TyreColorId");
            DropTable("dbo.TyreColor");
        }

See the inline notes for details.

I also updated the seed method

        protected override void Seed(DataAccess.AutomobileContext context)
        {
            context.TyreColor.AddOrUpdate(tc => tc.Name,
                 new TyreColor { Name = "Black", Description = "Black" },
                 new TyreColor { Name = "Gray", Description = "A little black" },
                 new TyreColor { Name = "White", Description = "Very white" },
                 new TyreColor { Name = "Black/White", Description = "A mix of black and white" }
                 );

            context.SaveChanges();
            
            // grab the one that was added
            TyreColor firstTyreColor = context.TyreColor.First();  
            
            context.TyreType.AddOrUpdate(tt => new { tt.Name, tt.TyreColorId },
                 new TyreType { Name = "Very Fast Tyre", Material = "Very Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Fast Tyre", Material = "Fast Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Very Slow Tyre", Material = "Very Slow Rubber", TyreColorId = firstTyreColor.TyreColorId },
                 new TyreType { Name = "Slow Tyre", Material = "Slow Rubber", TyreColorId = firstTyreColor.TyreColorId }
                 );
        }

In the package manager run,

Update-Database

The changes are applied and the seed method is called (I’ve changed the seed method a little, you can see that in the source code).

I now have very close to the table structure I want, I just need to set the TyreColorId column in TryeType to non nullable and add an index.

Phase 3 – Making the new column non null


In the package manger run -

Add-Migration AlterTyreColorToNonNull

This will create a new migration with empty Up() and Down() methods.
Add the following

        public override void Up()
        {
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: false));
            CreateIndex("dbo.TyreType", "TyreColorId"); 
        }
        
        public override void Down()
        {
            DropIndex("dbo.TyreType", new[] { "TyreColorId" });
            AlterColumn("dbo.TyreType", "TyreColorId", c => c.Int(nullable: true));
        }

Now I have the structure I want

Data In Tables

And the data I want

Final Table Structure

Full source code is provided.

Using JSON to store application preferences and configuration

Download full source code.

Storing configuration preferences in the database is not a hard task. It usually involves storing the name of the preference, the value and the type in the database. Then when retrieving the data you perform some sort of cast.

The casts tend to be a bit painful, but it works.

This is the call –

 
bool displayWidget = (bool) Convert.ChangeType(_preferenceManager.BadWayToCastToGetPreference("DisplayWidget"), typeof(bool));

And this is the called method –

 
        public object BadWayToCastToGetPreference<T>(string name)
        {
            var preference = Convert.ChangeType(_context.Preferences.SingleOrDefault(p => p.Name == name).Value, typeof(T));
            return preference;
        }

But this quickly falls apart when you want to store a list of something, this requires a custom way of generating a string that represents the list. A common way I’ve seen is to store the values as a pipe delimited list.

For example if you wanted to store three cities
Boston, New York and Seattle
they would become “Boston|New York|Seattle” when stored in the database.

Now you need a write code to turn that back into a list of strings.

The problem gets worse when you want to store some custom types, for example an emergency contact shaped like –

    public class EmergencyContact
    {
        public int Priority { get; set; }
        public string EmailAddress { get; set; }
    }

Or worse if you want to store a list of emergency contacts.

Rather than suffering all that pain, just use Json to serialize and deserialize the values. This makes life a lot easier.

I have a simple method to create the preference object that gets store in the database –

        private Preference CreatePreference(string name, string value, string type)
        {
            var preference = new Preference
            {
                Name = name,
                Value = value,
                Type = type,
                PreferenceID = Guid.NewGuid()
            };
            return preference;
        }

CreatePreference gets called like this for a simple string –

            string defaultEmail = "admin@example.com";
            Preference preference1 = CreatePreference("DefaultEmail", JsonConvert.SerializeObject(defaultEmail), defaultEmail.GetType().ToString());

And it’s the same when storing a more complex type.

            EmergencyContact[] secondaryEmergencyContacts = new[]
            {
                new EmergencyContact{ EmailAddress = "tom@example.com", Priority = 1},
                new EmergencyContact{ EmailAddress = "dick@example.com", Priority = 2},
                new EmergencyContact{ EmailAddress = "harry@example.com", Priority = 3},
            };
            Preference preference6 = CreatePreference("SecondaryEmergencyContacts",
                JsonConvert.SerializeObject(secondaryEmergencyContacts), secondaryEmergencyContacts.GetType().ToString());

To store these preferences in the database, its just simple Entity Framework.

            _context.Preferences.Add(preference1);
            _context.Preferences.Add(preference6);
            _context.SaveChanges();

And then to get the preferences back out of the database you call –

        public T GetPreference<T>(string name)
        {
            var preference =
                JsonConvert.DeserializeObject<T>(_context.Preferences.SingleOrDefault(p => p.Name == name).Value);
            return preference;
        }

Some might complain that it is slower than direct casts, and yes, it probably is. But if you haven’t measured it you shouldn’t optimize it. You can cache always cache preferences. And it is a lot neater than the alternative.

Download full source code.

Saving enums as strings with Entity Framework

Full source code here.

I hit a problem where I wanted to use Entity Framework to save the text value of an enum to the database rather than its numeric value. This is not possible with Entity Framework at the moment; there are a few hacky solutions out there. I add my own hacky solution here.

I have a Person class with an ID, name and gender; the gender is an enum with just male and female. I’d like to save “Male” or “Female” to the database instead of 1 or 2.

The enum is simple.

    public enum Gender
    {
        Male = 1,
        Female = 2
    }

The Person class makes use of Data Annotations to perform the correct mappings to the table. The Gender property is not mapped to the database while the GenderString is mapped as the column named Gender.

    public class Person 
    {
        public int PersonID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Column("Gender")]
        public string GenderString
        {
            get { return Gender.ToString(); }
            private set { Gender = value.ParseEnum<Gender>(); }
        }

        [NotMapped]
        public Gender Gender { get; set; }
    }

I use an extension method to parse the text value of gender back into the enum.

    public static class StringExtensions
    {
        public static T ParseEnum<T>(this string value)
        {
            return (T)Enum.Parse(typeof(T), value, true);
        }
    }

Rows in the table will now have the string value of the enum rather than that number.

PersonIDFirstNameLastNameGender
1JamesSmithMale
2JaneSmithFemale

And when a person is loaded from the database the Gender enum is correctly populated.

The only drawback is that there is now a public property called Gender and GenderString on the Person class, but I have made the set of GenderString private to prevent accidental updating.

Full source code here.

Entity Framework lazy loading vs explicit loading and the generated SQL

Download the source code here.
If you are using lazy loading with Entity Framework you should check that queries to the database are working as expected.

A lazy load call to something like customer.Orders.FirstOrDefault() will give you a single order, but if you look at the SQL executed you’ll see that all orders for the customer are returned from the database; it is your code that is taking first one from memory. Depending on your application, this may have significant performance implications especially as the amount of data grows.

However, using explicit loading I am able to return just the one order I need.

In this post I’ll show how to use lazy loading and explicit loading, and compare the generated SQL for each.

The CustomerController contains a lazy and explicit Index action method and the OrderController contains an lazy and explicit Details action method.

To view the SQL generated for each query I used glimpse; turn it on by going to http://localhost:63817/glimpse.axd and clicking the “On” button.

Download the source code here.

Lazy Loading

By default lazy loading is enabled in EF. To take advantage of it all you do is mark the related entity as virtual, EF will do the rest.

public class Customer
{
   public int CustomerID { get; set; }
   public string Firstname { get; set; }
   public string Lastname { get; set; }
   public virtual ICollection<Order> Orders { get; set; }
}

When you call customer.Orders EF overrides the Orders virtual method and makes the appropriate call to the database to load all orders for the that customer. The problem is that all orders are loaded even if you only want to the first order.

Open the attached solution and take a look a the Customer IndexLazy action method and its associated view, it shows just the first order for each customer.

The IndexLazy action method loads the customers from the database, but does not load the orders. It is in the view that the orders are lazily loaded when generating the action links to the order details page –

item.Orders.FirstOrDefault().OrderID

Examination of the executed SQL shows that all orders for that customer were returned from the database even though only the first was used.

Explicit Loading

A more efficient approach is shown in the IndexExplicit action method. It disables lazy loading by calling –

db.Configuration.LazyLoadingEnabled = false;

The action method then loads the customers from the database as above. Then the first order for each customer is loaded into the Orders property of the Customer.

   foreach (var customer in customers)
   {
      db.Entry(customer).Collection(c => c.Orders).Query().Take(1).Load();
   }

In this way only one order record for each customer is returned from the database.

The Data

The data model is very simple, an orders table, a customer table and an order items table.
Zero or more order items are associated with an order, and zero or more orders are associated with a customer.

In the provided data, there are two customers, each with four orders made up of varying numbers of order items; take a look at the global.asax.cs to see exactly – note that in this code the way I initialize and seed the database are NOT what you should do for a serious piece of code.

Scroll to the bottom of the page to see the actual data in the tables.

The Execution

Customer page
Firstly go to the customer index which is lazily loaded – http://localhost:63817/Customer/IndexLazy

You’ll see two rows on the page representing the two customers and one order from each.
Here is the sql that was executed using lazy loading.

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]

Two customers returned

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @EntityKeyValue1 */

All four orders for customer 1 are returned from the database even though only one is needed

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 2 /* @EntityKeyValue1 */

All four orders for customer 2 are returned from the database even though only one is needed

Now go to localhost:63817/Customer/IndexExplicit and see the improvements of explicit loading.

SELECT
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]

Two customers returned

SELECT TOP (1)
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @EntityKeyValue1 */

One order returned for customer 1, this is what we wanted, note the use of TOP (1) ensuring that just one order is retrieved. Compare that to the SQL called for the lazy load.

SELECT TOP (1)
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 2 /* @EntityKeyValue1 */

One order returned for customer 2, again here we are getting just the one record we wanted.

Summary of lazy loading vs explicit loading for index page

TaskLazy Loaded RowsExplicit Loaded Rows
Retrieve customers22
Retrieve Steve's orders41
Retrieve James' orders 41

When using EF it is not enough to be aware of the reasons for using iqueryable vs ienumerable, you should look at the usage scenario of the entities you are loading from the database, especially those that are lazily loaded.

Order details page

From the customer index view, navigate to the order details view for both of the customers, it shows the customer, his first order and the first order item on that first order. The order details view can be loaded from two action methods, one uses lazy loading and the other explicit.

Below are the SQL calls using lazy loading.

SELECT TOP (2)
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @p0 */

returns one customer record

SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 1 /* @EntityKeyValue1 */

returns four order records even though only one is needed

SELECT
[Extent1].[OrderItemID] AS [OrderItemID],
[Extent1].[ItemDescription] AS [ItemDescription],
[Extent1].[Price] AS [Price],
[Extent1].[OrderID] AS [OrderID]
FROM [dbo].[OrderItems] AS [Extent1]
WHERE [Extent1].[OrderID] = 1 /* @EntityKeyValue1 */

returns six order item records even though only one is needed

Here are the SQL calls explicit loading.

SELECT TOP (2)
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[Firstname] AS [Firstname],
[Extent1].[Lastname] AS [Lastname]
FROM [dbo].[Customers] AS [Extent1]
WHERE [Extent1].[CustomerID] = 2 /* @p0 */

returns just one customer

SELECT TOP (1)
[Extent1].[OrderID] AS [OrderID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[Comment] AS [Comment],
[Extent1].[CustomerID] AS [CustomerID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[CustomerID] = 2 /* @EntityKeyValue1 */

returns just one order

SELECT TOP (1)
[Extent1].[OrderItemID] AS [OrderItemID],
[Extent1].[ItemDescription] AS [ItemDescription],
[Extent1].[Price] AS [Price],
[Extent1].[OrderID] AS [OrderID]
FROM [dbo].[OrderItems] AS [Extent1]
WHERE [Extent1].[OrderID] = 5 /* @EntityKeyValue1 */

returns just one order item

Summary of lazy loading vs explicit loading for order details page

TaskLazy Loaded RowsExplicit Loaded Rows
Retrieve customers11
Retrieve orders41
Retrieve order items61




Full Data

Customers table

CustomerIDFirstnameLastname
1Lazy SteveSmith
2JamesJones

Orders table

OrderIDOrderDateCommentCustomerID
18/6/2014 12:00:00 AM Steve's first order1
28/7/2014 12:00:00 AMSteve's second order1
38/8/2014 12:00:00 AMSteve's third order1
48/9/2014 12:00:00 AMSteve's fourth order1
58/7/2014 12:00:00 AMJames' first order2
68/8/2014 12:00:00 AMJames' second order2
78/9/2014 12:00:00 AMJames' third order2
88/10/2014 12:00:00 AMJames' fourth order2

Order Item table

OrderItemIDItemDescriptionPriceOrderID
1Ball31
2Watch321
3Book121
4Glasses411
5Pen41
6Chair231
7Laptop4002
8Mouse132
9Bottle12
10Stapler72
11Wires2.52
12Hose413
13Tiles224
14Table495
15Spoons1.995
16Forks2.995
17Cups55
18Plates145
19Knives85
20Bike3406
21Reflectors96
22Tool106
23Tube repair4.996
24Lights116
25Helmet276
26Mouse147
27Keyboard297
28Mouse148
29Keyboard298

Download the source code here.

Adding ROWGUIDCOL to Entity Framework Code First using migrations

To add add a ROWGUIDCOL to a unique identifier in a table using code first you have to use code migrations.

Below is the snippet you need. I haven’t covered how to perform a migration because there are plenty of articles available.

public partial class AddRowGuidCol : DbMigration
{
   public override void Up()
   {
      Sql("ALTER TABLE dbo.Address ALTER COLUMN [AddressID] ADD ROWGUIDCOL");
   }

   public override Void Down()
   {
      Sql("ALTER TABLE dbo.Address ALTER COLUMN [AddressID] DROP ROWGUIDCOL");
   }
}