Entity Framework Core 1.0 Database-First to Code-First

There is much interest within the .NET development community with the announcement of the new introduction of ASP.NET 5aka ASP Core 1.0, formerly known as ASP.NET 5 and Entity Framework 7 formerly known as Entity Framework Core 1.0. The highlight of these technologies is the ability to develop applications that run on the classic .NET framework, and the all new .NET Core which runs on top of the new .NET Execution Environment (DNX) which enables developing cross platform application to run on Windows, Linux, Mac.

Both ASP.NET Core and EF Core frameworks ground up implementations with quite a few changes to the traditional way of how we used to work with ASP.NET applications, however there is much more capabilities offered with the new versions, albeit they are still in the RC state, which is perfectly fine for playing around, experimenting and getting your hands dirty.

Its fairly easy to start off with EF Core using the code first approach, in fact; there are quite a number of blog posts that explain code first. Hence in this post is on how you can start off using an existing database using EF Core, together with some insights on the new ASP.NET.

Before we go any further, one important highlight with the new EF Core and VS tooling is,

No more EDMX support!

Currently you are able to create your model in two ways, using an XML based EDMX in the designer, or by using a code first with a set of classes and DBContext that defines the mappings. The model you choose has no difference on how the EF Framework behaves at run-time. The framework will create an in-memory model by reading the EDMX, or by reflecting upon the DBContext and related classes and its mappings.

Also as highlighted by Julie Lerman on “Data Points - Looking Ahead to Entity Framework 7”, going forward EF will no longer support the EDMX based model although database-first will be supported (using scaffolding) which can thereby evolve as code-first model. Updates/changes to the data model can later be migrated and applied to the database as and when necessary.

For those developers who are bonded with the EDMX designer, this post will detail the steps on how you can make use of an existing database (database-first development) to generate a code first model and move on with updating the data model and migrate and apply the updates to the database (code-first development)

For those developers who are bonded with the EDMX designer, this post will detail the steps on how you can make use of an existing database (database-first development) to generate a code first model and move on with updating the data model and migrate and apply the updates to the database (code-first development).

Creating the Data Model

You will need Visual Studio 2015 installed on Windows which is what I will be using to outline the actions that need to be performed. Upon installation of VS 2015 you will also need to upgrade the .NET Version Manager (DNVM) to use the latest version of the .NET Execution Environment (DNX). You can follow the steps detailed at “Installing ASP.NET 5 On Windows” to get your self up to speed.

For brevity we will start of with creating a console application project which will contain our Entity Data model.

Create the project

1. Open Visual Studio
2. Select File > New Project
3. Select Visual C# > Windows > Web
4. Select Console Application (Package) and give your solution and project a name like so,
   
   

New Project Structure

This console application is not the traditional type of console application we are used to. It is based on the new project convention Microsoft introduced for ASP.NET Core. There are quite a few changes with the new project structure. What you would immediately notice is that the app.config file is missing. Instead there is a project.json file. This is one of the overhauls of the ASP.NET Core, where the entire project will be based-off over a JSON configuration file called project.json. I will not be talking much about the project.json file here, except for the bare essentials. However if you are interested to get to know more about the project.json file refer to this wiki on GitHub.

Add References to EF Commands

In order to generate the data model based on the database, we need a reference to a couple nuget pacakges,

• EntityFramework.Commands
• EntityFramework.MicrosoftSqlServer
• EntityFramework.MicrosoftSqlServer.Design

EntityFramework.Commands – This package provides all the necessary commands to work with EF such as scaffolding the database, creating/applying migrations etc.

EntityFramework.MicrosoftSqlServer and EntityFramework.MicrosoftSqlServer.Design – These packages provide Microsoft SQL Server specific capabilities for entity framework to work with since we are suing Microsoft SQL Server as our database.

As of now VS 2015 does not tooling support for EF Core to generate your data model from the database. Hence we will be using the command line to generate it for us. Open the project.json file and add the dependencies as shown below,

Save your project.json and you the relevant package will be downloaded to your DNX profile and referred from within the project.

Note the section where the dependencies are declared

If it is declared within the a target framework, the declared dependencies will be only available to that specific framework as shown in the yellow box. If you require a dependency to target both the .NET full framework and the .NET Core framework you can declare it in the global dependencies section where the EntityFramework Packages have been declared, shown in the green box.

You also need to make sure you added a command as shown in the purple box, which will be the entry point from DNX to access the EF Command.

Run Entity Framework Commands

1. Open command-prompt in Administrator mode
2. Execute dnvm list and validate if you are using the latest runtime as shown below. If not use dnvm upgrade to download and use the latest runtime.
   
   

 

1. Navigate to the folder of you console application project.
2. Execute dnx ef and validate if you are able to access the EF Core as shown below,
   
   

Scaffold database to Code-First DBContext

Execute dnx ef dbcontext scaffold -h. This will list you all the parameters that are required to scaffold the DBContext against the target database as shown below,

