Entity Framework

Getting the data from the database into C# objects can be quite a tedious task. First, you need to create a C# class for every table in the database. Second, every class that you create needs to have a set of private data fields matching the database table columns. Third, some mechanism needs to be provided to facilitate loading and saving (also known as persisting) the data to the database. Finally, when all this is finished, another nightmare comes whenever changes to the database structure are required. Every change in the database needs to be reflected in the structure of your C# classes.

Object-relational mapping (ORM) tools aim to take the above described burden off the developers’ shoulders by automating the process. There are many ORM solutions available for the .NET Framework. Entity Framework is one of them

Entity Framework is an ORM product from Microsoft. Its development, however, is now being done by an Open Source community.

There are three fundamental approaches you can take when working with Entity Framework:

  • Database first
  • Model first
  • Code fist

As these names suggest, every approach differs from another by what you already have at the beginning.

If you already have an existing database, you will probably want to use Database first approach. Entity Framework will take an existing database, reads all information about its structure and generates a database model. The database model describes all tables, their columns and all other entities within the database. Once the model is created, Entity Framework uses it to generate C# classes – a separate class for every table with properties defined based on table columns. When there is a need to change the database structure in the future, you can do this in the database directly. Then you make Entity Framework to regenerate both, the model as well as the C# classes.

With Model fist approach you first need to create a database model. The model should contain all tables, their relations, etc. – basically everything you plan your database to contain. Once you have the model defined, Entity Framework can use it to generate C# classes as well as the database itself. When there is a need to change the database structure in the future, you should start by modifying the model first.

Code first allows you to first code your C# classes. Once you have all the classes manually coded, Entity Framework will use it to generate the database schema. This database schema can be used to create the database itself. When there is a need to change the database structure in the future, you do this by modifying the classes directly. With help of some tools, you can then create a new database schema as well as migration instructions for porting one database schema to another.

All three approaches each have their advantages and disadvantages. The Database first is the oldest option available. The Code first is the most modern and basically tries to completely “outsource” the database management to the developer.

In our examples, we will focus on the Database first approach.

Preparing a project

Before we can use Entity Framework in our project, we need to get its DLLs first – they are not part of the .NET Framework itself.

We could of course go ahead, download the required DLLs manually from the web and configure our project to use them. Everything would work as expected. There is however an easier way.

Even though the .NET Framework already includes many useful DLLs as a part of its Base Class Libraries, there are many other libraries that developers like to use. It would not be practical to bundle all of these libraries within the .NET Framework itself. First, its size would grow enormously and second, new framework versions would need to be released more frequently – just to make sure new updates to these bundled libraries get distributed. Another system had to be used for such DLL distribution – the NuGet.

NuGet is a public repository for various libraries for the .NET Framework and is closely integrated with Visual Studio. All libraries are distributed through NuGet in form of NuGet packages. These packages usually contain not only the DLLs required but they also know how to modify your Visual Studio projects to use them properly. Entity Framework is one of many packages you can get through NuGet.

Alright! Now that we know what NuGet is, let us create a new Console application and load it up with the .NET Framework NuGet package.

Once you have your Console application created, right-click your project in Solution Explorer, and select “Manage NuGet packages”. In the new window, make sure you are switched to “Online” packages and type “Entity Framework” to the search window. Wait a while for the available packages to show up:

Entity Framework NuGet package

Click “Install” on the “EntityFramework” package, hit “I Accept” in a “License Acceptance” window and the package should get installed in your project.

Two DLL references have automatically been added. “EntityFramework” is the core assembly for Entity Framework, “EntityFramework.SqlServer” is an assembly with specific instructions on how Entity Framework can talk to Microsoft SQL Server. If you use different database server, you will need to get a different assembly. You can also write your own implementations.

The NuGet package also updated your App.config file. This is a configuration file for your application. Two modifications have been made:

<configSections>
  <section name="entityFramework"
            type="System.Data.Entity.Internal.ConfigFile..."
            requirePermission="false" />
</configSections>
<entityFramework>
  <defaultConnectionFactory
    type="System.Data.Entity.Infrastructure.SqlConnectionFactory, ..." />
  <providers>
    <provider invariantName="System.Data.SqlClient"
              type="System.Data.Entity..., EntityFramework.SqlServer" />
  </providers>
</entityFramework>

The first addition merely introduces a new section called “entityFramework” that can be added to the App.config file. The second addition actually adds this new section. In this section you can see that Entity Framework will use the “EntityFramework.SqlServer” assembly as its provider. This means that it will talk to Microsoft SQL server. If you were using a different database system, you would need to change this.

Generating model and code from the database

Now that we have our console application ready, we can go ahead and have the database model and all the C# classes generated through the Database first approach. We will use our MyCSharpDotNet database hosted on Azure.

It is better to place the new model and all of the C# classes in a new folder. Let us create a new folder by right-clicking the project in Solution Explorer and selecting “Add” > “New Folder”. We will name the folder “Models” for example.

To create a new model in the Models folder, right-click the folder and select “Add” > “New Item”. In the “Add New Item” dialog, select “Data” on the left, “ADO.NET Entity Data Model” on the right and give it a descriptive name below. We will use “MyCSharpDotNetModel” as the model name:

Adding ADO.NET Entity Data Model

Click “Add” and select “EF Designer from database” option in the next window. This will tell the wizard that we want to use the Database first approach:

Entity Data Model Wizard 1

Click “Next”. In the new window, click “New Connection” button to display the “Connection Properties” window. Enter the database connection information. The window should look as follows:

Connection Properties

Click “Test Connection” to see that everything is ok and the click the “OK” button. This will populate the wizard window with a required connection string:

Entity Data Model Wizard 2

For the purpose of this example, select the “Yes, include the sensitive data in the connection string.” option and click “Next”.

In the following page of the wizard choose that you want to fill the model with all the tables from the database and that you would like to pluralize and singularize object names. This means that for the table Orders (plural), for example, an Order (singular) object will be created:

Entity Data Model Wizard 3

Click “Finish” and wait for the wizard to create your model and all the required C# classes.

Once everything gets created, the newly created model will open in a Model Browser. Here you can see all the tables, their columns, relations and if you delve deeper, you can even find how everything was automatically mapped.

Model

You can see all the automatically generated C# classes in the Solution Explorer. The generation itself uses special “*.tt” template files. You can modify these files to influence the content of the automatically generated files if needed.

Classes

The last thing the “ADO.NET Entity Data Model” wizard did was modifying the App.config file. A new section, containing the connection string, has been added:

<connectionStrings>
  <add name="MyCSharpDotNetEntities"
        connectionString="metadata=res://*/Models.MyCSharpDotNetModel..."
        providerName="System.Data.EntityClient" />
</connectionStrings>

Creating DbContext instance

All interaction with the Entity Framework happens through a DbContext class instance. Its new subclass, “MyCSharpDotNetEntities” class, has already been created for us.

To work with the “MyCSharpDotNetEntities“, make sure you use the “using” statement. Just like the ADO.NET SqlConnection object, the instance of DbContext holds quite a few things in memory. The “using” statement makes sure the memory gets released as soon as the code execution leaves the “using” block:

using (MyCSharpDotNetEntities context = new MyCSharpDotNetEntities())
{ 
    ...
}

The constructor takes no parameters and uses the connection string defined in the App.config file by default.

Getting the data

The following examples use LINQ to Entities, specifically the LINQ Method syntax. Anything you write using this syntax will automatically get translated into SQL queries by the Entity Framework.

The example below gets a list of all orders in the database and writes them out to the console:

IQueryable<Order> ordersQueryable = context.Orders;

List<Order> orders = ordersQueryable.ToList();

