web hosting

Entity Framework

What is Entity Framework?

Entity Framework is the framework ORM (object-relational mapping) that Microsoft makes available as part of the .NET (3.5 SP1 and later)development. The purpose of the Entity Framework 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. Basically, we talk about decoupling between our Applications and the logic of data access, which ends up being significant in addition to. For instance: If we need to move - with regards to a single program - to various manufacturers' databases, it is needed to survey the way and the guidelines with which we interface the data manager on the job.

Entity Framework approaches

As of now, Ef mostly permits two kinds of approaches related to this use. They are Database-first and Code-first(the first being missing from EF7, yet valid up to version 6.1.3.). The difference between these two methodologies is clear from their name likewise with Database-First, we end up in a position, where we need to slow a prior database, while in the Code-mode First, we have to prepare by giving them the properties addressing the table fields to decide the structure of the database. It isn't required that Code-First is obliged to work at first without a database as we can display the classes of the current database and connect to it to perform the typical task of I/OR. We can say that two approaches, beyond some instrumental eccentricity, represent a kind of index of priorities compared to those in power in determining the structure of the data, with which the Application should do "before the database" ( from which they determined classes) or "before the" code (from which a database model can be finished).

Referring to Entity Framework into projects

Before you see several instances of the utilization of EF, you should reference it in our project, i.e, making libraries open to the solution. Then, we will make another project in Visual Studio, please pick the format that is needed (in the model, as expected, will sort C # WinForms). Thusly, save the arrangement, and afterward, we open manage Nuget packages.

we will package the entity framework and it will just add to our solution, utilizing the "install" button. At the point when completed, we will see how one of the project references has been remembered for those related to EF.

We are now ready to use the capability of our ORM, as far as development is concerned. We will likewise perceive how to complete an underlying database connection and demonstrating classes, following the two ideal models referenced previously.

Database-First

Allow us first to consider the Database-First mode, which - as we said - is primarily directed to the current database, i.e from which to derive a model.

Preparing database

We have made a database named TECHNET, inside it, there are two tables: "Articles" and "Families". The T-SQL script that creates the entities, as well as the continuation as per the following:

```
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 expressed, between the two tables, where CodFamiglia is the field that links the table Articles with the Families table to get the portrayal of the latter. Run the script and we will have few records.

Modeling classes with EF

Now 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 recover, from the menu of Visual C # Items »Date, an object of type ADO.NET Entity Data Model, we will call it "TechnetModello ".

After confirming your choice, Visual Studio will launch the Wizard for Entity Data Model, so that we can indicate what type of model is used, in this case, will choose "EF Designer from database ', which realizes the Database-First paradigm.

ti will ask to make a connection to our database, please 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.

Now we will see the generated connection string and we can save it in App.config, you can give a name for future use.

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

The wizard will finish 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 worldview, we have effectively referenced how the database will provide the framework demonstrating classes and not the other way around. Hence, assuming in this mode we will alter some property and we will find ourselves in the circumstance of recovering the model, these changes - present only on one side of the code - will be lost, overwritten to that stated on the database.

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

By opening them, we will notice that following 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 in the side code.

A simple query

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

The query just mentions it, as following:

using (TECHNETEntities db = new TECHNETEntities())

```
{  
```

    Articoli art = db.Articoli.Where((x) => x.CodArt == "ART001").FirstOrDefault();

```
   
```

   5.      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 technique on items class, using lambda function that selects the desired item number and returns the first object found by FirstOrDefault method (please do not operate here validity checks of the data, which of course in a real context must be provided).
Using the art variable, we can refer to its properties and then ask for the issuance of a MessageBox text having equal to DESERT properties of art, we get the following output:

It is clear that the tremendous capability of the Entity Framework, which for the developer translates into the factors of essential importance, such as the impressive 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 established that the two entities Articles and Families were connected 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 guidelines, which carry out the joins, that are absent at the model level.

Assume we need to choose 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);

Launched, as before, the TECHNETEntities object, we proclaim 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 technique 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 currently 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 quest for the product ART005, the showed result is the following:

In the event that we had assessed this imperative in the planning stage, 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.

Let's insert items in the table by using SQL Management Studio Convenience,, a Foreign Key that establishes the relationship between Articles and Families.

Visual Studio side will have to regenerate the data model once you save the changes to the database, 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:

This followed with a brief wizard, where we'll just simply confirm the refresh.

Henceforth, we will see that our model will be redesigned by building up a connection between the two tables.

This progression is to adjust our classes in a significant manner. Class Families procure a new property, Articles, type ICollection <items>, while the Articles class now has a property Families, type Families, declared virtual.

This implies 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.

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

As such, the class of the item 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 segment, we will see a method of Code-First, usable in the case of this database, leaving for the 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 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, yet 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 beforehand, with the exception of the mode to be selected, which in this case will be "Code-First from Database". We proceed with the leftover strides, as you did in the case of Database-First.

Class definition

As we will see, there are graphical portrayals of the entities and their relationships, the generated classes beginning from our diagrams 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);

```
        }  
```
```
    }  
```
```
}  
```

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; }

```
    }  
```
```
}  
```

Indeed, there isn't a lot of difference compared to that generated in Database-First, aside from 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 model, 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 greatest 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; }

```
    }  
```
```
}  
```

For this situation, you will see an additional class constructor, which initializes the property defined as ICollection <Articles> on the basis of the HashSet class, that performs operations on the rundown.

Class modify and migrations

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

What occurs in the event that we attempt to run the Application now? Following a couple of moments of pausing, 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 absent in the database.

We present here the idea of migration: EF is capable, from our classrooms and uses a database for comparison, to figure out what transforms you need to make to it, to make it compatible with the model set in the program. We start with setting up our project migration. This kind of activity is done, 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.