1. Create an Existing Database

Typically when you are targeting an existing database it will already be created, but for this walkthrough we need to create a database to access.

The database server that is installed with Visual Studio is different depending on the version of Visual Studio you have installed:

• If you are using Visual Studio 2010 you‘ll be creating a SQL Express database.
• If you are using Visual Studio 2012 then you‘ll be creating a LocalDb database.

 

Let‘s go ahead and generate the database.

• Open Visual Studio
• View -> Server Explorer
• Right click on Data Connections -> Add Connection…
• If you haven’t connected to a database from Server Explorer before you’ll need to select Microsoft SQL Server as the data source
  
  
• Connect to either LocalDb ((localdb)\v11.0) or SQL Express (.\SQLEXPRESS), depending on which one you have installed, and enter Blogging as the database name
  
  
• Select OK and you will be asked if you want to create a new database, select Yes
  
  
• The new database will now appear in Server Explorer, right-click on it and select New Query
• Copy the following SQL into the new query, then right-click on the query and select Execute

CREATE TABLE [dbo].[Blogs] (
    [BlogId] INT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (200) NULL,
    [Url]  NVARCHAR (200) NULL,
    CONSTRAINT [PK_dbo.Blogs] PRIMARY KEY CLUSTERED ([BlogId] ASC)
);
 
CREATE TABLE [dbo].[Posts] (
    [PostId] INT IDENTITY (1, 1) NOT NULL,
    [Title] NVARCHAR (200) NULL,
    [Content] NTEXT NULL,
    [BlogId] INT NOT NULL,
    CONSTRAINT [PK_dbo.Posts] PRIMARY KEY CLUSTERED ([PostId] ASC),
    CONSTRAINT [FK_dbo.Posts_dbo.Blogs_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [dbo].[Blogs] ([BlogId]) ON DELETE CASCADE
);

2. Create the Application

To keep things simple we’re going to build a basic console application that uses Code First to perform data access:

• Open Visual Studio
• File -> New -> Project…
• Select Windows from the left menu and Console Application
• Enter CodeFirstExistingDatabaseSample as the name
• Select OK

 

3. Reverse Engineer Model

We’re going to make use of the Entity Framework Power Tools to help us generate some initial code to map to the database. These tools are just generating code that you could also type by hand if you prefer.

• Right-click on the CodeFirstExistingDatabaseSample project in Solution Explorer
• Select Entity Framework -> Reverse Engineer Code First
  (If you don’t see the Entity Framework option, ensure you have the EF Power Tools installed. You’ll need to restart Visual Studio after installing them)
  
  
• Enter the same connection details you supplied in the first section, when you created the database
• The status bar at the bottom of Visual Studio will display the status of the reverse engineering process
  

 

Once the reverse engineer process completes a number of items will have been added to the project, let‘s take a look at what‘s been added.

Configuration file

An App.config file has been added to the project, this file contains the connection string to the existing database.

<connectionStrings>
  <add name="BloggingContext" 
       connectionString="Data Source=(localdb)\v11.0;Initial Catalog=Blogging;Integrated Security=True;MultipleActiveResultSets=True"

       providerName="System.Data.SqlClient" />
</connectionStrings>

You’ll notice some other settings in the configuration file too, these are default EF settings that tell Code First where to create databases. Since we are mapping to an existing database these setting will be ignored in our application.

Derived Context

A BloggingContext class has been created in the Models folder. The context represents a session with the database, allowing us to query and save data.

The context exposes a DbSet<TEntity> for each type in our model.

public DbSet<Blog> Blogs { get; set; }

public DbSet<Post> Posts { get; set; }

You’ll also notice that the default constructor calls a base constructor using the ‘name=’ syntax. This tells Code First that the connection string to use for this context should be loaded from the configuration file.

public BloggingContext()
    : base("Name=BloggingContext")
{
}

You should always use the ‘name=’ syntax when you are using a connection string in the config file. This ensures that if the connection string is not present then Entity Framework will throw rather than creating a new database by convention.

Model classes

In the Models folder you’ll also find a Blog and Post class. These are the domain classes that make up our model.

public class Blog
{
    public Blog()
    {
        this.Posts = new List<Post>();
    }
 
    public int BlogId { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
    public virtual ICollection<Post> Posts { get; set; }
}

Configuration classes

In the Models\Mapping folder you’ll find a set of configuration classes that use the fluent API to configure how the classes map to the database.

public class BlogMap : EntityTypeConfiguration<Blog>
{
    public BlogMap()
    {
        // Primary Key

        this.HasKey(t => t.BlogId);
 
        // Properties

        this.Property(t => t.Name)
            .HasMaxLength(200);
 
        this.Property(t => t.Url)
            .HasMaxLength(200);
 
        // Table & Column Mappings

        this.ToTable("Blogs");
        this.Property(t => t.BlogId).HasColumnName("BlogId");
        this.Property(t => t.Name).HasColumnName("Name");
        this.Property(t => t.Url).HasColumnName("Url");
    }
}

Many developers would rather use Data Annotations than the fluent API to perform configuration. Currently the EF Power Tools only generates fluent API code but there are plans to enable a choice between fluent API and Data Annotations in the future.

The mapping is also redundant in some cases. For example, BlogId is configured as they key even though Code First would detect this by convention. Similarly, the properties are all explicitly mapped to columns even though the column and property names match up and would be detected by convention. You may choose to delete the generated configuration in cases where it is not required.

 

4. Reading & Writing Data

Now that we have a model it’s time to use it to access some data. We’re going to start using the classes that were added to the Models folder, so we need to add a using at the top of Program.cs.

using CodeFirstExistingDatabaseSample.Models;

Implement the Main method in Program.cs as shown below. This code creates a new instance of our context and then uses it to insert a new Blog. Then it uses a LINQ query to retrieve all Blogs from the database ordered alphabetically by Title.

class Program
{
    static void Main(string[] args)
    {
        using (var db = new BloggingContext())
        {
            // Create and save a new Blog

            Console.Write("Enter a name for a new Blog: ");
            var name = Console.ReadLine();
 
            var blog = new Blog { Name = name };
            db.Blogs.Add(blog);
            db.SaveChanges();
 
            // Display all Blogs from the database

            var query = from b in db.Blogs
                        orderby b.Name
                        select b;
 
            Console.WriteLine("All blogs in the database:");
            foreach (var item in query)
            {
                Console.WriteLine(item.Name);
            }
 
            Console.WriteLine("Press any key to exit...");
            Console.ReadKey();
        }
    }
}

You can now run the application and test it out.

Enter a name for a new Blog: ADO.NET Blog All blogs in the database: ADO.NET Blog Press any key to exit...

 

What if My Database Changes?

The EF Power Tools are designed to generate a starting point set of classes that you can then tweak and modify. If your database schema changes you can either manually edit the classes or perform another reverse engineer to overwrite the classes.

The code that gets generated by the reverse engineer process can be customized by modifying the templates that are used to generate code. You can find an example of this in the Customizing ‘Reverse Engineer Code First’ in the EF Power Tools post.

 

Summary

In this walkthrough we looked at Code First development using an existing database. We used the Entity Framework Power Tools to reverse engineer a set of classes that mapped to the database and could be used to store and retrieve data.