Working With Queries Using Entity Framework Core and Entity Developer

Introduction

Entity Developer is a popular ORM tool from Devart with many interesting features. In this article, we’ll use Entity Developer to generate an entity data model and its corresponding classes.

Once your data model is available, you can create a new query from the Tools -> Entity Developer menu option inside Visual Studio. This article discusses how to work with queries in Entity Developer.

Pre-requisites

To be able to work with the code examples demonstrated in this article, you should have the following installed in your system:

• .NET 5.0
• Visual Studio 2019 Community Edition
• SQL Server 2019 Developer Edition
• Entity Developer

You can download Visual Studio 2019 from here: https://visualstudio.microsoft.com/downloads/

You can download SQL Server 2019 Developer Edition from here: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

You can download a copy of Entity Developer (trial version) from here: https://www.devart.com/entitydeveloper/download.html

You can download .NET 5.0 runtime from here.

Create a New ASP.NET Core 5.0 Project in Visual Studio 2019

Assuming that the necessary software has been installed on your computer to be able to work with Entity Developer, follow the steps outlined below to create a new ASP.NET Core Web API project.

1. First off, open the Visual Studio 2019 IDE.
2. Next, click "Create a new project" once the IDE has loaded.
3. In the "Create a new project" screen, select “ASP.NET Core Web API” as the project template.

Figure 1

Click the "Next" button.

• Specify the project name, location, and where it should be stored in your system.
• Optionally, click the "Place solution and project in the same directory" checkbox.
• Next, click the "Create" button.
• In the "Create a new ASP.NET Core Web Application" dialog window that is shown next, select "API" as the project template.
• In the “Additional Information” screen, .NET 5.0 as the framework version.

Figure 2

• You should disable the "Configure for HTTPS" and "Enable Docker Support" options by disabling the respective checkboxes.
• Since we'll not be using authentication in this example, specify authentication as "No Authentication."
• Finally, click on the "Create" button to finish the process.

Creating the Database

Now that the application is ready for use, let’s create the database. We’ll keep the database design simple. Our database will have just two tables with an easy-to-follow design.

Launch the SQL Server Management Studio and create a new database. Name the database 'Test.' Next, use the following script to create two tables named 'Customers' and 'Orders' inside the Test database.

CREATE TABLE [dbo].[Customers](

      [CustomerId] [bigint] IDENTITY(1,1) NOT NULL,

      [CustomerName] [nvarchar](max) NOT NULL,

      [CustomerEmail] [nvarchar](max) NOT NULL,

      [CustomerPhone] [nvarchar](max) NOT NULL,

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 

(

      [CustomerId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[Orders](

      [OrderId] [bigint] IDENTITY(1,1) NOT NULL,

      [OrderNumber] [nvarchar](max) NULL,

      [OrderDate] [datetime] NULL,

      [OrderQuantity] [int] NULL,

      [CustomerId] [bigint] NULL,

 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 

(

      [OrderId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Figure 3 shows how these two tables are related.

Figure 3

We’ll refer to this database in the subsequent sections of this article.

Create an Entity Data Model Using Entity Developer

In this section, we’ll explore how to create an Entity Data Model using Entity Developer. To create an Entity Data Model using Entity Developer in Visual Studio 2019, follow the steps outlined below.

• Right-click on the project in the Solution Explorer Window.
• Select Add -> New Item.

Now you should specify how your model should be created. There are two options: Database First (this is selected by default) and Model First. In this example, we’ll take advantage of the Database First approach.

• Click on the "Next" button to continue.
• In the next screen, specify the connection properties and click on the “Next” button to continue.

On the next screen you should specify what the model should contain. The "Generate from Database" option is selected by default.

• Since we’ll need the model to be created from the database, click on the “Next” button to continue.

On the next screen the database metadata is retrieved by the Entity Developer runtime and the database objects are selected.

• De-select all options and then specify only the database objects you would like to be a part of the model. Here’s where we’ll select the Customers and Orders tables.
• In the "Set up naming rules" screen you can specify naming rules for your entities.
• Since we will not be making any changes here, click on the "Next" button to continue.
• Specify a name for the Context Namespace and click on the “Next” button to continue. On the next screen you can specify what your model diagram should contain.

You’ll be able to see the two database objects we selected earlier and the "All Entities" radio button is selected by default. This implies that our model will comprise of all the entities or tables in the database.

• Since we will not be changing anything here, click on the "Next" button to continue. On the following screen you can choose the code generation templates you want to use.
• Since we will not be modifying anything here, click on the "Next" button to continue.
• In the next screen, click the “Next” button to continue.

Figure 4

• Click “Finish” to complete the process.

Your Entity Data Model using Entity Developer has been created. Here’s how your Entity Data Model should look.

Figure 5

Working With Queries

When working with Entity Framework Core you might often need to view or edit data and execute queries against the Entity Data Model. You can take advantage of Entity Developer to create and execute LINQ and Entity SQL queries against the model.

Note that while LINQ queries can be executed against all model types, Entity Framework models can be executed against Entity Framework data models only and HQL queries are also specific to NHibernate (i.e., you can run HQL queries against NHibernate data models only).

To create a new query in Entity Developer from within the Visual Studio 2019 IDE, click on Tools -> Entity Developer -> New Query to launch the Query window as shown in the screenshot given below.

Figure 6

Alternatively, you can click Ctrl + Q keys together as well. Note that you should keep the Entity Data Model open before you launch the Query window. The following screenshot shows how you can create a query in Entity Developer.

Figure 7

Note that you can save the queries you write in Entity Developer as *.eqry files. To save a query, just click on the Save button in the Standard Toolbar and then specify a name for your query.

Note that to be able to work with the Queries editor of Entity Developer, you should provide the path to the Entity Framework Core runtime assemblies in the Entity Developer config file.

As an alternative, you can use a tool called LINQPad, a light-weight, free (the standard edition is free) tool that provides similar features. LINQPad is a popular tool for executing LINQ queries – you can leverage it to interactively query databases using LINQ.

Summary

Entity Framework is an Object Relational Mapper (ORM) tool from Microsoft that enables developers to create data-centric applications by programming against a conceptual model rather than the relational model.

Entity Framework Core, a light-weight cross platform version of Entity Framework, provides you a standard way to access data from several data sources. Entity Developer supports ADO.NET Entity Framework, Entity Framework Core, Hibernate, LinqConnect, Telerik Data Access, and LINQ to SQL. You can get started using Entity Developer after downloading a trial version from here.