How to use EF Core query types in ASP.NET Core 7

0

Entity Framework Core (EF Core for short) is a popular ORM (object-relational mapper) from Microsoft that allow you to perform CRUD operations (create, read, update, and delete) without having to know how the data is persisted in the underlying database.

When working with ORMs, we often leverage models that are mapped to database tables. However, what if we have a model that doesn’t mimic a database table? How can we map non-entity types and populate objects of such a model in our applications? We can accomplish this with query types.

Initially introduced in EF Core 2.1, query types are non-entity types (classes) that can map to tables or views in the database without an identity column specified, meaning tables and views that lack a key. EF Core query types make it simpler to query views and model types that don’t require identity columns. However, because query types don’t have an identity column defined, you cannot insert, update, or delete data using them. You can use them only to retrieve the data.

Query types allow you to specify a mapping between a database query and your domain classes. You can then use the same query type with different types of EF Core queries, such as LINQ to Entities or EF Core Migrations.

This article discusses how we can use EF Core query types in ASP.NET Core 7 applications. To work with the code examples provided in this article, you should have Visual Studio 2022 Preview installed in your system. If you don’t already have a copy, you can download Visual Studio 2022 here.

Create an ASP.NET Core 7 Web API project in Visual Studio 2022

First off, let’s create an ASP.NET Core Web API project in Visual Studio 2022. Following these steps will create a new ASP.NET Core Web API project in Visual Studio 2022:

  1. Launch the Visual Studio 2022 IDE.
  2. Click on “Create new project.”
  3. In the “Create new project” window, select “ASP.NET Core Web API” from the list of templates displayed.
  4. Click Next.
  5. In the “Configure your new project” window, specify the name and location for the new project.
  6. Optionally check the “Place solution and project in the same directory” check box, depending on your preferences.
  7. Click Next.
  8. In the “Additional Information” window shown next, under Framework, select .NET 7.0.
  9. Leave the check box that says “Use controllers…” checked since we’ll be using controllers in this example. Leave the “Authentication Type” set to “None” (default).
  10. Ensure that the check boxes “Enable Docker,” “Configure for HTTPS,” and “Enable Open API Support” are unchecked as we won’t be using any of those features here.
  11. Click Create.

We’ll use this ASP.NET Core 7 Web API project to work with EF Core query types in the subsequent sections of this article.

Working with query types in ASP.NET Core 7

Let’s start by creating some entities that we can query. We’ll use the following two classes, Teacher and Batch, in our example.

 
    public class Teacher
    
        public int Id  get; set; 
        public string FirstName  get; set; 
        public string LastName  get; set; 
        public ICollection<Batch> Batches  get; set; 
    
    public class Batch
    
        public int Id  get; set; 
        public string Title  get; set; 
        public int NoOfStudents  get; set; 
        public int TeacherId  get; set; 
    

Create a view in your database

Now create a view named BatchDetails in your database with the following code. We’ll use query types to map to this view.

 
Create View BatchDetails AS
Select t.FirstName, t.LastName, t.BatchTitle, t.NoOfStudents as Total_Students
From Teacher t
Join Batch b on b.Id = t.Id

We will also need a class that can be used to store the data retrieved from the view we just created. The following code snippet illustrates how you can create a class named BatchDetails to store the data queried from the view.

 
public class BatchDetails
    
        public string FirstName  get; set; 
        public string LastName  get; set; 
        public string Title  get; set; 
        public int NoOfStudents  get; set; 
    

Configure the query type in EF Core

You have two ways to configure the query type. If you want to refrain from cluttering your DbContext, you can create your DbContext class as a partial class and then split the DbQuery declaration into a separate file altogether.

Here is the content of the DemoDbContext.cs file:

 
public partial class DemoDbContext : DbContext
    
        public DbSet<Teacher> Teachers  get; set; 
        public DbSet<Batch> Batches  get; set; 
    

And here is the content of the DemoDbContextQuery.cs file:

 
public partial class DemoDbContext : DbContext
    
        public DbQuery<BatchDetails> Batches  get; set; 
    

You should configure the query type in the OnModelCreating method as shown in the code snippet given below.

 
protected override void OnModelCreating(ModelBuilder modelBuilder)

    modelBuilder.Query<BatchDetails>().ToView("BatchDetails");

Create a repository in ASP.NET Core

We’ll now create a repository to read data from the database. Note that, while the repository will interact with the database directly, the controller will use the repository to get data. (We’ll implement the controller in the next section.)

Create a file named IBatchRepository.cs with the following code. IBatchRepository will serve as the interface for our BatchDetailsRepository.

 
public interface IBatchDetailsRepository
    
        public List<BatchDetails> GetBatchDetails();
    

Create another file named BatchDetailsRepository.cs and enter the following code to create the repository class.

 
    public class BatchDetailsRepository: IBatchDetailsRepository
    
        private DemoDbContext dbContext;
        public BatchDetailsRepository(DemoDbContext demoDbContext)
        
            dbContext = demoDbContext;
        
        public List<BatchDetails> GetBatchDetails()
        
            return dbContext.BatchDetails.ToList();
        
    

Create an API controller in ASP.NET Core

Now, create an API controller named BatchDetailsController in a file with the same name and a .cs extension. Then write the following code in there.

 
    [Route("api/[controller]")]
    [ApiController]
    public class BatchDetailsController : ControllerBase
    
        private IBatchDetailsRepository _batchDetailsRepository;
        public BatchDetailsController(IBatchDetailsRepository
        batchDetailsRepository)
        
            _batchDetailsRepository = batchDetailsRepository;
        
        [HttpGet]
        public IActionResult Get()
        
            return Ok(_batchDetailsRepository.GetBatchDetails());
        
    

Refer to the preceding code listing. Note how dependency injection has been used to inject an instance of type IBatchDetailsRepository in the constructor of the BatchDetailsController class.

You can return query types from raw SQL queries using the FromSql method in the DbQuery type and they can participate in relationships as well.

Finally, there are two limitations of query types you should keep in mind. As mentioned above, because query types cannot be tracked by the context, you can only use them for reads, not writes. And you cannot use the Add and Attach methods of the DbContext when working with query types.

Copyright © 2022 IDG Communications, Inc.

Leave a Reply