Using EF “Code First” with an Existing Database

Last month I blogged about the new Entity Framework 4 “code first” development option.  EF “code-first” enables a pretty sweet code-centric development workflow for working with data.  It enables you to:

  • Work with data without ever having to open a designer or define an XML mapping file
  • Define model objects by simply writing “plain old classes” with no base classes required
  • Use a “convention over configuration” approach that enables database persistence without explicitly configuring anything

In my initial blog post I introduced EF “code-first” and demonstrated how to use the default EF4 mapping conventions to create a new database.  These default conventions work very well for new applications, and enable you to avoid having to explicitly configure anything in order to map classes to/from a database.  I then did a second custom database schema mapping blog post that discussed how you can override the default persistence mapping rules, and enable custom database schemas.

In today’s blog post I’m going to cover a question that several people asked me recently, which is: “how do I use EF code-first with an existing database?”

Using EF Code-First with an Existing Database

EF “Code First” works great with existing databases, and enables a very nice code-centric development approach with them. In particular, it enables you to use clean “plain old classes” (aka POCO) for your model objects, and cleanly map them to/from the database using either the default mapping conventions or by overriding them with custom schema mapping rules

Below are step by step instructions on how you can use EF “Code First” with an existing database.

Step 1: Create a new ASP.NET Web Application Project

Let’s begin by creating a new ASP.NET Web Application Project.  My previous two EF “code first” blog posts used ASP.NET MVC – for this blog post I’ll use ASP.NET Web Forms.  Note that all of the EF concepts are identical regardless of whichever type of ASP.NET application you use.

We’ll use “File->New Project” within VS 2010 (or the free Visual Web Developer 2010 Express) and choose the “ASP.NET Web application” project template to create the new application. 

The new “ASP.NET Web Application” project in VS 2010 is a nice starter template that provides a default master-page layout with CSS design (I blogged about this new starter project template in a previous blog post).  When it is created you’ll find it contains a few default files within it:

image

We don’t need these default files (we could instead just use the “Empty ASP.NET Web Application” project template) – but they’ll make our simple app look a little prettier by default so we’ll use them.

Step 2: Reference the EF Code First Assembly

Our next step will be to add a reference to the EF Code First library to our project.  Right click on the “references” node within the Solution Explorer and choose “Add Reference”. 

You’ll reference the “Microsoft.Data.Entity.Ctp.dll” assembly that is installed within the “\Program Files\Microsoft ADO.NET Entity Framework Feature CTP4\Binaries\” directory when you download and install the EF Code First library.  After adding this reference you’ll see it show up in your project’s references window like below:

image

Step 3: Northwind Database

You can skip this step if you have a SQL Server database with Northwind (or another database) installed. 

If you don’t have Northwind already installed then you can download it here.  You can either use the .SQL files it includes to install it into a SQL database, or copy the Northwind.mdf SQL Express file into the \App_Data directory of your application:

image

Step 4: Create our Model Layer

Now we’ll write our model classes and use EF “code first” to map them to our Northwind database.  Below is all of the code we need to write to enable this – no other code is required:

image

Below are some details about what all this code does and how it works:

POCO Model Classes

EF “code first” enables us to use “plain old CLR objects” (aka POCO) to represent entities within a database.  This means that we do not have to derive our model classes from a base class, nor implement any interfaces or attributes on them.  This enables us to keep our model classes clean and “persistence ignorant”.

Above we’ve defined two POCO classes - “Product” and “Category” – that we’ll use to represent the “Products” and “Categories” tables within our Northwind database.  The properties on these two classes map to columns within the tables.  Each instance of a Product or Category class represents a row within the respective database tables.

Nullable Columns

Notice that some of the properties within the “Product” class are defined as nullable (this is what Decimal? means – that indicates it is a nullable type).  Nullable columns within a database table should be represented within the model class as Nullable properties if they are value types:

image

You can also optionally omit specifying nullable columns entirely from a model class if you don’t need to access it.  For example, the Product table within Northwind has a “QuantityPerUnit” column that is a nullable nvarchar, and a “UnitsOnOrder” column that is a nullable smallint.  I’ve omitted both of these properties from the “Product” class I defined above.  Because they are nullable within the database I can still retrieve, insert, update and delete Products without problems.

Association Properties and Lazy Loading

EF “code-first” makes it easy to take advantage of primary-key/foreign-key relationships within the database, and expose properties on our model classes that enable us to traverse between model classes using them.

Above we exposed a “Category” property on our Product class, and a “Products” property on our Category class.  Accessing these properties enables us to use the PK/FK relationship between the two tables to retrieve back model instances.  Notice how the properties themselves are still “POCO” properties and do not require us to use any EF-specific collection type to define them.

Association properties that are marked as “virtual” will by default be lazy-loaded. What this means is that if you retrieve a Product entity, its Category information will not be retrieved from the database until you access its Category property (or unless you explicitly indicate that the Category data should be retrieved when you write your LINQ query to retrieve the Product object). 

EF Context Class

Once we’ve created our “Product” and “Category” POCO classes, we used EF “code first” to create a “context” class that we can use to map our POCO model classes to/from tables within the database:

image

The “Northwind” class above is the context class we are using to map our Product and Category classes to/from the database.  It derives from the DbContext base class provided by EF “code-first”, and exposes two properties that correspond to tables within our database.  For this sample we are using the default “convention over configuration” based mapping rules to define how the classes should map to/from the database. 

We could alternatively override the “OnModelCreating” method and specify custom mapping rules if we wanted the object model of our model classes to look differently than our database schema.  My previous blog EF “code first” post covers how to do this.

Step 5: Configuring our Database Connection String

We’ve written all of the code we need to write to define our model layer.  Our last step before we use it will be to setup a connection-string that connects it with our database.

In my initial EF “code first” blog post I discussed a cool option that EF “code first” provides that allows you to have it auto-create/recreate your database schema for you.  This is a option that can be particularly useful for green-field development scenarios – since it allows you to focus on your model layer early in the project without having to spend time on updating your database schema after each model change.

Importantly, though, the auto-create database option is just an option – it is definitely not required.  If you point your connection-string at an existing database then EF “code first” will not try and create one automatically. The auto-recreate option also won’t be enabled unless you explicitly want EF to do this – so you don’t need to worry about it dropping and recreating your database unless you’ve explicitly indicated you want it to do so.

For this blog post we will not auto-create the database.  Instead, we’ll point at the existing Northwind database we already have.  To do this we’ll add a “Northwind” connection-string to our web.config file like so:

  <connectionStrings>
     
    <add name="Northwind"
         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\northwind.mdf;User Instance=true"
         providerName="System.Data.SqlClient" />

  </connectionStrings>

EF “code first” uses a convention where context classes by default look for a connection-string that has the same name as the context class.  Because our context class is called “Northwind” it by default looks for a “Northwind” connection-string to use.  Above our Northwind connection-string is configured to use a local SQL Express database.  You can alternatively point it at a remote SQL Server.

Step 6: Using our Model Classes

Let’s now write a (very) simple page that uses our Northwind model classes to display some data from our database. 

We’ll begin by adding a new page to our ASP.NET project.  Right-click on the web project and choose Add->New Item, then select the “Web Form using Master Page” item template.  We’ll name the page “Products.aspx” and have it use the “Site.master” master-page that is included by default with the ASP.NET Web Project starter template.

We’ll add a <asp:GridView> control to our new Products.aspx page.  We’ll configure it to show just the name and price of our Products:

image

Within our code-behind file we can then write the following LINQ query against our model classes to retrieve all active products from our database, and bind them to the above GridView control: 

image 

And now when we run the project and navigate to the Products.aspx page we’ll get a listing of our Products like so:

image

We now have a simple application that uses EF “code first” against an existing database. 

Download Sample

You can download a completed version of the above sample here.  It assumes you have EF “code first” CTP4 and SQL Express installed.

Additional Code Examples

Below are some additional code examples that demonstrate how we could use our Northwind model for other common scenarios.

Query Across Relations

The LINQ query below demonstrates retrieve a sequence of Product objects based on the name of the Category they belong to. Notice below how we can write LINQ queries that span both the Product and a sub-property of its associated Category object.  The actual filter is all done in the database engine itself – so only Product objects get returned to the middle tier (making it efficient):

image

Use the Find method to retrieve a single Product

In addition to allowing you to write LINQ queries, EF “Code First” also supports a “Find()” method on DbSet<T> collections that allows you to write code like below to retrieve a single instance based on its ID:

image

Inserting a New Category

The code below demonstrates how to add a new Category to the Database:

image

Notice how we create the Category object, assign properties to it, then add it to the Context’s Categories collection.  We then call SaveChanges() on the context to persist updates to the database.

Inserting a New Category and Product (and associating them)

The code below demonstrates how to create a new Category and a new Product, associate the Product so that it belongs to the new Category, and then save both to the Database:

image

Notice above how we are able to have the new Product reference the newly created Category by assigning its “Category” property to point to the Category instance.  We do not need to explicitly set the CategoryID foreign key property – this will be done automatically for us when we persist the changes to the database.

EF uses an pattern called “unit of work” – which means that it can track multiple changes to a context, and then when “SaveChanges()” is called it can persist all of them together in a single atomic transaction (which means all the changes succeed or none of them do).  This makes it easier to ensure that your database can’t be left in an inconsistent state – where some changes are applied and others aren’t. 

In the code snippet above both the Category and the Product will both be persisted, or neither of them will (and an exception will be raised).

Update a Product and Save it Back

The code below demonstrates how to retrieve and update a Product, and then save it back to the database.  Earlier I demonstrated how to use the Find() method to retrieve a product based on its ProductID.  Below we are using a LINQ query to retrieve a specific product based on its ProductName.

image

We could make any number of changes (to any existing objects, as well as add new ones).  When we call SaveChanges() they will all be persisted in a single transaction back to the database.

Default Conventions vs. Custom Mapping Rules

When we created the Product and Category classes earlier, we used the default conventions in EF “Code-First” to map the classes to/from the database.  This avoided the need for us to specify any custom mapping rules, and kept our code really concise.

There will definitely be times when you don’t like the shape of the database your are mapping, though, and want to have your model’s object model be different.  Refer back to my Custom Database Schema Mapping blog post for examples of how to use EF to specify custom mapping rules.  These all work equally well when mapping existing databases.

Summary

I’m pretty excited about the EF "Code-First” functionality and think it provides a pretty nice code-centric way to work with data.  It brings with it a lot of productivity, as well as a lot of power.  In particular I like it because it helps keep code really clean, maintainable, and allows you to do a lot concisely.  Hopefully these last three blog posts about it provides a glimpse of some of the possibilities it provides – both for new and existing databases.

You can download the CTP4 release of EF Code-First here.  To learn even more about “EF Code-First” check out these blog posts by the ADO.NET team:

Hope this helps,

Scott

P.S. In addition to blogging, I am also now using Twitter for quick updates and to share links. Follow me at: twitter.com/scottgu

Published Tuesday, August 3, 2010 10:09 PM by ScottGu
Filed under: , , ,

Comments

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:14 AM by Chris

Awesome work guys...

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:26 AM by @augustlights

As always, great, concise post. Do you know if anything's been written yet on how to do Pocos with a) self-referencing (Manager/Employee), b) many to many relationships (I see you avoided Orders in your NW example)? Thx!

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:28 AM by Krunal

This is really really good.. :)

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:13 AM by Bogdan Marian

@Scott

You mentioned “persistence ignorant”, but as the same time an EF "code first" entity class must have its relations declared virtual (probably, internally, a proxy is generated to allow lazy loading of all the collections) - this certainly means I have to know about persistence lazy loading principle, don't I? No more "persistence ignorance" for me ... How about using the new System.Lazy<T> class inside EF framework?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:31 AM by Rob Conery

Do you still need to have the foreign key fields identified in the entities for the child collections? In other words - if you remove CategoryID from Product, will the relationship hold? Even if you specify the mapping?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:05 AM by ScottGu

@augustlights,

>>>>>>>> As always, great, concise post. Do you know if anything's been written yet on how to do Pocos with a) self-referencing (Manager/Employee), b) many to many relationships (I see you avoided Orders in your NW example)? Thx!

Glad you found that many-to-many post.  I'm going to try and do a post of my own that covers many to many and self-referential scenarios too. I'll also cover more table shredding scenarios as well.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:06 AM by jeroenh

Can the "lazy loading" feature be tweaked? Suppose I want to load a page of categories with their products, can I avoid a SELECT N+1 problem (at runtime, obviously)?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:08 AM by ScottGu

@Bogdan,

>>>>>>> You mentioned “persistence ignorant”, but as the same time an EF "code first" entity class must have its relations declared virtual (probably, internally, a proxy is generated to allow lazy loading of all the collections) - this certainly means I have to know about persistence lazy loading principle, don't I? No more "persistence ignorance" for me ... How about using the new System.Lazy<T> class inside EF framework?

The Lazy<T> idea is an interesting one - I'll forward it to the data team to consider.  Virtual is currently required since EF creates proxy objects to enable the interception points.  

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:16 AM by ScottGu

@Rob,

>>>>>>>>> Do you still need to have the foreign key fields identified in the entities for the child collections? In other words - if you remove CategoryID from Product, will the relationship hold? Even if you specify the mapping?

No - you can remove the Foreign Keys and association relationships will still work.  Specifically, you can change Product to be like below with no CategoryID - but still have a Category property:

   public class Product

   {

       public int      ProductID    { get; set; }

       public string   ProductName  { get; set; }

       public Decimal? UnitPrice    { get; set; }

       public bool     Discontinued { get; set; }

       public virtual Category Category { get; set; }

   }

EF "code first" can handle this just fine and all of the above code snippets will still work.  You don't need to setup any mapping rules to enable this - the default conventions support it out of the box.