foreach (Order order in orders)
{
    Console.WriteLine(
        "Order: {0}, customer {1}",
        order.OrderID,
        order.CustomerID);
}

Console.ReadLine();

Notice that first we get an IQueryable object. This object merely contains what we want to get from the database – in our case, it is all the orders. The object itself does not contain any data however. The database is not queried until the second line where the ToList() is called.

Calling ToList() on the IQueryable object is just one way of querying the database. Enumerating the IQueryable object in a foreach cycle is another way:

IQueryable<Order> ordersQueryable = context.Orders;

foreach (Order order in ordersQueryable)
{
    Console.WriteLine(
        "Order: {0}, customer {1}",
        order.OrderID,
        order.CustomerID);
}

Console.ReadLine();

Once the database is queried, all the data returned is kept in memory by the context object. If you were for example to do another foreach right after the first one, the data would come from the memory. No other SQL statement will hit the database.

Depending on the amount of data being kept by the context object, subsequent operations might be faster as they are performed in memory. They can however also become much slower. If too much data is kept in memory, the client computer can make otherwise simple operations take too long. The database was not meant to be in the client computer memory – to limit the amount of data in memory, never keep your context object living for too long.

To limit the number of rows returned, we can use the following LINQ Method syntax:

IQueryable<Order> ordersQueryable =
    context.Orders
        .Where(x => x.CustomerID == "SUPRD")
        .OrderBy(x => x.OrderID);

foreach (Order order in ordersQueryable)
{
    Console.WriteLine(
        "Order: {0}, customer {1}",
        order.OrderID,
        order.CustomerID);
}

Console.ReadLine();

The example above applies the WHERE and ORDER BY clauses to the SQL query. Other operations (clauses) can be easily added by using yet another “dot” at the end. This provides an easy way of chaining all the method calls together.

Again, at the time when we are working with the IQueryable, we are basically just building the SQL statement. The execution itself happens later – when the object is enumerated by the foreach cycle for example.

So far we have always expected a list of entities to be returned. To get a single object, a Single method can be used:

Order order =
    context.Orders
        .Where(x => x.OrderID == 11035)
        .Single();

Console.WriteLine(
    "Order: {0}, customer {1}",
    order.OrderID,
    order.CustomerID);

Console.ReadLine();

Single is not the only method that allows for a single result to be returned. There are more methods. These four are the most frequently used:

  • Single: If the query contains zero or more than one result, an exception will be thrown.
  • SingleOrDefault: If the query contains zero results a default value will be returned (mostly NULL). If the query contains more than one result, an exception will be thrown.
  • First: If the query contains zero results, an exception will be thrown. If the query contains more than one result, the first one will be returned.
  • FirstOrDefault: If the query contains zero results a default value will be returned (mostly NULL). If the query contains more than one result, the first one will be returned.

In the previous example, the condition is defined in the Where method. Single, as well as other methods discussed above, can also take this condition as a parameter. This parameter is also known as a “predicate”. The following example is therefore functionally identical to the previous one:

Order order =
    context.Orders
        .Single(x => x.OrderID == 11035);

Console.WriteLine(
    "Order: {0}, customer {1}",
    order.OrderID,
    order.CustomerID);

Console.ReadLine();

All the examples above always return all of the columns for the given entity – even if we in the end use less. You can limit the number of columns returned by using the Select method. When you use it, you will not get the same object type though. You will get an anonymous type and you need to use var for its declaration:

var smallOrders =
    context.Orders
        .Where(x => x.CustomerID == "SUPRD")
        .Select(x => new
        {
            OrderID = x.OrderID,
            CustomerID = x.CustomerID
        });

foreach (var smallOrder in smallOrders)
{
    Console.WriteLine(
        "Order: {0}, customer {1}",
        smallOrder.OrderID,
        smallOrder.CustomerID);
}

Console.ReadLine();

