Table of Contents

Introduction

In this article, first in a short series, we will see how to use Entity Framework in our applications, developed through the various languages that Visual Studio allows us to use. The examples in this article, and probably in the next, be made of C # use under WinForms, but - as mentioned - this choice is not going to affect a different intended use that the developer may reasonably request.

What is Entity Framework?

Entity Framework (hereafter, EF) is the framework ORM (object-relational mapping) that Microsoft makes available as part of the .NET development (version 3.5 SP1 and later). Its purpose is to abstract the ties to a relational database, in such a way that the developer can relate to database entity as to a set of objects, and then to classes and their properties. transparent are made the connection to the database phases, as well as the instructions that our application will be sent in the native language to the database itself. In essence, we speak of a decoupling between our applications and the logic of data access: this proves to be a major plus, for example, if we have the need to move - in the context of a single program - to different manufacturers database, as it would be required to review the way and the instructions with which we interface to the data manager on duty.

Entity Framework approaches

At present, EF mainly allows two types of approach related to its use. They are Database-First and Code-First (the first being absent from EF7, but still valid up to version 6.1.3). The difference between the two approaches is obvious from their name with Database-First, we find ourselves in a position where we have to model a pre-existing database (and therefore, to derive from it our objects), while in the Code-mode First, our classes will be - we will have to prepare by giving them the properties representing the table fields - to determine the structure of the database. An important point to note about this last observation, is that there does not necessarily Code-First obliges to work initially in the absence of the database: in fact, we can model the classes of an existing database, and connect to it to perform the usual operations of I / OR. We can say that the two approaches, beyond some instrumental peculiarity that we will see, represent a kind of index of priorities compared to those in power in determining the structure of the data with which the application will have to do "before the database" (from which they derived classes) or "before the" code (from which a database model can be textured).

Referencing Entity Framework in projects

Before you see a couple of examples of the use of EF, you must reference it in our project, that of making libraries accessible to the solution. then we create a new project in Visual Studio, choose the template that we need (in the example, as anticipated, will sort of C # WinForms). then save the solution (important to avoid alerts being added EF), and then we open manage Nuget 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. Leaving for subsequent appointments the different particularities of a specific use, then down a bit 'more technical detail, we see here how to carry out an initial database connection and modeling classes, following the two paradigms mentioned above.

Database-First

Let us first consider the Database-First mode, which - as we said - is mainly directed to existing database, from which to derive a model. We recall once again that, at least during the writing of this article, by EF7 onwards this mode has been suppressed in favor of Code-First, and is an important detail to keep in mind when you go to evaluate the analysis of a solution.

Preparing the database

Illustrative purposes, it created a database named TECHNET, inside which there are two tables: "Articles" and "Families". The T-SQL script that creates the entities, as well as we will see in the continuation, is as follows:

USE [TECHNET]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Articoli](
    [IdArticolo] [int] IDENTITY(1,1) NOT NULL,
    [CodArt] [nvarchar](25) NOT NULL,
    [DesArt] [nvarchar](50) NOT NULL,
    [CodFamiglia] [nvarchar](6) NOT NULL,
 CONSTRAINT [PK_Articoli] PRIMARY KEY CLUSTERED
(
    [IdArticolo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[Articoli] ADD  CONSTRAINT [DF_Articoli_CodArt]  DEFAULT ('') FOR [CodArt]
GO
 
ALTER TABLE [dbo].[Articoli] ADD  CONSTRAINT [DF_Articoli_DesArt]  DEFAULT ('') FOR [DesArt]
GO
 
ALTER TABLE [dbo].[Articoli] ADD  CONSTRAINT [DF_Articoli_CodFamiglia]  DEFAULT ('') FOR [CodFamiglia]
GO
 
CREATE TABLE [dbo].[Famiglie](
    [CodFamiglia] [nvarchar](6) NOT NULL,
    [DesFamiglia] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Famiglie] PRIMARY KEY CLUSTERED
(
    [CodFamiglia] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

After you create the tables, fill them with some data.

USE [TECHNET]
 
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART001', 'ARTICOLO TEST' , 'FAM01')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART002', 'PRODOTTO PROVA', 'FAM01')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART003', 'ART. 003', 'FAM02')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART004', 'ART. 004', 'FAM02')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART005', 'ART. 005', 'FAM02')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART006', 'ART. 006', 'FAM02')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART007', 'ART. 007', 'FAM03')
INSERT INTO Articoli(CodArt, DesArt, CodFamiglia) VALUES ('ART008', 'ART. 008', 'FAM04')
 
INSERT INTO Famiglie(CodFamiglia, DesFamiglia) VALUES ('FAM01', 'PROD. MECCANICI')
INSERT INTO Famiglie(CodFamiglia, DesFamiglia) VALUES ('FAM02', 'PROD. ELETTRONICI')
INSERT INTO Famiglie(CodFamiglia, DesFamiglia) VALUES ('FAM03', 'RICAMBI')
INSERT INTO Famiglie(CodFamiglia, DesFamiglia) VALUES ('FAM04', 'IMPORT')

You will notice the trivial link, not explicitly stated, between the two tables: CodFamiglia is the field that links the table Articles with the Families table to access the description of the latter. Run the script, we will have a few records to make sure, for example, the result of a data reading.

Modelling classes with EF

Let's see how to model, Visual Studio side, the classes to which we refer to our program to use these data entities.

We add a new element to the solution, going to retrieve, from the menu Visual C # Items »Date, an object of type ADO.NET Entity Data Model, which we will call "TechnetModello ".



Once you confirm your choice, Visual Studio will launch the Wizard dedicated to Entity Data Model, so that we can indicate what type of model used. Our choice, in this case, will be "EF Designer from database ', which realizes the Database-First paradigm.



Continuing, you will be asked to make a connection to our database. then press "New Connection" and we get to set the connection parameters. In the following image, an example to adapt to the real development environment.



Confirming yet, we will see the generated connection string, and we can save it in App.config, giving it a name, for future use.



Finally, we will be asked which entities arise in the model. In our case, we select the two tables created, leaving the rest of the settings as proposed.



The Wizard will complete the operations of the event, going to generate a data model consists of the representatives of the classes of database entities, and showing a graphical representation, on which we can act to create such explicit associations between tables or change some of the mentioned properties.

* Important: * following the Database-First paradigm, we have already mentioned how the database is to provide the framework modeling classes, and not vice versa. Therefore, if in this mode we will modify some property, and then we will find ourselves in the situation of having to regenerate the model, these changes - present only one side code - will be lost, overwritten from that stated on the database.



By browsing in the classes that the wizard has created for us, we note two of particular interest, namely those that - hierarchically - are daughters of TechnetModello.tt files, namely: Articoli.cs and Famiglie.cs.

Opening them, we will notice that follow the structure of the tables to which they refer, exposing the class name as that of the same table, and the fields of their counterpart, appropriately converted to the types used side code.



A simple query

We mention quickly to the way, in this context, we can make a reading of the data, to postpone further discussions on future articles.

Suppose you want to extract, from the table items, the record having Codart = 'ART001', and of wanting to emit a video description (field DESART). Since our data context has been transformed into classes, we can use the handy LINQ syntax, applied to the entities of the daughters TECHNETEntities data context.

The query just mentions it so trivially solved as follows:

using (TECHNETEntities db = new TECHNETEntities())
{
    Articoli art = db.Articoli.Where((x) => x.CodArt == "ART001").FirstOrDefault();
 
    MessageBox.Show(art.DesArt);
}

For example, a DB object is instantiated, the TECHNETEntities type, among which we find the properties referenced tables from EF, on which to run - for example - the common selection operations. Important to note that, in addition to the entity in itself, we have now linked to the types of tables: the art variable has been declared as a type of items, or possess all the properties of its class.

Then we perform the Where method on items class, using lambda function that selects the desired item number, returning the first object found by FirstOrDefault method (do not operate here validity checks of the data, which of course in a real context must be provided).

At this point, using the art variable, we can refer to its properties, and ask for the issuance of a MessageBox text having equal to DESART properties of art, we get the following output:



Already at this point, it is evident the huge potential of the Entity Framework, which for the developer translates into factors of primary importance, such as the considerable saving of time, and be able to dedicate their efforts to the solution itself, rather than to the layer intermediate between it and the database, now fully managed.

Relations between tables, and model consequences

In the model the database, we have determined that the two entities Articles and Families were linked together. In fact, we have not defined Foreign Keys that could have instructed EF how to tie the two tables - in fact - are therefore independent. In this condition, we will have to resort to more elaborate LINQ instructions, which implement the joins that are missing at the model level.

Suppose we want to select the item ART005, and we want to expose the screen is the product description than that of the same class. In the present conditions, we can make the request as follows:

using (TECHNETEntities db = new TECHNETEntities())
{
    var art = db.Articoli
                .Join(db.Famiglie,
                      articolo => articolo.CodFamiglia,
                      famiglia => famiglia.CodFamiglia,
                      (articolo, famiglia) => new { Articoli = articolo, Famiglie = famiglia})
                .Where((x) => x.Articoli.CodArt == "ART005")
                .FirstOrDefault();
 
    MessageBox.Show(art.Articoli.DesArt + " - " + art.Famiglie.DesFamiglia);
}

Instantiated as before the TECHNETEntities object, we declare an implicit type this time. It will contain the LINQ query result that will unfold on the following conditions: Selection of db.Articoli, performing a Join with db.Famiglie, on the basis of their respective CodFamiglia fields, and extracting from that joins a new object that contains the properties of both. On this object runs the Where method, accessing the Articles class and making the selection on Codart field, and then return the first element of the question.

Our art variable is now a composite type, and we can access respectively to the properties of the part and of the part items Families: note how in MessageBox the description of the article to be emitted, as well as that of the family. By running a test in the search for the product ART005, the displayed result is the following:



But if he had taken account of this constraint the design phase, by inserting the Foreign Keys where appropriate, EF could notice it, going to create this kind of report automatically. Then we perform a variation on the database structure, to see how this affects in modeling classes.

Using SQL Management Studio convenience, let's insert, items in the table, a Foreign Key that establishes the relationship between Articles and Families



Once you save the changes to the database, Visual Studio side we will have to regenerate the data model. TechnetModello.edmx will simply open the file, which graphically shows our data model, and using right click to select "Update Model from Database."



Followed by a brief wizard, where we'll just simply confirm the refresh.
So we will see that our model will be upgraded by establishing a relationship between the two tables, we discover - in the field of EF - be called "Navigation Properties."



This step is to modify our classes in an important way. Class Families acquires a new property, Articles, type ICollection <items>, while the Articles class now has a property Families, type Families, declared virtual.



This means that in our data model, the two entities will naturally be related to one another. In this case, for example, if we want to re-run the query first, item description and description of the family, we will not directly instruct the compiler on how the tables are related, but we can refer to the property arranged by EF.

Translated into practice, the latest example becomes:

using (TECHNETEntities db = new TECHNETEntities())
{
      Articoli art = db.Articoli.Where((x) => x.CodArt == "ART005").FirstOrDefault();
 
      MessageBox.Show(art.DesArt + " - " + art.Famiglie.DesFamiglia);
}

In other words, the items class already contains the property Families, derived from the database. As a result, we can query directly Articles going to select the properties you want in it, knowing that it will find the property Families, which will solve the Join existing independently between the two entities, and to which we can access directly from the variable type items.

Code First

In this section, we will see a mode of Code-First usable in the case of this database, leaving for a next article the approach that goes to define the database to zero (or almost). The project that you find in the Download section refers to this second mode, considering fact that the Code-First (regardless of whether they come from a predefined template, or will define completely) acts with priority than the basic physical data, and the reader It can then generate it on its own instance of SQL Server. For added convenience, it created a folder named Model, which will contain the classes initially created by EF, but we can vary to change the model.

Then add back an object of type ADO.NET Entity Data Model, which we will call "TechnetModelloCF". The steps to follow are similar to those already seen previously, with the exception of the mode to be selected, which in this case will be "Code-First from Database". We continue then the remaining steps as you did in the case of Database-First.



Class definition

As we shall see, there are graphical representations of entities and their relationships, and the generated classes starting from our charts will be significantly different. Let's see in detail, starting from a class that in the case of Database-First was not as immediately visible.

namespace ArticoloEF.Model
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
 
    public partial class TechnetModelloCF : DbContext
    {
        public TechnetModelloCF()
            : base("name=TechnetModelloCF")
        {
        }
 
        public virtual DbSet<Articoli> Articoli { get; set; }
        public virtual DbSet<Famiglie> Famiglie { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Famiglie>()
                .HasMany(e => e.Articoli)
                .WithRequired(e => e.Famiglie)
                .WillCascadeOnDelete(false);
        }
    }
}

