Category: Entity Framework

Code-first does not support stored procedures YET. See here.

The problem :-
Following is the code I use and I get an error given below:-

Contact:-
public class Contact
{
public int ContactID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public DateTime AddDate { get; set; }
public DateTime ModifiedDate { get; set; }
}


Context:-
public class AddressBook : DbContext
{
public DbSet<Contact> Contact { get; set; }
}

The main program:-
using (var context = new AddressBook())
  {
   var contact = new Contact
   {
   ContactID = 10000,
   FirstName = "Brian",
   LastName = "Lara",
   ModifiedDate = DateTime.Now,
   AddDate = DateTime.Now,
   Title = "Mr."
   };
   context.Contact.Add(contact);
   int result = context.SaveChanges();
   Console.WriteLine("Result :- " + result.ToString());
  }


And I get the following error on "context.Contact.Add(contact);":-

System.InvalidOperationException: The model backing the ‘AddressBook’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the RecreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data. at System.Data.Entity.Infrastructure.CreateDatabaseOnlyIfNotExists1.InitializeDatabase(TContext context) at System.Data.Entity.Infrastructure.Database.Initialize() at System.Data.Entity.Internal.InternalContext.Initialize() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.EfInternalQuery1.Initialize() at System.Data.Entity.DbSet1.ActOnSet(Action action,EntityState newState, TEntity entity) at System.Data.Entity.DbSet1.Add(TEntity entity) at CodeFirst.Client.Program.Main(String[] args) in E:\Ashish\Research\VS Solutions\EntityFramework\CodeFirstApproach_EF_CTP4\CodeFirst.Client\Program.cs:line 35

Solution :-

The error message says:-
“System.InvalidOperationException: The model backing the ‘AddressBook’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the RecreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data”

so, first look at the Database.SetInitializer() method which you can call in three different ways:-

// 1) This is the default strategy.  It creates the DB only if it doesn't exist
Database.SetInitializer(new CreateDatabaseOnlyIfNotExists<ProductContext>());

// 2) Recreates the DB if the model changes but doesn't insert seed data.
Database.SetInitializer(new RecreateDatabaseIfModelChanges<ProductContext>());

// 3) Strategy for always recreating the DB every time the app is run.
Database.SetInitializer(new AlwaysRecreateDatabase<ProductContext>());

None of the above cases is applicable to us If we have an already existing database with data. However, we still need to call Database.SetInitializer(null) to nullify the default strategy , the first one in the above list – “creates the DB only if it doesn’t exist”. There is a link pointed by Pault which has the following comment by Jeff of EF team:-

Friday, August 06, 2010 11:28 AM by Jeff
@Mark
For those who are seeing this exception:
"The model backing the ‘Production’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance."
Here is what is going on and what to do about it:

When a model is first created, we run a DatabaseInitializer to do things like create the database if it’s not there or add seed data. The default DatabaseInitializer tries to compare the database schema needed to use the model with a hash of the schema stored in an EdmMetadata table that is created with a database (when Code First is the one creating the database). Existing databases won’t have the EdmMetadata table and so won’t have the hash…and the implementation today will throw if that table is missing. We’ll work on changing this behavior before we ship the fial version since it is the default. Until then, existing databases do not generally need any database initializer so it can be turned off for your context type by calling:

Database.SetInitializer<Production>(null);

Jeff

So, I took a leaf out of the above and added that in my main program:-

Database.SetInitializer<AddressBook>(null);
using (var context = new AddressBook())
          {
                   var contact = new Contact
                   {
                       ContactID = 10000,
                       FirstName = "Brian",
                       LastName = "Lara",
                       ModifiedDate = DateTime.Now,
                       AddDate = DateTime.Now,
                       Title = "Mr."
                   };
                   context.Contacts.Add(contact);
                   int result = context.SaveChanges();
                   Console.WriteLine("Result :- " + result.ToString());

               }

