Entity Framework using C#

Entity Framework in C#

In this article, we will see how to use Entity Framework in C# Applications using Visual Studio. The examples in this article and probably in the next, will be C # used 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 the database entity as to a set of objects and then to classes in addition to their properties. In essence, we speak about decoupling between our Applications and the logic of data access, which proves to be a major plus. For example: If we need to move - in the context of a single program - to different manufacturers database, it would be required to review the way and the instructions with which we interface the data manager on duty.

Entity Framework approaches

At present, EF mainly allows two types of approaches related to this 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 as 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, we will have to prepare by giving them the properties representing the table fields to determine the structure of the database. It is not necessary that Code-First is obliged to work initially in the absence of the database as 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, 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 data base model can be textured).

Referencing Entity Framework into projects

Before you see a couple of examples of the use of EF, you must reference it in our project, i.e, making libraries accessible to the solution. Then we will create a new project in Visual Studio, choose the template that is required (in the example, as anticipated, will sort C # WinForms). Subsequently, 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 it will simply add to our solution, using the "Install" button. When finished, we will see how one of the project references have been included to those related to EF.

 

We are now ready to use the potential of our ORM, as far as development is concerned. We will also see 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 the existing database, i.e 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 point to keep in mind, when you are going to evaluate the analysis of a solution.

Preparing database

For 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 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, where CodFamiglia is the field that links the table Articles with the Families table to access the description of the latter. Run the script and we will have few records.

Modelling classes with EF

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

We add a new element to the solution, and are going to retrieve, from the menu of 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 is used. Our choice, in this case, will be "EF Designer from database ', which realizes the Database-First paradigm.

You will be asked to make a connection to our database, press "New Connection" and we will subsequently set the connection parameters. In the following image, an example to adapt to the real development environment is depicted.

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 will 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. It is going to generate a data model that consists of the representatives of the classes of the 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 will 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 regenerating the model, these changes - present only on one sideof the  code - will be lost, overwritten to that stated on the database.

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

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

A simple query

Suppose you want to extract from the table items the record having Codart = 'ART001', and want to depict 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, as follows:

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

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 item possessing all the properties of its class.

We perform the Where method on items class, using lambda function that selects the desired item number, returns 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 then ask for the issuance of a MessageBox text having equal to DESART properties of art, we get the following output:

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

Relations between tables, and model consequences

In the model, 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, which are 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 in the product description  rather 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 .  The Where method that accesses the Articles class and makes the selection on Codart field runs on this object, and then returns 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 item's Families: note how in MessageBox there is 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:

If we had taken account of this constraint in the design phase, by inserting the Foreign Keys where appropriate, EF would notice it and would be going to create this kind of report automatically. Then we perform a variation on the database structure to see how this affects things 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 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", the screenshot shown below, appears:

Followed by a brief wizard, where we'll just simply confirm the refresh.

Hence, we will see that our model will be upgraded by establishing a relationship between the two tables.

This step is to modify our classes in an important way. Class Families acquire 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 each other. In this case, if for example, 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 in 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 directly query Articles and subsequently select the properties you want in it, knowing that it will find the property Families, which will solve the Join existing between the independent tables, which can be accessed 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 data base to zero (or almost). The project that you find in the download section refers to this second mode. Considering the fact, that the Code-First (regardless of whether they come from a predefined template, or will define completely) acts with more 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 make changes to the model.

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 the remaining steps, as you did in the case of Database-First.

Class definition

As we shall see, there are graphical representations of the entities and their relationships, the generated classes starting from our charts will be significantly different. Let's see in detail, starting from a class; what in the case of Database-First was not 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 is 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 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 an 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 store the data. Finally, in what is overriding the OnModelCreating method (in which is defined 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 tables are deleted references to that secondary one.

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 a mandatory instruction, but at the same time may be needed to model more accurately our entities.

In the example, the class known is a record Table, declared before the class. It specifies the database; i.e, 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, instructs the model because of the primary key of the table. In fact articleID requires the mandatory field (translates the T-SQL NOT NULL education), while StringLength specifies the maximum length.

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, that performs operations on the list.

Class modify and migrations

Suppose we 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 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 uses 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 migrate, you must type:

Enable-Migrations  

It is noted here that  EF is going 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 the 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.

Migrations can be created with the command:

Add-Migration <NOME_MIGRAZIONE>

Where <NAME MIGRATION> is a text typed by the developer, which identifies that particular migration release, the output depicted is as follows:

Here, I have 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 and the name that we typed. In the screenshot 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 a good practice, before creating customized migrations and creating 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 create and observe the result.

This is done through the instruction  

Update-Database

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

Download

At the following link, you can download the Code-First project, used in the example.

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 in that instance you will have to create a name for TECHNET database.

Next article > Entity Framework Code-first and Migrations: Part two 

Conclusion

In this first part, we have scratched the surface of the entity framework, tried to figure out what it is, how it can come to our aid and what are the paradigms used in its use. We saw example of the use of objects created by the database and started to hint at migration mechanisms. We recommend the reader to be 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:

• Introduzione ad Entity Framework con C#, parte I (it-IT)

If you're working with .NET Core, check out Introduction to EF Core