This is the TechnetModelloCF class, the class with the name that we assigned to the model, and going to inherit their type and functionality from DbContext class of EF. The task of this class is primarily to make available the database entities, but also to establish a generation of particular parameters of the database, as well as to explain if required, the relationships between the various objects.

In our case, we note first of all there is a constructor, in which the "name = TechnetModelloCF" parameter is passed. The manufacturer will seek to establish the connection from the database when required, and this will be through the passed parameter, which can be directly ConnectionString or, as in the example, a name that refers to a ConnectionString defined in the App.config file.

Next, they define the extent to which you will have access (our tables), which will be of DbSet type objects, defined on the basis of their respective classes. They are also declared virtual, in order to allow physical access to stored data. Finally, in what is overriding the OnModelCreating method (in which define the model creation rules) it is made explicit the relationship between the two entities. More specifically, it is indicated in ModelBuilder how this entity Family, which is bound to items in a many-to-one relationship, and that does not perform erase operations in cascade if the main table is deleted references to that secondary.

Let's see the other two classes. Let's start with Articoli.cs:

namespace ArticoloEF.Model
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;
 
    [Table("Articoli")]
    public partial class Articoli
    {
        [Key]
        public int IdArticolo { get; set; }
 
        [Required]
        [StringLength(25)]
        public string CodArt { get; set; }
 
        [Required]
        [StringLength(50)]
        public string DesArt { get; set; }
 
        [Required]
        [StringLength(6)]
        public string CodFamiglia { get; set; }
 
        public virtual Famiglie Famiglie { get; set; }
    }
}