At a bare minimum you need to input two arguments, a [connection] to the target database and a [provider] to use for working with the database. I will also specify the -c to specify a custom name for the DbContext. You can try this out with any database you have at your side. I happen to have a StudentDatabase with just two tables.

In order to generate the DbContext against the database you can execute the following EF command,

dnx ef dbcontext scaffold "data source=.;initial catalog=StudentDatabase;Integrated Security=true" "EntityFramework.MicrosoftSqlServer" -c "StudentDbContext".

This will create you the DbContext against the target database and you should be able to see the classes already included in the project in VS Solution Explorer for your use as shown below,

Query the database using the DbContext

You should now have a DbContext scaffolded using the EF command. Hence lets try to query for some data using the code below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace App.Console
{
public static class Program
{
    public static void Main(string[] args)
    {
        // Create DbContext.
        var context = new StudentDbContext();

        // Display students.
        context.Student
            .ToList()
            .ForEach(s => System.Console.WriteLine("Student ID: {0}, First Name: {1}, Last Name: {2}", s.Id, s.FirstName, s.LastName));

        System.Console.ReadKey();
    }
}
}

Voila! We are able to query the database, without having to type all the cumbersome code.

Wait a minute! How does the application know on how to connect to the database. Well as part of the scaffold  process the DbContext is automatically  configured to used the connection string we provided to the scaffold command. This is not a nice way  to maintain the connection string. The new framework supports much better ways to overcome this issue by enabling configuration options to be passed as dependencies, which we will look at in a future post.

An important aspect with EF is migrations where you are able to maintain versions of your data model as and when it evolves over time. I will be writing up more on how you can perform migrations over your data model in the coming series of posts.

Happy Coding!

Continue Reading

Entity Framework 5 with enums

Entity Framework 4 and earlier version limitations

Enums or Enumration Types are a very useful feature in .NET framework, that lets you define a collection of logical options as a specific type. Although this feature is a part of the language, it was not supported by the ADO.NET Entity Framework. In previous version of Entity Framework 4 and earlier, there was no possible way that you could define a scalar property as an enum type in any of your entities. It was possible to however explicitly associate integral type scalar properties to an enum via an explicit cast to the desired enum type.

The caveat with this explicit cast is that, a developer could cast any integral entity scalar property type to an enum type provided the enum being cast to has the integer value defined as its underlying value, meaning that you would need to make sure you performing the cast over the correct enum type. This is not a major issue if you have a very few enums defined in your application, but would a be a point of confusion when there are more than a few enums with similar names.

Other approaches would include creating additional properties that encapsulate the entity property within by returning an enum based on the value of the property within the entity class. Which by its very nature can only be accomplished if the data model is instructed to use custom POCO classes.

Entity Framework 5 and Enum Support

Fortunately ADO.NET Entity Framework 5 will officially support the ability to define enum types or use existing enum types as part of your entities scalar properties. Listed below are the very basic steps to add a simple enum property to you entity, I have a very basic sample databasa scheme to illustrate this. You can download the sample solution from here, which also has a local database with this schema.

Step 1: Defining the sample database schema

The above schema represents a relational database that holds order information related to a customer and product. An order in the order table can be one of two states which is either “Delivered” or “Pending Delivery”. Hence this state of the order is represented in the Status column which is of type int within the Order table.

Step 2: Generating an Entity Data Model from the Schema

Note that you could also generate the database script that could be deployed as a database by modeling the entities first. However for this scenario I will be generating the Entity Data Model over the existing database schema. Listed below are the steps for this. In order to generate your Entity Data Model using an existing database,

1. Right Click on the project you need to include the Entity Data Model and create an ADO.NET Entity Data Model with a name that best suites your data model and click Next.
2. Select the Generate from database option and click Next.
3. Define your connection, provided a connection string name and click Next.
4. Select the Tables relevant to the model, provide a valid namesapace for the entities and click Finish.

Your Entity Data Model should look like the following,

Step 3: Setting entity property as an enum

With ADO.NET Entity Framework 5 you are able to define new enum types that best suite your usecase, or you any existing enum type definitions that’s already part of the project. In order to link the Order tables Status property as an enum,

1. Select the property and right click on it and select Convert to Enum from the context menu, which will bring up the following dialog, where I have filled in the required information.
    
2. If you desire to associate an enum type which is already part of the project, you could do so by checking the Reference external type and giving the fully qualified name for the enum type.
3. You could always modify or add options to your enum type by locating the enum created under the Model Browser –> Enum Section as shown below.
   

Step 4: Using the enum types as part of the entity.

Upon having the enum configured to use a an enum type, its just a matter of writing code against entities the same way you would against a regular old objects that contains enum types within. Listed below is the code sample for doing just that.

// Delevered orders
Console.WriteLine("Delevered Orders");
WriteOrders(orders.Where(o => o.Status == OrderStatus.Delevered));

// Pending orders
Console.WriteLine("Pending Orders");
WriteOrders(orders.Where(o => o.Status == OrderStatus.Pending));

You can download the sample solution from here of that described in the blog post and go though the application.

Continue Reading