This tutorial guides you through the process of creating a simple application powered by ADO.NET Entity Framework. In less than 5 minutes you will have a ready-to-use data access layer for your business objects.
Please note that this tutorial is not applicable for Entity Framework Core. It is intended for previous Entity Framework versions. You can find links to Entity Framework Core tutorials in the See Also section, in the end of this topic.
In this walkthrough:
ADO.NET Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework. It is designed to enable developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications.
In order to connect to PostgreSQL server you need the server itself running, dotConnect for PostgreSQL installed and IDE running. ADO.NET Entity Framework requires .NET Framework 3.5 Service Pack 1 or higher, Visual Studio 2008 Service Pack 1 or higher, and PostgreSQL server 8.0 or higher. Note that Entity Framework support is available only in Professional Edition of dotConnect for PostgreSQL.
For Entity Framework 6, you will also need a NuGet Visual Studio extension installed since it is used for adding EntityFramework NuGet package. Alternatively you may create model for Entity Framework v1 or v4, which don't require NuGet, in this tutorial.
In this tutorial it is assumed that you already have the database objects
created. You have to execute a script from the following file if you have
not done so yet:
\Program Files\Devart\dotConnect\PostgreSQL\Samples\crm_demo.sql
In this sample we will create a simple console application. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial. Start Visual Studio and create a new console application.
The following actions are required if you want to create an Entity Framework v6 model.
Open the Package Manager Console window and execute the following command in it.
install-package EntityFramework
After this add the following line:
<provider invariantName="Devart.Data.PostgreSql" type="Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices, Devart.Data.PostgreSql.Entity.EF6, Version=7.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
to the entityFramework -> providers section of the application config file.
<entityFramework> <providers> <provider invariantName="Devart.Data.PostgreSql" type="Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices, Devart.Data.PostgreSql.Entity.EF6, Version=7.1.26.0, Culture=neutral, PublicKeyToken=09af7300eec23701" /> </providers> </entityFramework>
Note: replace 7.1.26.0 with the actual assembly version.
You also need to add the following assemblies to the project references:
After this you need to rebuild the project before running the EDM wizard.
The model you've just generated is ready to use. Its name is Model1.edmx, unless you changed it in the step 2. You can inspect it visually in the designer or take a look behind the scenes with XML Editor.
The wizard creates classes for all selected tables that represent entities. It also creates a descendant of System.Data.Entity.DbContext class (used by default since Visual Studio 2012) or System.Data.Objects.ObjectContext class (used by default in older Visual Studio versions), which controls the connection to the database, and the whole data flow. This class includes properties and methods named after your database objects. You will use these members to retrieve and modify data in the context. The code is contained in an autogenerated file Model1.Designer.cs (Model1.Designer.vb).
Note that if you have an association between two properties of non-coinciding numeric types, you can manually change both conceptual and storage types to the type that will be wide enough to include data for each property.
All Entity Framework operations through a DbContext descendant (default since Visual Studio 2012) or through a ObjectContext descendant, which is named CrmDemoEntities in this tutorial. To retrieve data you have to first create an instance of the context, then prepare a query with LINQ to Entities or EntitySQL or their mix, and then access the object returned by the query, which may be a collection of objects or a single object.
Let's read all the data from the table Company, sort it by CompanyID, and output some columns. Add the following block of code to the method Main:
As simple as that. You prepare a query and then iterate through it as you would do with a usual collection of objects. The database interaction is performed by Entity Framework in the background. Now let's see who is who in this code sample.
Here is the project's output in the console:
Note that the LINQ to Entities query code just describes the query. It does not execute it. This approach is known as deferred execution.
Now let's query data from two tables united with a foreign key. Replace the old code with this:
This sample is much like the previous one, with exception that it adds the Include method that instructs the query to retrieve data from one more table.
What earlier was adding rows to tables, now is just adding new objects to context collections. When you are ready to send the changes to the database, call the SaveChanges() method of the context. Before doing this, you must first set all properties that do not support null (Nothing) values. The SaveChanges() method generates and executes commands that perform the equivalent INSERT, UPDATE, or DELETE statements against the data source.
Let's add a new product and a new category to the database. Replace the old code with this:
The methods AddToProduct_Categories, AddToProducts, and others are automatically generated in the context. Such methods exist for every class in your model.
Note that after you have added the new product and category by submitting the changes, you cannot execute this solution again as is. To execute the solution again, change the names and IDs of the objects to be added.
Entity instances are modified as usual. The only thing to remember is that you have to invoke the SaveChanges() method to send the data to the database.
Append the following block to the existing code and launch the project:
To extract an instance from a context use the DeleteObject method of the context. The object is removed from the collection of its type, but not destroyed. To delete the object's data from the database invoke the SaveChanges() method.
You can do this with a block of code like the following:
Now that you can perform the basic data manipulation with Entity Framework, you can move on to some advanced topics.
We recommend you to use Entity Developer (Devart Entity Model, *.edml) instead of EDM Designer (ADO.NET Entity Data Model, *.edmx) because it is adjusted for working with PostgreSQL and offers advanced functionality. Additionally, Entity Developer registers Entity Framework v6 providers in app.config automatically and offers advanced visual designer and support for Database First / Model First approaches for EF Core.
Here are some useful links to MSDN:
For hands-on experience download the separate Entity Framework Query Samples (EF1/EF4/EF5/EF6) package or use samples shipped with dotConnect for PostgreSQL. You can access the samples from the Start menu.
To understand deeper the works of Entity Framework engine you can watch the generated SQL statements in dbMonitor.
Entity Framework section | Entity Framework Support Overview | Entity Framework Core Code-First Tutorial for Full .NET Framework | Entity Framework Core Database-First Tutorial for Full .NET Framework | Entity Framework Core Code-First Tutorial for .NET Core | Entity Framework Core Database-First Tutorial for .NET Core