Note that you do need to still expose a primary key for each entity (this doesn't have to be integer based - you can use any unique key). But explicitly exposing the foreign keys is not required.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:20 AM by ScottGu

@jeroenh,

>>>>>>>>>> Can the "lazy loading" feature be tweaked? Suppose I want to load a page of categories with their products, can I avoid a SELECT N+1 problem (at runtime, obviously)?

Yes - you can customize the loading with hints within a LINQ query. EF is nice because you can do this on a per-query basis.  You can use the .Include() method within LINQ queries to identify which properties of a set that you want to pull in - allowing you to avoid the SELECT N+1 problem.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:21 AM by ScottGu

@Øyvind

>>>>>>>>>> I really like this. However, I am also really interested in techniques for query optimisation in the databases that are targeted by EF (efficient indexing etc). Have you got any suggested reading material on this topic?

I don't have pointers to this right now - but will forward to the EF team to see if they can put some together.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:31 AM by int08h

Why we must have a CategoryID property in Product class but not to access product.Category.CategoryID?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:42 AM by ScottGu

@int08h

>>>>>>> Why we must have a CategoryID property in Product class but not to access product.Category.CategoryID?

You don't need to expose a CategoryID property on your Product class. I've updated my original post to remove it and just have the Category property to help clarify this.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 4:58 AM by chrisb

I know similar questions have been asked many times before, but will enums be supported in the near future?  Since you're now pushing code first and designerless methodologies, surely it cant be that hard to get it into a CTP along with the POCO stuff?  Cheers

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 5:28 AM by Billy

It's 2 years since sql 2008 was released and still EF doesn't support the user defined table type which forces us back into hand written ADO. Surely if this is MS's data strategy going forward it needs to at least support the types of it's own products?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 7:20 AM by Alexnaldo Santos

Scott,

How to update the database after "Code First" with new tables/columns ?

Is true that EF only can create a new database and can't update ?

Thanks,

Alexnaldo Santos

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 8:01 AM by Vitor Canova

How can I create a Linq do query products like that SQL :

SELECT * FROM PRODUCTS WHERE PRODUCTNAME LIKE '%GREEN%'?

I need to write my own Expression?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 8:54 AM by Scott Prugh

Scott,

We have a good deal of legacy databases that conform to a certain set of naming standards that have a long history.  I have been able to overide the naming by specifying every Entity and Field in the modelBuilder.Entity<T>().MapSingleType synatx.  But, this is pretty tedious for hundreds of tables and thousands of columns especially when there is a deterministic mapping from the Property name to the FieldName(ProductId -> product_id).  Is there a way to either:

-customize the inference logic that CodeFirst EF uses to produce the column mapping

-or dynamically iterate the mappings and set them in code.  ModelBuilder doesn't seeem to contain a way to get at the mappings on the types in the OnModelCreating call and ObjectContext cannot be accessed at this point in the code.

I assume I could always use reflection to do this, but this seems awkward.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 9:27 AM by Sergio Tapia

Scott as always your posts are amazing. I'm a bit confused, I'm using Linq-to-sql for my MVC2 application (again, NerdDinner was tremendously helpful) and I was told maybe I should upgrade to EF. Is there only one Entity Framework or is this post refering to a new 'in-progress' version of EF?

Thank you for your time.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 10:04 AM by José F. Romaniello

About lazy load; I think you are doing it wrong.

If you intercept the Product part and not the Category, even p.Category.Id will hit the database and this is bad.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 10:44 AM by emmanuel

Scott, I'm just curious if code first for an existing db will work for db without relations  (object persistent db). I know, its not the best practice for storing data in relation db but i have a client who's db was designed as data persistence so there are no foreign key or relation on the table but the data in the tables do related to each other logically (1 to 1, many to many, many to 1). What i want to know is if i can use code first on such a db without placing relationships on the tables. Thanks.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 10:55 AM by Damien Guard

@chrisb Enums are coming but this CTP is just for Code Only which generates the XML mapping EF4 requires at runtime and some lightweight helpers. It doesn't modify EF4.

@sergio These posts are using the shipping version of EF4. That version supports POCO and all the clever magic needed for these things to work. Code Only simply generates the XML (EDMX) mapping at runtime based on your objects and any fluent configuration or data annotations you have used.

[)amien

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 11:41 AM by df

Really excited about this functionality. Can you point me to some examples that cover using EF 4 code first with stored procedures? I have not seen any. If you have information on enhancements/gotchas in this area, that would be great too. Thanks!

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 11:48 AM by Jeff

@Vitor Canova

>> How can I create a Linq do query products like that SQL :

>> SELECT * FROM PRODUCTS WHERE PRODUCTNAME LIKE '%GREEN%'?

You can write the LINQ query this way:

var query = from p in ctx.Products

           where p.ProductName.Contains("GREEN")

           select p;

Jeff

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 12:30 PM by Jeff

@Billy / @chrisb

User-defined types are very near the top of our priority list (along with enum support) for the next major release of the Entity Framework. One of the themes of that release will be all about enabling cool things in SQL Server. Code First will support these types at the same time as core Entity Framework supports them.

Jeff

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 12:32 PM by Jeff

@df

Unfortunately, Code First doesn't yet support mapping to stored procedures on existing databases. This is on our radar though, so if you have any specific requests/requirements, do let us know!

Jeff

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:22 PM by ScottGu

@chrisb,

>>>>>>> I know similar questions have been asked many times before, but will enums be supported in the near future?  Since you're now pushing code first and designerless methodologies, surely it cant be that hard to get it into a CTP along with the POCO stuff?  Cheers

Enums aren't supported with the current CTP - but that is something that will be supported in the future.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:23 PM by ScottGu

@Billy,

>>>>>>>>>> It's 2 years since sql 2008 was released and still EF doesn't support the user defined table type which forces us back into hand written ADO. Surely if this is MS's data strategy going forward it needs to at least support the types of it's own products?

Not sure if you saw Jeff's response above (Jeff works on the EF team).  User defined table types aren't supported by EF today - but are coming in the future.  

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:27 PM by ScottGu

@Alexnaldo Santos

>>>>>>>>> How to update the database after "Code First" with new tables/columns ?  Is true that EF only can create a new database and can't update ?

You can use a variety of approaches to update the database schema.  For the scenario above (where we were using an existing database) you could use the Server Explorer in VS (or any other tool or script) to update the schema, and then update the model classes to reflect that.  Because EF is strongly-typed, you can use refactoring within VS so that when you make a change to the model class you can update your other code accordingly.

In my first and second blog posts in this series I showed how you can start with code-first, and then generate the SQL database from it.  There is an option in EF to not only create the database, but recreate/update it automatically when you modify the model class definitions.  My first blog post in this series covers how to do this.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:28 PM by ScottGu

@Vitor,

>>>>>>>How can I create a Linq do query products like that SQL: SELECT * FROM PRODUCTS WHERE PRODUCTNAME LIKE '%GREEN%'?

As Jeff mentioned above, you can write a LINQ query like this to enable this:

var query = from p in ctx.Products

          where p.ProductName.Contains("GREEN")

          select p;

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:32 PM by ScottGu

@Scott Prugh,

>>>>>>> -customize the inference logic that CodeFirst EF uses to produce the column mapping, -or dynamically iterate the mappings and set them in code.  ModelBuilder doesn't seeem to contain a way to get at the mappings on the types in the OnModelCreating call and ObjectContext cannot be accessed at this point in the code.

I'm following up with the EF team on this to see if there is a good recommendation.  I'll let you know what I hear back.

Thanks,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:36 PM by ScottGu

@Sergio,

>>>>>>>>> Scott as always your posts are amazing. I'm a bit confused, I'm using Linq-to-sql for my MVC2 application (again, NerdDinner was tremendously helpful) and I was told maybe I should upgrade to EF. Is there only one Entity Framework or is this post refering to a new 'in-progress' version of EF?

You don't need to migrate from Linq-to-SQL - that is still fully supported (and has been enhanced with .NET 4).  EF (and especially EF code first) offers some nice features as well that you can take advantage of - but if you are happy with Linq to SQL you should just continue using that (you shouldn't feel like you have to move).

When it comes to EF there is one core EF that ships in .NET 4.  That provides all of the base support. You can use it with VS 2010 and .NET 4 out of the box today - including with full designer support.  

EF "code first" is then a library that builds on top of the EF in .NET 4 and adds the ability to use EF without a designer, and instead use a convention and code-based mapping customization option.  The final release of this code-first library will ship in a few months time.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:45 PM by ScottGu

@Jose,

>>>>>>>>> If you intercept the Product part and not the Category, even p.Category.Id will hit the database and this is bad.

I'm not sure I understand what you are saying.  The interception is not on retrieving the Product, but rather when you access its Category property.  This enables EF to lazy-load the data required to return a reference to the Category object the first time it is accessed.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:49 PM by ScottGu

@emmanuel

>>>>>>>>> Scott, I'm just curious if code first for an existing db will work for db without relations  (object persistent db). I know, its not the best practice for storing data in relation db but i have a client who's db was designed as data persistence so there are no foreign key or relation on the table but the data in the tables do related to each other logically (1 to 1, many to many, many to 1). What i want to know is if i can use code first on such a db without placing relationships on the tables. Thanks.

That is a very good question.  Jeff from the EF team saw your question and is working on a sample that will hopefully demonstrate how to-do this.  He'll post a comment lower on this post once he plays with it a little more.

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:50 PM by ScottGu

@df,

>>>>>>>> Really excited about this functionality. Can you point me to some examples that cover using EF 4 code first with stored procedures? I have not seen any. If you have information on enhancements/gotchas in this area, that would be great too. Thanks!

