Using Entity Framework 4.1 Code First with an Existing Database

This post focuses on using the Entity Framework 4.1 RC Fluent API with an existing database.

When I say an existing database, what I really mean is that you’re not letting EF Code First auto generate the database for you. So it could be that you like to create your entities and then manually craft the database. Or you may have a separate team of developers who like to control and manage the database.

This article refers to the fluent API which is part of the Code First approach to the using the Entity Framework.  Code First can create the database for you based on your entities and entity relationships, but as I mentioned above, you may not want Code First to do this.

Using EF Code First in this way is seen as a core aspect of Code First by the EF team at Microsoft. So far, I have to say I really like this approach, no large XML files to worry about, instead it’s all controlled through code. Additionally, the entities are plain old CLR objects (POCO). This means that your entities (and thus the assembly they are contained in) don’t need to reference the Entity Framework.

The following example shows how to create an entity model, then manually create your database and then map those entities to your database. Finally, it shows using the DatabaseContext to save and retrieve entities.

Create the Entities

Here, we have a company class and a country class. Each company has a country associated with it. There is no association going the other way:

public class Company
{
public int Id { get; set; }
public string CompanyName { get; set; }
public Country HomeCountry { get; set; }
}

public class Country
{
public int Id { get; set; }
public string Code { get; set; }
public string Name { get; set; }
}

Create the Database

Next we create the database. We could let EF do this for us, but here we want full control over the new database (or it could be an existing database you’re connecting to):

CREATE DATABASE CodeFirstCustom;
GO
Use CodeFirst
create table Companies	(
Id int identity(1,1) not null,
HomeCountryId int not null,
Name varchar(20) not null,
constraint PK_Companies primary key clustered (Id))

create table Countries	(
Id int identity(1,1) not null,
Code varchar(4) not null,
Name varchar(20) not null,
constraint PK_Countries primary key clustered (Id))

alter table Companies
add constraint FK_Company_HomeCountry foreign key (HomeCountryId)
references Countries (Id) on delete no action

Mapping the Entities to the Database

So we now have the entities and the database. Next the fun bit, mapping the two together using the fluent API which is part of EF Code First.

Firstly, we create a configuration class for each entity which maps between the entity and the database:

public class CompanyConfiguration: EntityTypeConfiguration<Company>
{
public CompanyConfiguration(): base()
{
HasKey(p => p.Id);
Property(p => p.Id).
    HasColumnName("Id").
    HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).
    IsRequired();
Property(p => p.CompanyName).
    HasColumnName("Name").
    IsRequired();
HasRequired(x => x.HomeCountry).
    WithMany().
    Map(x => x.MapKey("HomeCountryId"));
ToTable("Companies");
}
}

public class CountryConfiguration: EntityTypeConfiguration<Country>
{
public CountryConfiguration(): base()
{
HasKey(p => p.Id);
    Property(p => p.Id)
    .HasColumnName("Id")
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
    .IsRequired();
Property(p => p.Code)
    .HasColumnName("Code")
    .IsRequired();
Property(p => p.Name)
    .HasColumnName("Name")
    .IsRequired();
ToTable("Countries");
}
}

The most difficult part of the above is understanding the syntax for defining the relationship between the Company and Country entity. We actually wouldn’t need to do this if we had named the FK column HomeCountry_ID (as this is what EF will use by default). In our case though, we don’t use underscores so we have to tell the fluent API what the name of the column is. So within the configuration class for the Company, we say HasRequired(x => x.HomeCountry) – this tells EF that a home country must be set. We then define the relationship going back the other way, in this case we say WithMany() – however, in our example, there is no relation going the other way, so we leave this empty. Finally we use Map(x => x.MapKey(“HomeCountryId”)) to specify the foreign key name.

It should be noted that an alternative to the Fluent API is to use attributes on your entity. I like to keep the entities clean though, even from attributes. I’ll talk about attributes in a future post though.

Creating the Context

It is the context that we code against to add and remove entities to and from the database. We create our context by creating a class which inherits from DbContext:

public class MyContext: DbContext
{
public DbSet<Company> Companies { get; set; }
public DbSet<Country> Countries { get; set; }

public MyContext(string connectionString): base(connectionString)
{
Database.SetInitializer<MyContext>(null);
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new CountryConfiguration());
modelBuilder.Configurations.Add(new CompanyConfiguration());
base.OnModelCreating(modelBuilder);
}
}

Above, I have created a couple of properties using DbSet for our entities that we wish to expose.

In the constructor, we then tell EF Code First not to attempt to create a database as we are using our own. We then override the OnModelCreating method and load our configuration classes.

That’s it, we can then start using the context.

Using the Context

In this example, we’re starting off with an empty database:

class Program
{
private const string ConnectionString = 
@"Server=.\sql2005;Database=CodeFirst;integrated security=SSPI;";

static void Main(string[] args)
{
// Firstly, create a new country record.
Country country = new Country();
country.Code = "UK";
country.Name = "United Kingdom";

// Now create an instance of the context.
MyContext myContext = new MyContext(ConnectionString);

// Set the state of the new entity as added (note that this also adds it to the context).
myContext.Entry(country).State = EntityState.Added;
myContext.SaveChanges();

Console.WriteLine("Saved Country");

// Now insert a Company record
Company company = new Company();
company.CompanyName = "AccessUK";

// Assign our new Country instance to the Company instance
// (we could just use the instance we created above, but I want to show how
// easy it is to query the context)
company.HomeCountry = myContext.Countries.First(e => e.Code == "UK");

// This is another way of adding an entity to the context.
myContext.Companies.Add(company);

// Save the new company.
myContext.SaveChanges();
Console.WriteLine("Saved Company");

// Retrieve the company we just saved
Company savedCompany = myContext.Companies.First(e => e.CompanyName == "AccessUK");
Console.WriteLine("Retrieved Company");

Console.ReadLine();
}
}

In the above example, I create a new Company instance, save it to the database, then create a new Company to which I assign the Country. I then finish by retrieving the Company instance.

The full example created above can be found here (use an SVN client to download it): https://subversion.assembla.com/svn/agilenet/tags/EFCodeFirstExampleForBlogPost

CodeProject