After making the above code change, I ran the program and hit a wall again (small part of the error message shown below):-

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. —> System.Data.SqlClient.SqlException: Invalid object name ‘dbo.Contacts’.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
 

Code-first, by convention, assumes that your table in the database is plularized form of your POCO class. For example, If your POCO class is Contact, code-first assumes that the table name is “Contacts” and tries to find the table named “Contacts” to persist the Contact objects to “Contacts”  table. Thats why we see an error above as we dont have a table named “Contacts” in the database. We have table name as “Contact” instead.

So, while working with existing database, What If you want your POCO class point to a differnt table? For example, your POCO class is Contact however your table in the database is also Contact. The table name could be anything. To fix this, you need to map your Contact entity to the correct table name when the model is being created. In the context class, override the OnModelCreating() event handler in the Context class and map the object to the correct table name.
public class AddressBook : DbContext
    {
        public DbSet<Contact> Contacts { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Contact>().MapSingleType().ToTable("tblContact");
        }
    }

If we run the sample now, It would run without any error. I also discussed this question here.

I have this following model. Check the “ModifiedDate” which is common in both the entities.

And both tables in the database have ModifiedDate columns. Following is the stored procedure which gets all the contacts along with their addresses:-

CREATE PROCEDURE SelectAllContactsWithAddresses
AS
BEGIN
SELECT Contact.ContactId, Contact.FirstName, Contact.LastName, Contact.ModifiedDate,
Address.AddressId, Address.ContactId AS AddressContactId, Address.CountryRegion,
Address.StateProvince, Address.ModifiedDate
FROM Contact
INNER JOIN Address
ON Contact.ContactId = Address.ContactId
END

Right click on the model and add the new stored procedure to the model :-

Right click on the model and add the stored ptocedure as a “Function Import”.

In the “Add Function Import” dialog, enter the function name and select the stored procedure name :-

Make sure you click on “Create New Complex Type” to create a new complex type. The result set will be a complex type because It does not match up wo any of the entities in the model, rather it is made of columns from both Contact and Address tables.

Now the context has a method named SelectAllContactsWithAddresses() and when you execute a code like this,

foreach (var item in context.SelectAllContactsWithAddresses())
{
Console.WriteLine(item.FirstName.Trim() + ” ” + item.LastName.Trim());
Console.WriteLine(item.AddressContactId + ” ” + item.CountryRegion);
}

you get the following error :-

System.Data.EntityCommandExecutionException: The data reader is incompatible with the specified ‘AddressBookModel.SelectAllContactsWithAddresses_Result2’. A member of the type, ‘ModifiedDate1’, does not have a corresponding column in the data reader with the same name.
   at System.Data.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateFunctionImportStruc
turalTypeColumnMap(DbDataReader storeDataReader, FunctionImportMapping mapping,EntitySet entitySet, StructuralType baseStructuralType)
at System.Data.EntityClient.EntityCommandDefinition.FunctionColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateCol
umnMap(DbDataReader reader)   at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption)   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functio
nName, MergeOption mergeOption, ObjectParameter[] parameters)   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functio
nName, ObjectParameter[] parameters)   at AddressBook.AddressBookEntities.SelectAllContactsWithAddresses() in E:\Ashish\Research\VS Solutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBook\AddressBookModel.Designer.cs:line 148
at AddressBookConsole.Program.ShowContacts() in E:\Ashish\Research\VS Solutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBookConsole\Progra
m.cs:line 50   at AddressBookConsole.Program.Main(String[] args) in E:\Ashish\Research\VS So
lutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBookConsole\Program.cs:line 15

To put simply, the columns from the stored procedure must match the properties in the entities in the model. So we modify our stored procedure in the following way:-

ALTER PROCEDURE SelectAllContactsWithAddresses
AS
BEGIN
SELECT Contact.ContactId, Contact.FirstName, Contact.LastName,
Contact.ModifiedDate AS ContactModifiedDate,
Address.AddressId, Address.ContactId AS AddressContactId, Address.CountryRegion,
Address.StateProvince, Address.ModifiedDate AS AddressModifiedDate
    FROM Contact
INNER JOIN Address
ON Contact.ContactId = Address.ContactId
END

Now we need to “Refresh” the model with the changed stored procedure:-

We make sure the function import shows correct columns now. by clicking on the “Get column information” button inthe properties page.

We nee to change the properties in the model as well to make it same as the changed columns returned:-

Now, we write code like this :-

foreach (var contactWithAddresses in context.SelectAllContactsWithAddresses())
{
Console.WriteLine(contactWithAddresses .FirstName.Trim() + ” ” + contactWithAddresses .LastName.Trim());
Console.WriteLine(contactWithAddresses .ContactModifiedDate + ” ” + contactWithAddresses .AddressModifiedDate);
}

and we get the correct results without any problem:-

POCO stands for “Plain Old CLR Objects”. POCO classes are basically classes which we generally write with all the public properties (e.g. Customer, Order).  Now, EF4, by default generates all the entities for us (we will see this in this article) and we dont have to write those classes. Just becuase EF does this, in order to distinguish “OUR” classes or the “Classes written by us” from the generated classes, we call those “OUR” classes as POCO classes.

There are basically 2 ways you can make use of POCO support in EF4:-

A) Via ObjectContext
B) Via T4 Templates  (this will be covered here)

A) Via ObjectContext :-

This is done the following way:-

i) Generate the model using the wizard.
ii) Turn off the code-generation (because you want to write code yourself).
iii) Create your POCO classes, Address and Contact in this case and dont have them inherited from EntityObject.
iv) Create your own ObjectContext class.
v) Use your ObjectContext class to use POCO classes.

i) Generate the model using the wizard:-

Create a console application and follow the below steps:-

Step 1 :-

 Step 2 :-

Step 3 :-

When you click the “Finish” button above, you should see an EDMX file and a code-behind file added to your solution. Double-clicking the edmx file shows the following:-

Code-behind file contains the following :-

As you see the classes “Contact” and “Address” inherit from EntityObject. there would be times when you dont want your entities to be separated in their own class library and dont want them to get inherited from the EntityObject class. So you (very conventionally) create your own classes named Address and Contacts with the stardard public properties. When objects of those “YOUR” classes are called Plain Old CLR Objects or POCO.

ii) Turn off the code-generation (because you want to write code yourself):-

Select the EDMX file in the solution explorer and go to its property window (press F4). Delete the value for the “Custom Tool” in the properties window.

when you have deleted the custom tool value, notice that the code-behind for the EDMX file is gone and you are on your own to create entities which we will see in the next step.

iii) Create your POCO classes, Address and Contact in this case and dont have them inherited from EntityObject.
Create a class library and create the following classes :-

The Contact class
public class Contact
    {
        public int ContactID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Title { get; set; }
        public DateTime AddDate { get; set; }
        public DateTime ModifiedDate { get; set; }
        public ICollection<Address> Addresses { get; set; }

    }

The Address class
public class Address
    {
        public int addressID { get; set; }
        public string Street1 { get; set; }
        public string Street2 { get; set; }
        public string City { get; set; }
        public string StateProvince { get; set; }
        public string CountryRegion { get; set; }
        public string PostalCode { get; set; }
        public string AddressType { get; set; }
        public DateTime ModifiedDate { get; set; }

        public int ContactID { get; set; }
        public Contact Contact { get; set; }
    }

iv) Create your own ObjectContext class:-

public class POCOObjectContext : ObjectContext
{
       private ObjectSet<Contact> contacts;
       private ObjectSet<Address> addresses;
       public POCOObjectContext()
           : base("name=AddressBook2Entities", "AddressBook2Entities")
       {
           contacts = CreateObjectSet<Contact>();
           addresses = CreateObjectSet<Address>();
       }
       public ObjectSet<Address> Addresses
       {
           get { return addresses; }
       }
       public ObjectSet<Contact> Contacts
       {
           get { return contacts; }
       }

}

Notice the constructer getting used takes two parameters:-

For the first parameter (“name”) we are passing is the name of the connection string in config file. This connection string got added when we generated the model from the database.
For the second parameter, we are passing the name of the container. On the properties window of the edmx file, look for the value for “Entity Container Name”.

Using the application :-

Use the classes in the main method of the console application. Below is just printing all the contacts from the “Contact” table.

class Program
    {
        static void Main(string[] args)
        {
            using (POCOObjectContext context = new POCOObjectContext())
            {
                List<Contact> contacts = context.Contacts.ToList();
                foreach (var item in contacts)
                {
                    Console.WriteLine(item.FirstName.Trim() +" "+ item.LastName.Trim());
                }
                Console.ReadLine();
            }
        }
    }

The output:-

Summary:-

So, we saw that ObjectContext or your class (inheriting from the ObjectContext) is smart. Although our entity class is not inheriting from the EntityObject, ObjectContext  can look at them and let us work with the them just like we could have with the generated code. The advantage here (with POCOs) is our entities are clean and do not have any dependency on Entity Framework which enables better organized code and gives us flexibility for unit testing etc.

Lazy Loading

Lazy loading is not a new concept and I must confess I didn’t know what it is. Thanks to Entity Framework (EF) for having it because It led me to understand lazy loading concept before I know how it can be used in the EF. 🙂

Alright, let us take the following example:-

   1: // member variable

   2: private List<Customer> customers= null;

   3:  

   4: // property

   5: public List<Customer> Customers

   6: {

   7:   get

   8:   {

   9:     if (customers== null)

  10:      customers= GetCustomers();

  11:  

  12:     return customers;

  13:   }

  14: }

Now, anywhere in your program when you use CustomerDataSet, It sees If the customers are already loaded, If not load it (e.g from a data store).

But this is not a new concept, right?  Absolutely! However, the point is this concept is very important when you try to load related objects. Suppose Customers have Orders. You when you get all the customers, do you want to get all the orders placed by them? Probably not. You probably want to get the orders for customers when you need it (you are lazy here) and don’t want to get all the orders for all the customers when you just wanted customers.

Coming back to EF, lazy loading is enabled by default and it is controlled by the following property:-

   1: contextObject.ContextOption.EnableLazyLoading 

where contextObject is an instance of the type of ObjectContext. By default the lazy loading is enabled in EF.

So, how does it work in EF?. I generated the following model from an existing database.

 
So, as per the above model, a contact can have many addresses. Now, lets try to retrieve contacts having more than one address:-
 
Before I ran the above example, I opened the Sql Server profile and started a new trace and then ran the above. Following is the output:-

 
Following is what’s going on in the profiler:-
 
Disregard the “Trace Start” in the above and you should notice there are 12 rows. The very first query executed is the following which gets all the contacts in the system and DOES NOT get the addresses associated with all the contacts:-
The next 11 queries are the for getting the addresses for each of the 11 contacts the very first time we access the Addresses properties for each contact. In this case the query to get all the addresses will be fired when the item.Addresses.Count is called. 
Following is the Sql query which gets fired to get addresses for each contact. There would be 11 trips to the database with the following query.

Eager loading:-

What If we want to load all the addresses as well for all the contacts when we get the contacts. Surely It can be done. However, for this, we need to turn off the lazyLoading behavior in EF.  As I said before, It is on by default.

   1: context.ContextOptions.LazyLoadingEnabled = false;

 

The query results which show multiple entries for same contact id, because It is listing all the addresses for all the contacts:-

Explicit (lazy) Loading:-

You check for the  IsLoaded property of the collection (e.g. Addresses) and if It was not loaded for a given contact, you call the Load() method to load the Addresses for a given contact.

 
The output is same:-

You should see the same number of same queries (same number of database trips) as you saw in case of lazy loading.

So the explicit loading is nothing but the lazy loading, however, in explicit loading we explicitly load the child collections (relations) and in lazy loading, we dont have to “load” them, they get loaded when they are accessed.