Wanted to make sure you saw Jeff's response to your question above. Unfortunately there is no sproc support in Code First at the moment, but it’s on our list to accomplish before we RTM. If you want to use sprocs today, you have to create an EF EDMX file and then you can use that EDMX file with your DbContext (so, no fluents or auto-mapping).

Hope this helps,

Scott

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 1:55 PM by mxmissile

Posted a question on Stackoverflow related to mapping to a legacy table:

stackoverflow.com/.../code-first-entity-configuration-totable-problem

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:10 PM by bill xie

wow, finally Microsoft just realized that how easy data access could be in the .NET!!! This kind of practice has already been practiced for years since .NET 1.x.

A big advantage of this method is that every developer knows about the same way, no magic or tricks behind.

There are some noises here:

1) State tracking: tracking of the change is mostly for building the update sql statement. Developers may never use it. Note that any database context object are short lived object, tracking or caching does not make too much sense to developers.

2) FK/PK relationship. FK/PK is a constraint for data. In LINQ it allows you to write product.Category.Name = "xxxx", it seems also to allow you to write select category.Products. Actually it already distort the meaning of FK/PK. The constraint here becomes a query filter. Unfornately there may be many queries like that. Sometimes such kind of FK/PK representation in the front end does not make any sense. Given FK/PK is vital in data driven applications, I think developers should have direct control over them. It is better to reflect them in the query.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:24 PM by dc

I think this is really neat.  I notice that if the database and code aren't the same in some cases.  For example if the database has a nullable field but you don't specify it as nullable in code it will give you the general error saying that the database has changed and you need to update it or recreate it.  Is there any plan to make that error more descriptive?  

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:30 PM by Jeff

@emmanuel

It is possible to use Code First and define logical relationships in your classes, but not have these backed by real constraints in the database. Basically Code First just works: you create your logical associations (via navigation properties), and Code First will just create a mapping that thinks your database has a constraint/relationship between two tables. This will allow you to lazy/explicit load. You’ll get an exception if your relationship doesn’t return what it should (i.e. the Entity Framework thinks there can only be a single reference, but it finds two candidates in the database). In essence, you are telling CodeFirst a fib about what the database looks like, but this should still work if you are honoring your logical relationships in the database even if they aren’t enforced with a real constraint. I built a small sample of this this morning and had success so let us know if you run into anything along the way.

Thanks,

Jeff

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 2:39 PM by Jeff

@mxmissile

It appears you found a bug in the overload of MapSingleType() that takes no parameters! As a work around, you can use the overload of MapSingleType that contains the column mapping for your type. I posted a sample of the work-around in your Stackoverflow post.

Jeff

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 3:37 PM by Bruce

Very disappointed that the Lazy<> convention isn't (currently) supported. The difference between that and having to declare virtuals is a big turn-off for me...

Thanks for the great post, though, as always!

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 4:12 PM by Stilgar

I want to second Jose's question/comment.

As you pointed out earlier you can remove the CategoryID field from Product because it is kind of duplicated in Product.Category.ID. However if the Category is loaded lazily then will accessing the ID field still result in a trip to the DB? If we were using the CategoryID field of the Product class this would not be the case. It is possible to create a proxy Category object that only has an ID and accessing the ID does not result in loading the Category object from th DB. The question is does EF do that or it does lazy loading even if the PK property is accessed?

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 4:39 PM by Daniel Brownell

Thanks Scott this is excellent...I was able to get it working with my external SQL Server (2008).  Now I can start playing around with some "seperation of concerns" ideas and see how it goes...thanks a million...u are the mannnnnnn....

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 6:58 PM by aregs

Is there a way to exclude an object property from the table that is mapped to that object?

For example I have an Account class

with Password and  HashedPassword properties. But I want the table that it maps to only contain a HashedPassword column.

Thanks

# re: Using EF “Code First” with an Existing Database

Wednesday, August 4, 2010 10:10 PM by Jeff

@aregs

In the current CTP of CodeFirst, there is no way to exclude/ignore properties, but this is on our list for additions before the final release. We hope to add a data annotation attribute that you can use or an API call to ignore properties or types.

Jeff

# re: Using EF “Code First” with an Existing Database

Thursday, August 5, 2010 12:19 AM by clement_911

Well done Scott that is really cool.

It would be great if you could extract the functionality that creates/updates databases and tables on the fly and make it a public available API in the .Net framework. Maybe a provider model where each db can plug a provider (Sql Server, My Sql, Oracle, ...).

The reason I'm interested is that it would enable some very dynamic scenario where the shape of entities are not known at compile time but we still want to store entities in a structured table (to enable querying, aggregation, paging, etc...).

# re: Using EF “Code First” with an Existing Database

Thursday, August 5, 2010 12:36 PM by Mark McPherson

Code first appears to be a very elegant and flexible approach. When I tried to access a single table from an existing database I get the following message:

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.  

For example, the RecreateDatabaseIfModelChanges strategy will automatically delete and recreate the database,

and optionally seed it with new data.

In your example I notcied that you did not map all existing fields from the Northwind Products table so I assume this is not a requirement.

Why is this execption being thrown?

Thanks,

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 2010 12:16 AM by Todd

Seems valuable, but flat out does not work.  Download the sample.  Attach the included database to SqlServer (not using SqlExpress) and you get:

 The model backing the 'Northwind' 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.

I've tried to duplicate the concept in this article on one single table in an existing database with one unit test and get the exact same error/result.  Really shouldn't be that hard.

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 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

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 2010 11:46 AM by Bhavesh @ .Net Blog

much flexible than ADO.NET POCO generator template.

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 2010 11:49 AM by Igor

What about the batch operation?

For example I've like to delete all products by some category.

Here is an example :

northing.Product.Delete().Where(p => p.Category.CategoryId == 2);

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 2010 5:27 PM by Morder

Scott, How can we create a single table only automatically using EF? Right now the entire database is updated if we have a change in our model but I would like to only create a Table based on an existing model. Basically I want to have a single Model take the place of multiple tables. Something like: public DbSet<Person> PeopleTypes(int type) {get; set;}

Then if I want to map to different tables in the db i would just call db.PeopleTypes(1) or db.PeopleTypes(2). And I'd like to be able to create the table on the fly if the table doesn't exist (something like "PeopleTypes1" Since the ModelCreating is only called once there's currently no way I can see to map the object to many different tables...I hope all this makes sense.

# re: Using EF “Code First” with an Existing Database

Friday, August 6, 2010 8:04 PM by Jeff

@Mark

Here is some background on the exception you are seeing and what you can do about it:

"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..."

When a DbContext is first created, a configurable DatabaseInitializer is run to find or create the database. The default DatabaseInitializer tries to compare the database schema needed to back the model with a hash of the schema stored in that EdmMetadata table that is created with a database. Existing databases won’t have the EdmMetadata table and so won’t have the hash…and our implementation today will throw if that table is missing. It sounds like we need to change this behavior. 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

# re: Using EF “Code First” with an Existing Database

Saturday, August 7, 2010 9:51 PM by JulianR

Hi Scott,

The EF with the POCP approach looks really great. However, in your second last example, you create two new entities, set associations between them, but you also add *both* to the datacontext. In LINQ-to-SQL, when you create entities that have associations between them, you only need to add the 'top level' one to the datacontext, and all other new entities will be automatically inserted too. So like this:

var customer = new Customer { .. };

var address = new Address { .. };

customer.Address = address;

context.Customers.InsertOnSubmit(customer);

context.SubmitChanges(); // Inserts both entities

Is that scenario supported by EF?

# re: Using EF “Code First” with an Existing Database

Monday, August 9, 2010 4:28 PM by Daniel Done

For everyone having "the model backing the 'Production' context has changed since the database was created" error, just do the following:

       protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)

       {

           modelBuilder.IncludeMetadataInDatabase = false;

       }

# re: Using EF “Code First” with an Existing Database

Tuesday, August 10, 2010 1:21 PM by michel liesmons

Hi Scott,

Looks promissing.

We are currently using EF4 in a multi-tier scenario using the STEs.

What's the story on tracking serializable state changes using the Code First approach?

We also have a hard time splitting a model over different databases and extending entities across EDMXes, i.e. derive an entity living in EDMXDerived from an entity living in EDMXBase.

Are these scenario's better supported in the Code First model.

kr,

Michel.

# re: Using EF “Code First” with an Existing Database

Sunday, August 15, 2010 2:44 AM by moncler

Excellent article. Thanks a lot and keep it up.

# re: Using EF “Code First” with an Existing Database

Monday, August 16, 2010 3:54 AM by Balaji B

Hi Scott

Great work as you always do. Thanks a lot.

# re: Using EF “Code First” with an Existing Database

Monday, August 16, 2010 11:09 AM by wkosten

Hi Scott,

Your demo works like a charm at my place, but after implementing a more business related model I've encountered performance issues. I generated 10.000 objects which was done in a few milliseconds, adding them to the object context took about 29 seconds. The next call "SaveChanges" took almost 1 minute and eventually caused an OutOfMemoryException, and this on a 6Gb / quadcore machine. Is this a problem of the CTP release or isnt it cappable handling a large amount of data?

Thanks in advance, Wilko!

# re: Using EF “Code First” with an Existing Database

Monday, August 16, 2010 11:36 PM by ScottGu

@wkosten,

>>>>>>>>> Your demo works like a charm at my place, but after implementing a more business related model I've encountered performance issues. I generated 10.000 objects which was done in a few milliseconds, adding them to the object context took about 29 seconds. The next call "SaveChanges" took almost 1 minute and eventually caused an OutOfMemoryException, and this on a 6Gb / quadcore machine. Is this a problem of the CTP release or isnt it cappable handling a large amount of data?

Are you trying to do a bulk add/insert?  

If you can send me email (scottgu@microsoft.com) I can connect you with the EF team who might have some good recommendations on how best to handle this.

Thanks,

Scott

# re: Using EF “Code First” with an Existing Database

Tuesday, August 17, 2010 2:09 AM by John

Scott,

Great article. In your code you don't have a constructor to initialize the Collections, I couldn't get it to work without a constructor to initialize them. Am I missing something or should it be in the above examples?

Thanks,

John

# re: Using EF “Code First” with an Existing Database

Thursday, August 19, 2010 3:48 PM by jbeckton

I would like to see if or how one would utilize the MVC model validation when using EF 4 and POCO's in place of the MVC model.

# re: Using EF “Code First” with an Existing Database

Tuesday, August 24, 2010 5:48 AM by baz

I think this is the best MS product about data management from many years...

# re: Using EF “Code First” with an Existing Database

Tuesday, August 24, 2010 6:44 PM by David Evan

Scott or someone please provide a consise way of working with XML and serialization in the context of a "Code First" scenario where no explicit transformation queries with hard coded object/table and property/columns are required.  Think Dynamic.  Think the beauty of DataSet.ReadXML(myWhateverSQL and then just loading that into an XElement object where I can then bind the XML contents directly to the DOM through a jQuery AJAX.  Think of life without a SQL Server suitcase on your back. "Code First" with EF -SEEMS- to be the right direction but I see nothing nada, not even un poco, in examples with XML used as a data source or target other than indirectly via WCF.  

If I'm wrong where's the myFEObject.ReadXML or myFEObject.WriteXML methods to correspond those that were the beauty of a DataSet, that are readily serializable and less expensive than using explicit serialization or throwing explicit serialization decorators on every object?  

How is this this approach at all any better than a DataSet in terms of inferred transformation from an XML,XML with XSD Schema and a Diffgram?  Can businesses really afford to contact a C# or VB.Net developer every time they need to change something in their data?  Can Microsoft or any software company really make the case denying everything we've learned about loose coupling and dynamic business rules?

Pleaase advise.

# re: Using EF “Code First” with an Existing Database

Wednesday, August 25, 2010 5:52 PM by Jacob

Why does EF sometimes expect a different field name than the propery? e.g. System.Data.SqlClient.SqlException: Invalid column name 'CreatedBy_ID'. My database field is named CreatedBy just like the property, but EF seems to want to rename it. Is this convention documented somewhere? How do I changed the default?

   public class Member

   {

       public int ID { get; set; }

   }

   public class Topic

   {

       public int ID { get; set; }

       public Member CreatedBy { get; set; }

   }

# re: Using EF “Code First” with an Existing Database

Sunday, August 29, 2010 8:04 AM by alaa9jo

@Jacob:

Foreign keys are named by default like: ColumnsName_PKColumnName

In your case it will create a column at Topic table : CreatedBy_ID,this is done by default.

However,you can control this by changing your code to this:

public class Member

  {

      public int ID { get; set; }

  }

  public class Topic

  {

      public int ID { get; set; }

      public int MemberID{get;set;}

      [RelatedTo(ForeignKey = "MemberID")]

      public Member CreatedBy { get; set; }

  }

MemberID will be created instead of CreatedBy_ID and as a foreign key.

# re: Using EF “Code First” with an Existing Database

Monday, August 30, 2010 2:35 AM by Karim

@Daniel Done:

Nice catch!

I was preparing a nice whining post about how annoying it is to have to run twice before changes are taken into consideration :)

Nevertheless, it would be great if Scott could confirm whether that's indeed the best approach?

# re: Using EF “Code First” with an Existing Database

Tuesday, August 31, 2010 2:36 PM by Jacob

Oops, I just noticed that it works even without the underscore. I guess EF tries both. I must have been getting the error because I didn't define the integer key in the class:

public int CreatedByID { get; set; }

So I guess all is good. But maybe the error message should mention the non underscored field guess since that's how most people will probably name things. I won't be the last person to be confused by this.

# re: Using EF “Code First” with an Existing Database

Friday, September 10, 2010 9:01 PM by xuanvu

When I'm not having the "virtual" keyword on the related tables, WCF Data Service runs just fine, but lazy/deferred loading is disabled, adding "virtual" to the properties enabled lazy/deferred loading but in turn, caused the WCF Data Service to stop working. Is there a way to have lazy/deferred loading with EF POCO and WCF Data Service? Thanks.