In fact, there is not much difference compared to that generated in Database-First, except for some indications, specified in brackets, preceding the various fields. It is called DataAnnotations, elements very useful and concise that allow great flexibility in declaring particular aspects of the data that we will create. This is not mandatory instructions, but at the same time may be needed to model more accurately our entities.

In the example, the class known as there is a record Table declared before the class. It specifies, database, what is the name of the table to reference. If not specified, the table will assume the name of the class (and is certainly the most normal behavior, but there may be cases in which it is necessary to opt for a name of a different class from that of the table). On the fields, we see three special conditions: Key, prefixes the field articleID, instruct the model because of the primary key of the table, in fact, the articleID. Required requires the mandatory field (translates the T-SQL NOT NULL education), while StringLength specifies the maximum length that the field in question should have.

With the necessary distinctions, the same type of notation is also used in Famiglie.cs class:

namespace ArticoloEF.Model
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;
 
    [Table("Famiglie")]
    public partial class Famiglie
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Famiglie()
        {
            Articoli = new HashSet<Articoli>();
        }
 
        [Key]
        [StringLength(6)]
        public string CodFamiglia { get; set; }
 
        [Required]
        [StringLength(50)]
        public string DesFamiglia { get; set; }
 
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Articoli> Articoli { get; set; }
    }
}

In this case, you will notice an additional class constructor, which initializes the property defined as ICollection <Articles> on the basis of the HashSet class, perform particularly well in performing operations on the list.

Class modify and migrations

Before you make a few brief example of data access through this mode, we might want to see - in fact since the Code-First context - what happens if we change the model.

Suppose, then, quite simply, want to add a field to the Items table. We insert a field named CostoStandard, Decimal type.



What happens if we try to run the application now? After a few seconds of waiting, it will be raised, which will warn us of the difference between our data model and what we are trying to access. In this case, the CostoStandard column is not to be a valid field, and in fact is not present in the database.



We introduce here the concept of migration: EF is able, from our classrooms, and using a database for comparison, to determine what changes you need to make to it, to make it compatible with the model set in the program. We start with preparing our project migration. This type of operation is carried out using the Package Manager Console (Tools »Nuget Package Manager" Package Manager Console), which makes available several PowerShell cmdlets, including those we're going to use.

To enable the project to migration, you must type:

Enable-Migrations



It is noted here as EF go to check if there is a database on which to perform a first comparison. The command will create a directory in the project named Migrations folder, which will initially contain only Configuration.cs file. This file is useful for example if we want to deploy the initial data assuming the case of construction from scratch of a database, or the introduction of a table containing frontloaded records, we can create them - a completed migration - through the Seed method, the subject of a future article.

Getting back on topic, at this point, our project is ready for migration, but does not yet contain any. Migrations can be created with the command:

Add-Migration <NOME_MIGRAZIONE>

where <NAME MIGRATION> is obviously a text typed by the developer, which identifies that particular migration release.



Here we requested the creation of a migration, from CostoStandardSuArticoli name. You will notice the creation of a class file in the folder Migration (whose class extends the type DbMigration), the name composed of the current timestamp plus the name that we typed. In the image to follow, you see the contents of the file created, we see a series of instructions designed to model the database as defined in our modified classes. Thus we see that in the Articles table creation instruction, is the new field CostoStandard.

It is good practice, before creating customized migrations, create an initial startup migrate through education:

Add-Migration InitialCreate –IgnoreChanges

This allows you to move fully into field Code-First control of the data modeling, which in the current environment we still derived from an existing database.



At this point you just have to launch the migration thus created, and observe the result. This is done through the instruction

Update-Database

The operation is finished, the display of the items table schema using Management Studio, created to see the new column.



Download

At the following link, you can download the Code-First project used in the example: https://code.msdn.microsoft.com/Introduction-to-Entity-09676091

To properly use the source code must be changed the instance of SQL Server access, as specified in the App.config file of the project, and that instance you will have to create a name TECHNET database.

Conclusion

In this first part, we have scratched the surface of the Entity Framework, trying to figure out what it is, how it can come to our aid, and what are the paradigms used in its use. We saw some trivial example of the use of objects created by the database and started to hint at migration mechanisms. We recommend the reader to become familiar with these basics, waiting to continue in this overview, with the next items in the series.

Other languages

The current article is also available in the following localizations: