Getting Started With Entity Framework Core

In this tutorial, you'll find a comprehensive guide to mastering Entity Framework Core (EF Core), from installation to advanced features. We'll walk you through the essentials of EF Core, starting with an exploration of its core concepts and the two primary development approaches: Database-First and Model-First. Beyond the basics, you'll learn how to use visual design tools, like Entity Developer, and code-based techniques to create and manage entities, relationships, and database contexts.

What is Entity Framework Core?

Entity Framework Core is a powerful open-source and cross-platform Object-Relational Mapper (ORM) developed by Microsoft for .NET developers.

Placed between a .NET application and a database server, this ORM tool maps .NET objects to database tables and translates C# data access code into SQL statements that the database server can handle easily.

EF Core integration into .NET projects offers numerous benefits for developers:

• There is no need to learn a new programming language, as it is enough to use C# and LINQ.
• It's possible to keep C# models in sync with the database tables.
• During execution, EF Core tracks changes made to C# entities so it can effectively handle their storage.
• EF Core grants support for multiple database providers.

With Entity Framework Core, developers can interact with databases using .NET (C#) objects rather than writing SQL queries. This approach undoubtedly brings more simplicity and flexibility to data access when building .NET applications.

Database-First and Model-First approaches

Entity Framework Core offers developers two primary approaches to developing data models and databases: Database-First and Model-First.

The Database-First approach is ideal when working with an existing database, allowing developers to reverse-engineer the model and quickly generate code from the database schema. This method is very popular among programmers who prioritize database design and want to work with an existing database structure.

Meanwhile, the essential concept of the Model-First approach is defining the data model first and then generating both the database schema and the corresponding entity classes from that model. This approach is beneficial if you want to focus on designing your data model first and have EF Core automatically generate the necessary code and database schema for you.

You can apply Entity Developer in EF Core development regardless of whether you choose the Model-First or Database-First approach. The implementation of this visual ORM builder will make the process more intuitive and efficient, offering powerful design tools, flexible configurations, and smooth integration with your database. The graphical interface of Entity Developer allows you to create the entities without writing code, after which Entity Framework generates both the domain classes and the corresponding database schema.

Further in this tutorial, we'll discuss each EF Core development approach in more detail.

Visual design tool or code-based method

Entity Framework offers two ways to build Object-Relational Mapping (ORM) models: using a visual design tool like Entity Developer by Devart or writing code manually.

By default, Entity Framework Core relies on code-based methods to build ORMs, which means that developers must create and manage complex data models by hand.

In contrast, Entity Developer is a powerful, user-friendly graphical tool offering an intuitive drag-and-drop interface for quicker model creation and easier visualization of relationships between different data entities without writing much code. It is ideal for those who prefer a more visual, less code-intensive way of working with EF Core.

Pros and cons of using a visual design tool

Advantages

• Faster application development
• Visualization for nearly all kinds of mapping
• Clear visual representation of the data model
• Deployment of changes in models to databases and vice versa
• Support for the Model-First and Database-First approaches
• Operations with custom templates
• Reduction of errors
• More efficient collaboration

Disadvantages

• Limited control for more complex or custom configurations
• Customization limitations can be handled through manual configurations
• The learning curve required to master the tool's complex features

Pros and cons of the code-based method

On the other hand, the code-based approach involves manual coding to define entities, relationships, and configurations using EF Core's fluent API or data annotations. This method gives experienced developers complete control over the model and database schema, which they can use to fine-tune their configurations.

Advantages

• Independent, free method
• Total control over the code
• Requires a minimum of memory and resources on the computer

Disadvantages

• Much manual coding requires programming experience
• Requires a high learning curve for beginners to use the method
• Slower setup due to building larger models with many complex relationships
• High error risks for developers, especially when dealing with complex configurations
• Limited multitasking capabilities when working with several files or projects

Install EF Core packages

To get started with EF Core in your project, you need to install the necessary EF Core packages along with other data provider packages for the database in use (e.g., Oracle, MySQL, PostgreSQL, SQLite, etc.). The following instructions outline the process of installing the essential EF Core NuGet packages by running corresponding commands from the operating system's command line. Also, throughout this section, we'll be using PostgreSQL as an example database to illustrate the installation of the data provider packages.

1. To install the core package of Entity Framework, execute the following .NET CLI command:

dotnet add package Microsoft.EntityFrameworkCore

2. Next, install the following package required for the EF Core tools to work:

dotnet tool install Microsoft.EntityFrameworkCore.Tools

3. To install the data provider package for PostgreSQL, execute the following command:

dotnet add package Devart.Data.PostgreSql.EFCore

Upon executing this set of commands, all the necessary EF Core packages are installed. By running this series of commands, you will have all the necessary EF Core packages installed.

Model-First approach

In the Model-First approach, the emphasis is on creating the data model first, and then generating both the database schema and the corresponding entity classes from that model.

Using Entity Framework Core, you can define your data model either visually with Entity Developer or through code.

For a better understanding of the main configuration steps, you can find detailed instructions for each method in the corresponding Via Entity Developer and Via code sections.

If you choose Entity Developer, make sure you have installed this application by following the on-screen instructions.

Model-First: Add entities

By adding entities, we mean the creation and configuration of classes and their properties that represent the data you want to map to a specific database table.

Via Entity Developer

With the help of Entity Developer, you can create an empty model using the Model Wizard or supplement the previously developed model. In our case, we will create a new data model from scratch and configure its entities afterward.

Create an EF Core model

1. Open your project in Visual Studio. As we're using PostgreSQL as an example data provider, we'll open the PgEFCore project.

2. Right-click the PgEFCore project in the Solution Explorer and select Add > New Item.

3. Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.

4. In the Create Model Wizard, select Model First and click Next.

5. On the Model properties page, define the main settings of your model and click Next. Thus, you can select the target EF Core version from the ones supported by the .NET frameworks of your project and specify the name in the Entities Namespace field.

6. Choose code generation templates for the objects that you want to add to the new model. In the properties area of this wizard, you can configure different parameters you want the object to follow. Let's use the default settings for this tutorial and click Next.

Your model is ready now.

7. Finally, click Finish.

As a result, the model you've just created opens.

Add new classes and properties

Once the newly created model opens, you can begin with creating classes and configuring properties.

1. In the Model Explorer pane, right-click your model and select New Class.

2. In the Class Editor dialog, enter Customers in the Name field and click OK.

3. Once the Customers class is created, drag it onto the model to begin building the diagram.

4. To define the class properties, right-click Customers and select Add > New Property.

5. In the Property Editor dialog, configure the following Customers properties and click OK.

• Name: Id
• Type: String
• Value Generated: Never

6. Optional: To change the default column settings, in the Column field, click More (...) to open the Column Editor and define the desired column attributes. Then, click OK to save your settings.

Take the same workflow to add more classes and properties as needed into your model. For example, we'll create and configure two classes, Customers and Orders. Then, we will extend the model by adding the third entity.

Add associations

After you've added the required classes to your data model, you can move on to establishing an association between Customers and Orders.

To create an association, perform the following sequence of actions:

1. Right-click the Customers in the model and select Add > New Association from the context menu.

2. In the Association Editor dialog that appears, configure the required properties and click OK.

• Cardinality: OneToMany
• On Delete Action: NoAction
• Orders Properties: OrderId

Once added, the new association will appear in the diagram and under Associations in the Model Explorer pane.

Add inheritances

If desired, you can extend the current model by adding a third PremiumCustomer class, which will be configured as a derived class from the previously created Customers class. The process of adding an inheritance is very similar to the one of adding an association:

1. Create a new PremiumCustomer class and configure its properties.

2. Right-click the model and select Add > New Inheritance from the context menu.

3. In the Inheritance Editor dialog, configure the following properties and click OK:

• Base Class: Customers
• Derived Class: PremiumCustomer
• Type: Table Per Hierarchy

As a result, PremiumCustomer becomes a subclass of Customers, thus extending our model with the inheritance.

Via code

With this approach, you can manually define your data model through coding by creating classes with properties that map to columns in a database table.

To implement the code-based method, follow the steps below:

1. Install the necessary NuGet packages for Entity Framework Core and the database provider (here: Devart PostgreSQL):

dotnet add package Devart.Data.PostgreSql.EFCore
dotnet add package Microsoft.EntityFrameworkCore.Design

2. Create a new file named Customers.cs in your project and define the Customers class as follows:

public class Customers
{
	public int Id { get; set; }
	public required string Name { get; set; }
	public required string Email { get; set; }
	public required string OrdersCount { get; set; }
	public DateOnly LastOrder { get; set; }
}

This C# class with properties for each column will represent the structure of the Customers table in your database.

Model-First: Add context

Adding a DbContext plays a critical role in connecting your application to the database. It acts as a bridge between your app and the database and is responsible for querying and saving data.

You can add context programmatically through code by creating a DbContext class. Alternatively, tools like Entity Developer from Devart provide a visual designer to define entities and relationships, automatically generating the DbContext and entity classes.

Via Entity Developer

You have to save your model to finalize its creation and configuration. Once saved, the model context files will be successfully generated and stored in the specified folders in the Solution Explorer under PgEFCore.

Clicking any of the displayed folders reveals their content, as shown on the following screen.

Via code

Here, you'll find brief instructions on creating a new DbContext class using manual coding.

For example, if we want to add the ApplicationDbContext class, we need to create a new file named ApplicationDbContext.cs and define the ApplicationDbContext class as follows:

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext: DbContext {
  public DbSet < Customers > Customers {
	get;
	set;
  }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
	optionsBuilder.UsePgSql("YourConnectionStringHere");
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder) {
	modelBuilder.Entity < Customers > (entity => {
  	entity.HasKey(e => e.Id);
  	entity.Property(e => e.Name).IsRequired();
  	entity.Property(e => e.Email).IsRequired();
  	entity.Property(e => e.OrdersCount).IsRequired();
  	entity.Property(e => e.LastOrder).HasColumnType("date");
	});
  }
}

Model-First: Generate your database

After defining your model following the Model-First approach in EF Core, you can generate the database schema from it.

You can generate your database through traditional coding or by applying Devart's Entity Developer, which offers a visual alternative to manual coding.

Via Entity Developer

With Entity Developer, you can generate database scripts from a particular model. To achieve this, follow the step-by-step instructions below:

1. Right-click anywhere in the model and select Generate Database Script From Model from the context menu.

2. In the Generate Database Script Wizard window, leave the Include Drop and Regenerate Storage checkboxes cleared and click Next to proceed.

3. Configure the model synchronization settings and click Next.

4. Review the list of tables that will be scripted and clear checkboxes next to any tables you do not want to be included. Click Next to continue.

5. Now, review the script to ensure it is successfully generated. Click Finish to close the wizard.

Via code

With this approach, you configure domain classes first, and Entity Framework automatically generates the database tables. Additionally, you can manage schema changes through code using the Migration feature.

Below, you will find a list of code-based commands to execute.

1. To add a new migration, open your terminal, navigate to your project directory, and run the command:

dotnet ef migrations add InitialCreate

This command will generate a new migration file in your project's Migrations folder. The migration file will contain the necessary code to create the Customers table in your database.

2. After creating the migration, apply it to your database using the following command:

dotnet ef database update

This command will execute the migration and update your database schema to match the current state of your DbContext and entity models.

Database-First approach

In contrast to Model-First, the Database-First approach in EF Core means that a model is generated from an existing database rather than creating a new model from the beginning.

Database-First: Scaffold entity classes and DbContext

You can scaffold entity classes and DbContext in EF Core either through code or with Entity Developer.

Via Entity Developer

1. Right-click in the Solution Explorer pane and select Add > New Item.

2. Go to Installed > C# Items > Data, select Devart EF Core Model, and click Add.

3. In the Create Model Wizard, select Database First and click Next.

4. Fill in the details of your database connection and click Next.

5. Select Generate From Database and click Next.

6. Choose the database objects you want to scaffold. You can select either all checkboxes or only some of them. Click Next to proceed.

7. Define the naming convention for the property names in the database object and click Next. We suggest keeping the default settings this time.

8. On the Model properties page, change only the Target Framework field. Select .NET 8 (or a different framework your project uses) and click Next.

9. Choose the model diagram contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, select All Entities and click Next.

10. Choose the code generation templates for your objects. You can define different parameters you want the object to follow. Let's use the default settings for this tutorial. Click Next.

Your model is ready now.

11. Finally, click Finish.

Your created model opens.

Via code

This approach uses the command line to scaffold a DbContext from an existing database. Prior to using this approach, you need to make sure that your PostgreSQL server is running and accessible.

Let's scaffold entity classes and DbContext through code by running the commands below:

1. Use the dotnet ef dbcontext scaffold command to generate the DbContext and entity classes from your existing database.

dotnet ef dbcontext scaffold "YourConnectionStringHere"
Devart.Data.PostgreSql -o Models -c ApplicationDbContext

2. Replace the placeholders with your actual connection string and other details.

• "YourConnectionStringHere": Your PostgreSQL connection string.
• Devart.Data.PostgreSql: The provider to use.
• -o Models: The output directory for the generated files.
• -c ApplicationDbContext: The name of the DbContext class to generate.

The scaffolding command will generate the DbContext class and entity classes in the specified output directory (Models in this case).

3. Review and modify the generated files as needed.

For a better understanding of the overall process, we provide an example showing how the generated DbContext class might look like:

 using Microsoft.EntityFrameworkCore;

public partial class ApplicationDbContext: DbContext {
  public ApplicationDbContext() {}

  public ApplicationDbContext(DbContextOptions < ApplicationDbContext > options): base(options) {}

  public virtual DbSet < Customers > Customers {
    get;
    set;
  }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
    if (!optionsBuilder.IsConfigured) {
      optionsBuilder.UsePgSql("YourConnectionStringHere");
    }
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.Entity < Customers > (entity => {
      entity.HasKey(e => e.Id);
      entity.Property(e => e.Name).IsRequired();
      entity.Property(e => e.Email).IsRequired();
      entity.Property(e => e.OrdersCount).IsRequired();
      entity.Property(e => e.LastOrder).HasColumnType("date");
    });

    OnModelCreatingPartial(modelBuilder);
  }

  partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

You can now use the generated ApplicationDbContext in your application, as shown above.

Please keep in mind that the scaffolding process will generate entity classes based on the existing database schema. If your database schema changes, you may need to regenerate the entity classes.

Implement CRUD operations

Entity Framework (EF) Core simplifies database operations through its Object-Relational Mapping (ORM) capabilities. In this part of the tutorial, we'll give detailed instructions on implementing CRUD (Create, Read, Update, Delete) operations using EF Core based on the provided code examples.

Create

To add a new record to the database, create a model object, set its properties, and add it to the DbContext's DbSet. Calling SaveChanges preserves the record in the database. For example, a new Customer is created with properties like FirstName, LastName, and Email, added to the Customers DbSet, and saved.

using(var context = new DvdRentalContext()) {
  // Create a new Customer object
  var newCustomer = new Customer {
	FirstName = "John",
  	LastName = "Doe",
  	Email = "[email protected]"
	// Set other properties as needed
  };

  // Add the new customer to the context
  context.Customers.Add(newCustomer);

  // Save changes to the database
  context.SaveChanges();
}

Read

To read data, you can use LINQ to query the DbContext's DbSet and retrieve the data. The example queries all customers with context.Customers.ToList(), retrieving the entire Customers table as a list. The results are then iterated and displayed, showing each customer's ID, name, and email.

using(var context = new DvdRentalContext()) {
  // Query the Customer table
  var customers = context.Customers.ToList();

  // Display the customers
  foreach(var customer in customers) {
	Console.WriteLine($"Customer ID: {customer.CustomerId}, Name: {customer.FirstName} {customer.LastName}, Email: {customer.Email}");
  }
}

Update

To update a record, locate the entity using a LINQ query (e.g., FirstOrDefault to find a customer by name). Change the properties of the retrieved object, and call SaveChanges to store the changes. As you can see, the following example updates a customer's first and last names.

using(var context = new DvdRentalContext()) {
  // Find the customer with the name "John Doe"
  var customer = context.Customers
	.FirstOrDefault(c => c.FirstName == "John" && c.LastName == "Doe");

  if (customer != null) {
	// Update the customer's name
	customer.FirstName = "Jane";
	customer.LastName = "Smith";

	// Save changes to the database
	context.SaveChanges();

	Console.WriteLine("Customer name updated successfully!");
  } else {
	Console.WriteLine("Customer not found.");
  }
}

Delete

To delete a record, find the entity using a LINQ query, remove it from the DbSet context with Remove, and call SaveChanges to apply the deletion. The example deletes a customer by name, confirming the action or reporting if the customer is not found.

using(var context = new DvdRentalContext()) {
  // Find the customer with the name "John Doe"
  var customer = context.Customers
	.FirstOrDefault(c => c.FirstName == "John" && c.LastName == "Doe");

  if (customer != null) {
	// Remove the customer from the context
	context.Customers.Remove(customer);

	// Save changes to the database
	context.SaveChanges();

	Console.WriteLine("Customer deleted successfully!");
  } else {
	Console.WriteLine("Customer not found.");
  }
}

Each of the CRUD operations ensures easy and efficient data management in your EF Core-based application. These methods cover the essential tasks for inserting, reading, updating, and deleting records in the database.

EF Core performance tips

As we already mentioned in this tutorial, Entity Framework Core is a powerful ORM, but inefficient usage can lead to performance issues.

We will give you detailed and clear instructions on how to optimize your database operations by following the key EF Core performance tips:

• Avoid query operations in loops: Prevents the N+1 query problem.
• Select only important columns: Reduces data transfer.
• Use the NoTracking method: Minimizes memory overhead.
• Use SplitQuery: Separates large queries for better execution efficiency.

Implementation of these practices ensures faster, more scalable applications.

Avoid query operations in loops

The problem lies in performing database query operations inside loops, a common mistake among junior developers. This leads to the N+1 query problem, as querying inside loops sends multiple database queries (e.g., 100 queries for 100 entities). To avoid this, use batch queries to fetch data once and process it in memory.

Example of inefficient code (query in a loop)

public void QueryInsideLoop()
{
    using var context = new MyDbContext();

    for (int i = 1; i <= 100; i++)
    {
        var entity = context.MyEntities.FirstOrDefault(e => e.Id == i);
    }
}

This code executes 100 separate queries and results in poor performance.

Example of efficient code (batch query)

public void QueryOutsideLoop()
{
    using var context = new MyDbContext();

    var entities = context.MyEntities
        .Where(e => e.Id <= 100)
        .ToList();

    foreach (var entity in entities)
    {
        var id = entity.Id; // Simulating loop logic
    }
}

Running this code allows fetching all entities in one query, reducing database load and improving performance. Keep in mind, though, that batch querying is ideal for small datasets to avoid excessive memory usage.

Select only important columns

Usually, it is not necessary to retrieve all the columns from a table when only a few are needed, as this results in slower performance. For example, if you don't need all 20 columns from the table but Name, Surname, and Year of birth, you wouldn't like to extract all the rest columns in the table.

Example of inefficient query (loading all columns)

public void SelectAllColumns()
{
    using var context = new MyDbContext();

    var results = context.MyEntities.ToList();
}

Instead, we recommend using projections with LINQ and selecting only the necessary columns. You can achieve this using anonymous types or a DTO (Data Transfer Object).

Example of optimized query (selecting specific columns)

public void SelectImportantColumns()
{
    using var context = new MyDbContext();

    var results = context.MyEntities.Select(e => new { e.Id, e.Name }).ToList();
}

With fewer columns retrieved, you only load the data you need, reducing memory and bandwidth usage.

Use the NoTracking method

By default, Entity Framework Core tracks changes to the retrieved entities. Tracking is useful when updating and deleting entity objects, but it adds additional overhead for read-only queries. To overcome this and optimize performance, use the NoTracking method to disable change tracking.

Example of default tracking

public void SelectWithTracking()
{
    using var context = new MyDbContext();

    var results = context.MyEntities.ToList();
}

Here, EF Core tracks all entities, increasing overhead due to change tracking.

If you would like to disable tracking, we recommend applying the NoTracking method. In this case, the Entity Framework doesn't track changes to the retrieved entities, which reduces overhead and increases performance, especially for read-only data.

Example of using AsNoTracking

public void SelectWithNoTracking()
{
    using var context = new MyDbContext();

    var results = context.MyEntities.AsNoTracking().ToList();
}

Using AsNoTracking for read-only queries is a simple yet effective way to boost the performance of your EF Core applications.

Use SplitQuery to separate queries

By default, EF uses a single-query approach when loading related data. This can potentially result in performance issues like Cartesian explosion due to large JOIN queries. The code example that follows vividly demonstrates such a situation.

Examples of using SingleQuery

public void DefaultSingleQuery()
{
    using var context = new MyDbContext();

    var results = context.MyEntities
        .Include(e => e.RelatedEntities)
        .ToList();
}

To optimize complex queries, use the SplitQuery option instead of applying SingleQuery. This allows Entity Framework to execute separate queries for the primary entity and its related entities, reducing the impact of large JOINs.

Examples of using SplitQuery

public void UsingSplitQuery()
{
    using var context = new MyDbContext();

    var results = context.MyEntities
        .Include(e => e.RelatedEntities)
        .AsSplitQuery()
        .ToList();
}

On the one hand, using AsSplitQuery can increase performance and reduce complexity in such scenarios. On the other hand, it means multiple queries will be executed, so be sure to monitor the impact on the database load.

EF Core features you need to know

It goes without saying that Entity Framework Core is a robust tool for data access in .NET applications. However, diving into every detail of this ORM tool might not be our top priority. Nevertheless, understanding a few of the key features of EF Core can save you significant time and effort. That's why, rather than overwhelming you with every feature, we've selected five essential ones you really need to know:

• Query Splitting: Optimizes your database queries.
• Bulk Updates and Deletes: Improves the overall performance.
• Raw SQL Queries: Gains more control when needed.
• Query Filters: Maintains clean and efficient queries.
• Eager Loading: Optimizes data retrieval

Query splitting

Query splitting is especially useful when loading multiple collections in Entity Framework (EF) Core. It helps prevent the Cartesian explosion problem, which can degrade performance. For example, let's assume we want to retrieve a department's information and its team's and employees' data. To achieve this, we can write the following query:

Department department =
    context.Departments
        .Include(d => d.Teams)
        .Include(d => d.Employees)
        .Where(d => d.Id == departmentId)
        .First();

This query translates to a single SQL statement with two JOIN operations. However, because the JOIN operations occur at the same level, the database generates a cross-product. Each row from the Teams table is paired with each row from the Employees table, resulting in many rows being returned. This significantly degrades the overall performance.

We can avoid these performance issues with query splitting, as shown below:

Department department =
    context.Departments
        .Include(d => d.Teams)
        .Include(d => d.Employees)
        .Where(d => d.Id == departmentId)
        .AsSplitQuery()
        .First();

With AsSplitQuery, EF Core executes a separate SQL query for each data collection and navigation. This also results in reducing the number of rows returned, thus improving the overall performance.

Bulk updates and deletes

Entity Framework (EF) Core 7.0 introduced two APIs, ExecuteUpdate and ExecuteDelete, for performing bulk updates and deletes. These methods enable you to efficiently update a large number of rows in one roundtrip to the database instead of using iterative approaches.

Let's see how these methods work in practice. Suppose the company has decided to give a 5% raise to all employees in the "Sales" department. Using a traditional approach, we will iterate through each employee and update their salary individually.

var salesEmployees = context.Employees
    .Where(e => e.Department == "Sales")
    .ToList();

foreach (var employee in salesEmployees)
{
    employee.Salary *= 1.05m;
}

context.SaveChanges();

This traditional method results in multiple database roundtrips, which can be slow and resource-intensive, especially for large datasets. We can achieve the same result in a single round trip using ExecuteUpdate:

context.Employees
    .Where(e => e.Department == "Sales")
    .ExecuteUpdate(s => s.SetProperty(e => e.Salary, e => e.Salary * 1.05m));

This approach executes a single SQL UPDATE statement, directly modifying the salaries in the database without loading entities into memory. The result is faster execution and reduced resource usage.

Raw SQL queries

Entity Framework Core 8 added a new feature that allows querying unmapped types with raw SQL. This is useful when we need to retrieve data from a database view, stored procedure, or a table that does not directly correspond to any of our entity classes.

Let's assume we want to retrieve a sales summary for each product. With EF Core 8, we can create a simple ProductSummary class representing the structure of the result set and query it directly:

public class ProductSummary
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal TotalSales { get; set; }
}

var productSummaries = await context.Database
    .SqlQuery<ProductSummary>(
        @$"""
        SELECT p.ProductId, p.ProductName, SUM(oi.Quantity * oi.UnitPrice) AS TotalSales
        FROM Products p
        JOIN OrderItems oi ON p.ProductId = oi.ProductId
        WHERE p.CategoryId = {categoryId}
        GROUP BY p.ProductId, p.ProductName
        """)
    .ToListAsync();
                

The SqlQuery method returns an IQueryable, which allows you to combine the flexibility of raw SQL queries with the expressiveness of LINQ.

For security reasons, you should use parameterized queries to prevent SQL injection vulnerabilities. The SqlQuery method accepts a FormattableString, which means you can safely use an interpolated string. Each argument is converted to a SQL parameter.

Query filters

Query filters are like reusable WHERE clauses you can apply to your entities. These filters are automatically included in LINQ queries whenever you retrieve entities of the corresponding type. This ensures that you don't have to duplicate the same filtering criteria across your application.

For example, in a multi-tenant application, you may need to filter data based on the current tenant. Query filters make this easy to implement:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Associate products with tenants
    public int TenantId { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // The current TenantId is set based on the current request/context
    modelBuilder.Entity<Product>().HasQueryFilter(p => p.TenantId == _currentTenantId);
}

// Now, queries automatically filter based on the tenant:
var productsForCurrentTenant = context.Products.ToList();
                

When configuring multiple query filters on the same entity, only the last filter is applied. To combine multiple filters, use the && (AND) and || (OR) operators.

When needed, you can bypass filters in specific queries using the IgnoreQueryFilters method.

Eager loading

Eager loading in EF Core enables you to load related entities along with the main entity in a single database query. This approach improves application performance by reducing the number of database queries, especially for complex object graphs or scenarios where lazy loading could lead to multiple inefficient queries.

The following example demonstrates a VerifyEmail use case. It loads an EmailVerificationToken and its related User entity using the Include method to modify both entities in a single operation.

internal sealed class VerifyEmail(AppDbContext context)
{
    public async Task<bool> Handle(Guid tokenId)
    {
        EmailVerificationToken? token = await context.EmailVerificationTokens
            .Include(e => e.User)
            .FirstOrDefaultAsync(e => e.Id == tokenId);

        if (token is null || token.ExpiresOnUtc < DateTime.UtcNow || token.User.EmailVerified)
        {
            return false;
        }

        token.User.EmailVerified = true;

        context.EmailVerificationTokens.Remove(token);

        await context.SaveChangesAsync();

        return true;
    }
}
                

In this example, EF Core generates a single SQL query that joins the EmailVerificationToken and User tables and successfully retrieves all the required data.

Eager loading (and query splitting, discussed earlier) is not a universal solution. To optimize performance and avoid retrieving unnecessary data, consider using projections when you need only specific properties from related entities.

Additional Entity Developer benefits

Apart from the Entity Developer key features, there are a variety of additional benefits worth mentioning. Here, we will briefly explore the essential advantages that users can gain from Entity Developer.

Data sources

Devart Entity Developer is compatible with a broad selection of databases and online services, allowing developers to operate effortlessly across different platforms. It provides native support for popular databases such as SQL Server, MySQL, Oracle, PostgreSQL, SQLite, Firebird, and DB2.

Additionally, it connects to a wide range of cloud platforms using dotConnect and open-source data providers: Salesforce, Salesforce Marketing Cloud, QuickBooks Online, SugarCRM, Dynamics CRM (Dynamics 365), Zoho Books, Zoho CRM, Zoho Desk, Adobe Commerce and more. This flexibility ensures that developers can use Entity Developer in diverse environments with no need to switch tools, facilitating workflows and reducing the learning curve for managing different data sources.

Wide mapping

Entity Developer supports advanced mapping scenarios, including one-to-one, one-to-many, and many-to-many relationships, with an intuitive drag-and-drop interface. Developers can visually define associations, inheritance hierarchies, and complex JOINs without writing extensive code.

As part of Entity Developer, wide mapping allows you to perform the following actions:

• Enable visual mapping for all compatible ORMs
• Provide visual editors for classes, properties, complex types, and enums
• Support multiple inheritance types and associations
• Map CRUD operations to stored procedures
• Generate methods from stored routines or command texts
• Visually edit the storage layer of Entity Framework v1-v6 models
• Support table splitting, entity splitting, and query views

Powerful refactoring

The Model Refactoring wizard allows you to quickly configure mapping aspects that cannot be automatically generated from your database. With its help, you can create the following entities:

• Inheritance hierarchies
• Complex types
• Table splitting

Code generation templates

The template-based code generation offered by Entity Developer meets the requirements of even the most demanding users.

Using different types of templates that are included in the application, you can fine-tune the code generation process according to your particular needs.

Here's a brief list of the operations you can do following this approach:

• Generate C# or Visual Basic code
• Create code using T4 templates
• Edit templates in a convenient editor with syntax highlighting
• Store templates with models or in ED resources
• Add properties for model objects that affect code generation
• Get unlimited generation capabilities

Conclusion

This tutorial gives detailed instructions on how to get started with Entity Framework Core, a powerful and popular ORM that simplifies data access and accelerates development in modern .NET applications. EF Core enables developers to implement either the Model-First or Database-First approach. The implementation of the Model-First method means that the developer defines the model first, and EF generates a database using this model. Meanwhile, in the Database-First approach, the model is generated using an existing database. Both approaches are thoroughly explained in this tutorial, with detailed code snippets, illustrations, screenshots, and additional resources. Additionally, we covered tools like Devart Entity Developer, which enhances the EF Core experience, enabling much faster development while maintaining high code quality.