You can also use aggregate expressions, like Sum, Average, Max, Min, Count or Any. The following example gets the Sum of Freight in orders from the “SUPRD” customer:

decimal? freightSum =
    context.Orders
        .Where(x => x.CustomerID == "SUPRD")
        .Sum(x => x.Freight);

Console.WriteLine(freightSum);

Console.ReadLine();

Notice that since we are expecting only one value to return, the output type of the whole query is that specific datatype – the nullable decimal.

For a more complete overview of LINQ Method take the LINQ Tutorial!

Saving data

The DbContext object allows you to change the existing objects as well as add new ones. All this is done in memory only.

The Entity Framework keeps a “tracking” information with every object. This way it know whether the object has been changed, deleted or whether it is in fact a new object and should be inserted into the database when the time comes.

Once you make all the changes to your DbContext object, you can get the changes persisted by using the SaveChanges method. This method makes Entity Framework process all your in-memory changes and compose adequate INSERT, UPDATE and DELETE SQL statements. These statements will then get “played” on the database in a single transaction.

Again, since the context always caches all the data and keeps all the changes in memory, it should be as short-lived as possible. Always use one context for one unit of work only!

Updating an entity

The following examples shows how to update an entity:

WritableRow writableRow =
    context.WritableRows
        .OrderByDescending(x => x.DateTime)
        .Single();

Console.WriteLine("State before modification");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

writableRow.Text = "First row (changed)";

Console.WriteLine("State after modification");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

context.SaveChanges();

Console.WriteLine("State after save");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

Console.ReadLine();

First, the entity is loaded from the database, then its property is changed and finally all changes are saved by calling the SaveChanges method. To better understand what happens behind the scenes, more code has been added. This code only outputs the current tracking information the context keeps for our object at different stages of its life.

From the output, you can see that the object is first tracked as unchanged, then modified and finally, after saving, unchanged again:

State before modification
Unchanged

State after modification
Modified

State after save
Unchanged

Inserting new entity

To add a new object, you can simply create it using its public constructor and add it to the collection of entities. The following example shows how to create a new WritableRow entity:

WritableRow writableRow = new WritableRow();
writableRow.WritableRowID = Guid.NewGuid();
writableRow.DateTime = DateTime.Now;
writableRow.Text = "Entity Framework test";
writableRow.Nickname = "Not me";

Console.WriteLine("State before adding");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

context.WritableRows.Add(writableRow);

Console.WriteLine("State after adding");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();
                
context.SaveChanges();

Console.WriteLine("State after save");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

Console.ReadLine();

Apart from adding the entity, the code again also shows the current states of the entity throughout the process. This is the output of the example:

State before adding
Detached

State after adding
Added

State after save
Unchanged

You can see that once the object is attached and saved, it is marked as unchanged again.

Deleting an entity

Deleting an entity is much similar to adding one. Instead of the Add method, use Remove. The following code removes the entity we added in the previous example:

WritableRow writableRow =
    context.WritableRows
        .OrderByDescending(x => x.DateTime)
        .First();

Console.WriteLine("State before removing");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

context.WritableRows.Remove(writableRow);

Console.WriteLine("State after removing");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

context.SaveChanges();

Console.WriteLine("State after save");
Console.WriteLine(context.Entry(writableRow).State);
Console.WriteLine();

Console.ReadLine();

The output again shows the individual states the object goes through:

State before removing
Unchanged

State after removing
Deleted

State after save
Detached

Once the object is removed and the context is saved to the database, the entity is marked as detached.

Sample application

There is an Entity Framework sample application available to show you most of the Entity Framework functionality discussed above. In fact, it is the same application as in the ADO.NET topic. You can see how much code we could just throw away thanks to what Entity Framework does for us right out of the box.

This is the last page of this topic.

Go up to: Databases


Should you have any questions or found a mistake that needs correcting, feel free to send an email to: info [at] mycsharp [dot] net


Advertisements :