Entity Framework Code First And Migrations: Part Two

If you are new to this series, kindly acquaint yourselves with part one at:

• Entity Framework Introduction with C# - Part One

Introduction

In this second article, we will analyze the use of Entity Framework in relation to the Code-First paradigm, which -- as we saw earlier -- does not need an already-existing database schema. Rather than allowing it to operate at the data level, we start from the definition of the objects/classes that will characterize the entities. Again, it is made of C# used under WinForms, but -- as mentioned in the previous article -- this choice will not affect a different intended use than that which the developer may reasonably request.

Why Code First?

Having evaluated the practical approach of Database First, one wonders, what are the reasons that should lead us to the Code-First paradigm? On the other hand, you can design the database, working directly on the SQL Server (for example) with our scheme, entities, keys and relationships and then delegating the practical Database-First derivation of the classes and their interconnections, while taking the advantage of the GUI that EDMX models provide.

In fact, there are several reasons why Code-First is an appropriate choice. First, it proves to be the only feasible one in the case in which the base data does not exist, but must be created by the application. In this case, the classes will be defined by the developer to represent the template which helps to create the database and its entities. Code-First is optimally an interface with the existing database, and also where the basic data should be accessible, only in part. Code-First allows the declaration of the classes that contain only what we actually need. We have more control on the same database structure, which will be determined by the written code. If you need to apply the changes to the database, we have useful migration tools, redistributable with the Application. Moreover, the code in use is actually  written once, avoiding the over-bloating that the Database-First approach, by its inherent structure, in a certain sense imposes, with its numerous files and the total derivation of the referenced tables.

Although the present article deals with EF 6.1.3, the version 7.0 Code-First becomes the only usable paradigm and therefore, it involves a major advantage for the future.

Database creation

In this section, we will see how to create a database containing several tables, using only the classes we're going to write. Before that, we summarize briefly about referencing Entity Framework in our project, as was done in the previous article, "Introduction to the Entity Framework with C #, Part I (en-US)", to which we refer the reader to the preliminary information necessary to this second part.

Referencing Entity Framework into the project

To reference EF within our project, I.e., make its libraries accessible to the solution, you must first create a new project in Visual Studio, choose the template that we need (in the example, as anticipated, with C # WinForms) and then save the solution (important to avoid alerts being added to EF) and open manage NugGet packages.

In it, we will package the Entity Framework and simply will add to our solution, using the "Install" button. When finished, we will see how one of the project References have been included -- those related to EF.

We are now ready to use the potential of our ORM, regarding the development.

Development setup

As discussed in the previous article, you will need to add an ADO.NET data entity to the project. Click the solution and choose the "New Item" Add voice. We will choose the section "Date," an object of the type ADO.NET Entity Data Model, which we will call again "TechnetModello".

The creation wizard will continue and this time, we will select as a content model, a blank template prepared for Code-First.

Click the "Finish" button, a class will be created in the project named TechnetModello, extending the type DbContext. This is the basic type, that will allow us to create an initial database (or instantiate the connection to an existing database), allowing the definition of the classes / entities that will be translated into the tables used by the database itself.

Connection string setting

Opening the class Technet Model, we will see it has the following appearance:

public TechnetModello() : base("name=TechnetModello")
{ }

This is supplied to DbContext below, in  a string in the form "name = TechnetModello". This syntax refers to the connection string to be used as the class is referenced. In place of such a string that can be passed, for example, an entire ODBC connection string is formed by UDL. If you leave it in the format "name = ...", the name of the string to be found in the App.config file is where we will find a section called connectionStrings. It can be differentiated by different connection strings, each having a different name. In our case, "name = TechnetModello" hints that in App.config, there is a named TechnetModello connection string.

Checking the contents of the file, the following snapshot appears:

<connectionStrings>
<add name="TechnetModello" connectionString="data source=(LocalDb)\v11.0;initial catalog=ArticoloEF02.TechnetModello;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

An instance of SQL Server already exists, or changes the database name you created, which is defined in the connection string by the Initial Catalog property and can be applied in this string. LocalDB is a version of SQL Server Express, specifically created for the developers. It is typically installed with Visual Studio and has many features of the usual versions of SQL Server and allows the programmer to be able to avoid the installation of other instances, and is dedicated to the development machines. In this case, we will use this database engine, limiting ourselves to changing the database name, which will become TECHNET. The connection string in the App.config  is required to use the following code:

<connectionStrings>
<add name="TechnetModello" connectionString="data source=(LocalDb)\v11.0;initial catalog=TECHNET;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

At this point, you will need to create the first migration, which allows Code-First contexts to upgrade the physical data model, based on the predisposed classes.

Startup migration

Enabled for the project migrations, running from Package Manager Console, education is as follows:

Enable-Migrations

 Thus, we created the initial migration, with the command given below:

Add-Migration InitialCreate –IgnoreChanges

At this point, our project will be enriched by the Migrations directory, which will contain a configuration file, called Configuration.cs and from the first migration file, we are now in a position to perform.

We proceed further by typing the Package Manager Console Update-Database education and we wait until the processing is completed.

Once completed, we will be informed of the migration, performed on the database.

To check what happened on the Server side, we can connect to the instance of LocalDB, for example, via SQL Management Studio: We observe how the desired database has been created.

The operations related to the migration will be repeated in all those cases in which you need to make the changes to the database structure itself. According to Code-First paradigm, it will be our code to make the changes in the model and not vice versa. We will see a second example of the migration soon, once we have created some significant entities.

Creating entity classes

Now we have a database. Suppose we want to create the two table in it. They correspond, respectively, to an entity that will serve us to store a trivial identifier of the products, while the second will contain some detailed information about the commodity families of the products available. We can operate in the two modes, namely to create a new class file or append our classes to what is already contained in TechnetModello.cs file. For the practical questions, here we will opt for the second way, in order to make it a more maintainable code - in the real Applications; it makes perfect sense that each class has its own characteristic file.

Articoli class

We define a class named "Articoli". As we saw earlier, the class will contain the properties that the database corresponds to as many fields. Let us then suppose, you want to create a very simple class, consisting of a product code, a description and a family code. Therefore, we can write the code, as shown below:

public class Articoli
{
    public string Codice
  {
        get;
        set;
    }
    public string Descrizione
    {
        get;
        set;
    }
    public string CodFamiglia
    {
        get;
        set;
    }
}

We mentioned in the last article, the so-called Data Annotations or the large synthetic instructions, that help in defining the special properties of our fields. To map the structure of the classes in Code-First syntax, they are important, particularly because to the developer, it broadens the definition of the property by specifying additional characteristics of the field, such as its length.

We use then the Data Annotations to delineate the profile of our articles table.

In the following example, we put the key constraint on only the Code field, thereby setting the obligation and by Data Annotation StringLength, that defines a maximum length for each field.

public class Articoli
{
    [Key]
    [StringLength(15)]
    [Required]
    public string Codice
    {
        get;
        set;
    }
    [StringLength(50)]
    public string Descrizione
    {
        get;
        set;
    }
    [StringLength(6)]
    public string CodFamiglia
    {
        get;
        set;
    }
}

Families class

The Families class will contain only two fields: Code family, of course, it's a key and description representative of the long description of the family.

public class Famiglie
{
    [Key]
    [StringLength(6)]
    [Required]
    public string CodFamiglia
    {
        get;
        set;
    }
    [StringLength(35)]
    public string Descrizione
    {
        get;
        set;
    }
}

TechnetModello class

 The change is made as follows:

public class TechnetModello: DbContext
{
    public TechnetModello(): base("name=TechnetModello") {}
    public virtual DbSet < Articoli > Articoli
    {
        get;
        set;
    }
    public virtual DbSet < Famiglie > Famiglie
    {
        get;
        set;
    }
}

Second migration

With the new model, ready for use, we can now proceed with the publication of the second migration, this time, with the task of creating the tables in the database.

From the Package Manager Console, we perform the instruction gven below:

Add-Migration Tabelle_di_base

We perform as follows:

Update-Database

By connecting to the instance of LocalDB, we will see how our tables have been added, in which the final character class name in English was added designates the plural.

This peculiarity, a bit annoying in our language, can be circumvented by a special DataAnnotation, which we will analyze in the context of the upcoming migration.

Initial data in migration context

It may happen that, after the release of the model updates, you want to provide the preset table data (think, for example, a ministerial table, to be provided as they are and above all ready for the operation). The migrations allow us to distribute the precompiled data, exploiting the Seed () method, that is located in Configuration.cs files. This method is executed subsequent to the migration and is suitable to perform the various operations on the database.

Following our example, we will want to migrate our application people table's Articles and Families with some data, which will be used for the testing.

The Seed method will thus become as shown below:

protected override void Seed(ArticoloEF02.TechnetModello context)
{
    context.Articoli.AddOrUpdate(new Articoli
    {
        Codice = "Test001", Descrizione = "Articolo di Test 001", CodFamiglia = "F01"
    }, new Articoli {
        Codice = "Test002", Descrizione = "Articolo di Test 002", CodFamiglia = "F01"
    }, new Articoli {
        Codice = "Test003", Descrizione = "Articolo di Test 003", CodFamiglia = "F03"
    }, new Articoli {
        Codice = "Test004", Descrizione = "Articolo di Test 004", CodFamiglia = "F02"
    }, new Articoli {
        Codice = "Test005", Descrizione = "Articolo di Test 005", CodFamiglia = "F02"
    }, new Articoli {
        Codice = "Test006", Descrizione = "Articolo di Test 006", CodFamiglia = "F01"
    });
    context.Famiglie.AddOrUpdate(new Famiglie
    {
        CodFamiglia = "F01", Descrizione = "Prodotti Finiti"
    }, new Famiglie {
        CodFamiglia = "F02", Descrizione = "Semilavorati"
    }, new Famiglie {
        CodFamiglia = "F03", Descrizione = "Materie Prime"
    });
}

Notice how it uses, as a parameter, a context variable TechnetModello type: This will then allow you to refer directly to our data entities, calling  on the classes representing the tables. These methods allow you to change the underlying data. In our case, we add six items and three families.

We can now use the command again, shown below:

Update-Database

There are pending migrations, but at the end of this command, Seed () method will always be executed. Therefore, going back to query our database and ask a simple SELECT from the tables created, we get the following result:

Data access

In this section, we will see some simple examples to make access to the data in the context of selecting insert, update and delete.

For all these cases, it is necessary to have a data context properly initialized. In our case, this is achieved with the simple definition of a new reference to a variable, which belongs to the class that extends DbContext, namely:

TechnetModello db = new TechnetModello();

More generally, if we want to initialize a data context and delegate the task of having this connection upon completion of the operations you want, we can use the using clause, as shown below:

using(TechnetModello db = new TechnetModello())
{
    // TO DO: operazioni di lettura/scrittura
}

Data querying

Data querying refers to a simple routine to make the cyclic reading.

As it appears to be clear from the premises, our tables can be thought of as the lists belonging to a given type, which is precompiled (existing data in the table) and editable.

Consequently, once it initialized the data context, the entity items can be scrolled as a normal custom.

using(TechnetModello db = new TechnetModello())
{
    foreach(Articoli a in db.Articoli)
    {
        MessageBox.Show(a.Codice + " " + a.Descrizione);
    }
}

The snippet above initializes the data context on the db variable. Within a for / each loop, we read the individual entities of the type contained in Articles db.Articoli list, on the code description screen. In these cases, the LINQ syntax is obviously very useful to run the queries or to target the selections.

For example, suppose you want to extract a type of Articles variable representing the first occurrence of the items that the family code of F02 has.

With LINQ syntax, we can write concisely the following snippet:

using(TechnetModello db = new TechnetModello())
{
    Articoli a = db.Articoli.Where((x) => x.CodFamiglia == "F02").FirstOrDefault();
    if (a != null) MessageBox.Show(a.Codice);
}

New record insert

The operations of insertion pass - of course - for the referencing of a type compatible with that of the variable list, append it to the latter, once the properties are enhanced. A new type of Articles variable can be declared as follows:

Articoli a = new Articoli() { Codice = "PROVA", Descrizione = "Articolo inserito da codice", CodFamiglia = "" };

Obviously, the DbSet items has an Add () method, which will allow us to pass this variable to the set to add to it. No other controls, however, will expose us to the risk that entered the duplicate keys in the database. Therefore, it becomes necessary, before inserting our variable, to check if its key elements are not already specified.

With LINQ, it all becomes very simple, as follows:

Articoli a = new Articoli()
{
    Codice = "PROVA", Descrizione = "Articolo inserito da codice", CodFamiglia = ""
};
if (db.Articoli.Find(a.Codice) != null) MessageBox.Show("Articolo già presente su tabella Articoli");
else db.Articoli.Add(a);

We defined our type of Articles variable. Let's run the Find () method on the table DbSet representative, looking at any occurrences of a.Codice, or the primary key of the table. In case you find an existing item, we will issue a notice of an inability to proceed, but not to add the element.

However, in the data modification operations, simply do not act on the entities: it is necessary to consolidate the changes or to inform the Entity Framework required to save the changes that occurred during the use of DbSet. DbContext has a method called SaveChanges (),  which precisely performs this consolidation. It also has a property to be used for the control purposes with the changes that occurred on the database. You can then, before performing the save routine, introduce a preliminary check to make sure that the procedure, shown below, is actually necessary:

using(TechnetModello db = new TechnetModello())
{
    Articoli a = new Articoli()
    {
        Codice = "PROVA", Descrizione = "Articolo inserito da codice", CodFamiglia = ""
    };
    if (db.Articoli.Find(a.Codice) != null) MessageBox.Show("Articolo già presente su tabella Articoli");
    else db.Articoli.Add(a);
    if (db.ChangeTracker.HasChanges()) db.SaveChanges();
}

Since the snippet shown above may not produce variations in DbSet (in case of which a duplicate key is detected), we use the ChangeTracker properties and its HasChanges method to check whether the modifications on DbContext are available or not. If so, you run the SaveChanges method and consolidate current data.

Updating existent records

At this point, the update operations and delete data become obvious. Suppose we want to change the newly inserted record or having a key field code = TEST.

The first thing to do is to locate the DbSet items and then - if it is; we can change its properties. This operation will update at the same time the ChangeTracker, by which we will determine whether to perform a data consolidation or not.

Very simply, the code will be as follows:

using(TechnetModello db = new TechnetModello())
{
    Articoli a = db.Articoli.Find("PROVA");
    if (a != null)
    {
        a.CodFamiglia = "F03";
    }
    if (db.ChangeTracker.HasChanges()) db.SaveChanges();
}

In this case, we want to update for the single record, the Family Code, bringing it to F03. Note, that it is sufficient to have the corresponding property setting, when you have found the record. This can of course be performed for all the desired items. The SaveChanges () method must be called only once, in the end for the processing.

Record deletion

Even deleting a record is simple: it is to remove a particular item from DbSet items and re-consolidate the change.

In the following snippet, the research article has a key field code = TEST is performed and its subsequent removal, if found:

using(TechnetModello db = new TechnetModello())

{
    Articoli a = db.Articoli.Find("PROVA");
    if (a != null) db.Articoli.Remove(a);
    if (db.ChangeTracker.HasChanges()) db.SaveChanges();
}

Table name modification

As a final aspect of this article, we will see how to change the names of the physical tables through Data Annotations.

Additional considerations about it will be made in the future; when we will discuss more closely the so-called Fluent API.

We have seen how, by default, the Entity Framework generates tables giving them a character and queue name, which defines the plural in English. In Italian, we have already declined the names in the plural class, we want to avoid the tables that are created with a different nomenclature than you expect. Enough will precede the declaration of the classes and the items in the Families Data Annotation of the Table, indicating the name that the tables will have to hire.

The TechnetModello files therefore will appear, as shown below:

using System;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Data.Entity;

using System.Linq;

public class TechnetModello: DbContext

{

    public TechnetModello(): base("name=TechnetModello") {}

    public virtual DbSet < Articoli > Articoli {
        get;
        set;
    }

    public virtual DbSet < Famiglie > Famiglie {
        get;
        set;
    }

}

[Table("Articoli")]

public class Articoli

{

    [Key]

    [StringLength(15)]

    [Required]

    public string Codice {
        get;
        set;
    }

    [StringLength(50)]

    public string Descrizione {
        get;
        set;
    }

    [StringLength(6)]

    public string CodFamiglia {
        get;
        set;
    }

}

[Table("Famiglie")]

public class Famiglie

{

    [Key]

    [StringLength(6)]

    [Required]

    public string CodFamiglia {
        get;
        set;
    }

    [StringLength(35)]

    public string Descrizione {
        get;
        set;
    }

}

At this point, we can generate a new migration, shown blow:

Add-Migration NomiTabelle

and subsequently run it

Update-Database

After the migration, we are going to query the database TECHNET  and we will see how the name of the tables have changed, reflecting more hours than desired.

Download

The source code used in the examples, mentioned above, can be downloaded.

Conclusion

Here we've briefly outlined the methodologies concerning the Code-First paradigm of Entity Framework, showing some examples of use of the created entities, and focusing on data modeling possibilities underlying the context. We recommend the reader to become familiar with these concepts, together with those of the previous article, and waiting to continue this overview with the upcoming events of the series.

Other languages

The present article is available in the following localizations:

• [it-IT] Introduzione ad Entity Framework con C#, parte II: Code-First e Migrazioni (it-IT)

• Entity Framework Introduction with C#, part II - Code-First and Migrations