Wednesday, February 15, 2012

Entity Framework 4.1 Code-First Approach

Top of Form

Entity Framework 4.1 Code-First Approach Made Easy

No matter which data access technology you use, at some point you just need to do some CREATE-READ-UPDATE-DELETE (CRUD) operations, and you need to do it fast.

In this post, I’m going to show you how to get your CRUD done with the latest version of Entity Framework in no time flat. In fact, I’d be surprised if the code to get your first CRUD operation done takes you more than a few minutes. The one thing the EF team did right with Code-First is make it really easy,

For Whom

I’m assuming you know how to create a database, build a connection string, write LINQ queries, and read C# code. If any of those things are foreign to you, then I’d suggest learning those topics first.


First of all, you need Visual Studio 2010. Next, you’ll need to install the bits for Entity Framework 4.1, affectionately known as “Magical Unicorn Edition”.

Library used

For this example, I created a simple Console Application project called CodeFirstSample and added references to three libraries:

  • System.Data.Entity: this is the base Entity Framework library.
  • EntityFramework: this is the Entity Framework 4.1 library.
  • System.ComponentModel.DataAnnotations: this library will allow us to map CLR types to the database, among other things.
  • System.Transactions: provides transactional support.

Customer Class

First, I start with a very simple-looking class:

1: namespace CodeFirstSample

2: {

3: using System;


5: public class Customer

6: {

7: public Guid CustomerId { get; set; }


9: public string Name { get; set; }

10: }

11: }

Not surprisingly, I have a SQL Server database table that is quite similar (though not exact it may be a bit different also, so I can demonstrate mapping):

1: create database CodeFirstSample;

2: go


4: use CodeFirstSample;

5: go


7: create table dbo.Customer

8: (

9: customer_id uniqueidentifier primary key

10: ,name nvarchar(128) not null

11: );

12: go

Easy as pie so far, right? Well, you could make it easier by skipping creating the database; code-first will create it for us if it doesn’t already exist (and presuming we’re a sysadmin on the server). This is handy functionality if your database is trivial in design.

Next, we need to tell Entity Framework how to translate the columns in our database table to the properties in our Customer class. We do that by changing our class to the following (changes highlighted):

1: namespace CodeFirstSample

2: {

3: using System;

4: using System.ComponentModel.DataAnnotations;


6: public class Customer

7: {

8: [Key,Column("customer_id",TypeName="uniqueidentifier",Order=0)]

9: public Guid CustomerId { get; set; }


11: [Column("name",TypeName="nvarchar")]

12: [Required,StringLength(128)]

13: public string Name { get; set; }

14: }

15: }

Entity Framework Code-First uses attributes in the Data Annotations library for mapping columns and keys.

Key Attribute: informs Entity Framework that this field is (or is part of) the primary key.

Column Attribute consists of the following arguments:

  • Name: specifies the column name in the database to which the property will be mapped.
  • TypeName: optional argument that specifies the database data type of the column.
  • Order: if the column is part of the primary key, specifies the order of the column in the primary key as a zero-based index (the IntelliSense documentation on this field is very unhelpful). This is only required if you have a composite (multi-column) primary key.

That’s all for our Customer class.


In EF 4.1, the DbContext is where all the magic happens. We’ll create a derived type called EfContext and tell it that our Customer class is part of its model:

1: namespace CodeFirstSample

2: {

3: using System.Data.Entity;


5: class EfContext : DbContext

6: {

7: public EfContext( string connection ) : base( connection ) {}


9: protected override void OnModelCreating( DbModelBuilder modelBuilder )

10: {

11: modelBuilder.Entity().ToTable( "dbo.Customer" );

12: base.OnModelCreating(modelBuilder);

13: }

14: }

15: }

See that highlighted line? That’s all it takes to map our customer to a table. There are other ways this can be done, but doing it in the OnModelCreating method is foolproof, and if you’ll explore its features, you’ll find a plethora of other goodies it can accomplish.


Next, we add a simple configuration file to our project:

1: xml version="1.0" encoding="utf-8" ?>

2: <configuration>

3: <connectionStrings>

4: <clear/>

5: <add

6: name="EfSample"

7: providerName="System.Data.SqlClient"

8: connectionString="Data Source=localhost;Initial Catalog=CodeFirstSample;Integrated Security=SSPI;"/>

9: connectionStrings>

10: configuration>

Using it

Next, well… there’s not a next. We’re done. It can’t be that simple, can it?

Yes, it can be. As long as you know how to use LINQ, you technically don’t need to learn any other data mapping techniques in order to perform CRUD operations with EF 4.1. And just to prove it, here’s some sample code that covers most all the bases:

1: var id = Guid.NewGuid();


3: // insert

4: using ( var db = new EfContext( "name=EfSample" ) )

5: {

6: var customers = db.Set();

7: customers.Add( new Customer { CustomerId = id, Name = "sam" } );


9: db.SaveChanges();

10: }


12: // update

13: using ( var db = new EfContext( "name=EfSample" ) )

14: {

15: var customers = db.Set();

16: var c = customers.Single( x => x.CustomerId == id );


18: // look ma, no SQL injection!

19: c.Name = "'; drop table dbo.Customer;--";

20: db.SaveChanges();

21: }


23: // delete - with transaction

24: using ( var db = new EfContext( "name=EfSample" ) )

25: using ( var ts = new System.Transactions.TransactionScope() )

26: {

27: var customers = db.Set();

28: var c = customers.Single( x => x.CustomerId == id );


30: customers.Remove( c );

31: db.SaveChanges();

32: ts.Complete();

33: }


35: // insert - with rollback

36: using ( var db = new EfContext( "name=EfSample" ) )

37: using ( var ts = new System.Transactions.TransactionScope() )

38: {

39: var customers = db.Set();

40: customers.Add( new Customer { CustomerId = id, Name = "sam" } );


42: db.SaveChanges();

43: //ts.Complete(); // no completion will cause rollback

44: }

Note how the connection information is specified. It’s in the form “name={config}” where {config} is the name you gave the connection string in the App.config file. This comes in handy if you need to use connect to multiple databases of the same schema.

Not only are these CRUD operations easy, they’re also fully parameterized and resilient to most attempts at SQL Injection (assuming you follow guidelines).


No comments: