Find out how to use EF Core question varieties in ASP.NET Core 7

Entity Framework Core (EF Core for brief) is a well-liked ORM (object-relational mapper) from Microsoft that can help you carry out CRUD operations (create, learn, replace, and delete) with out having to know the way the info is continued within the underlying database.

When working with ORMs, we frequently leverage fashions which are mapped to database tables. Nonetheless, what if we have now a mannequin that doesn’t mimic a database desk? How can we map non-entity varieties and populate objects of such a mannequin in our purposes? We are able to accomplish this with question varieties.

Initially launched in EF Core 2.1, question varieties are non-entity varieties (courses) that may map to tables or views within the database with out an identification column specified, that means tables and views that lack a key. EF Core question varieties make it easier to question views and mannequin varieties that don’t require identification columns. Nonetheless, as a result of question varieties don’t have an identification column outlined, you can not insert, replace, or delete knowledge utilizing them. You should use them solely to retrieve the info.

Question varieties can help you specify a mapping between a database question and your area courses. You’ll be able to then use the identical question kind with various kinds of EF Core queries, corresponding to LINQ to Entities or EF Core Migrations. 

This text discusses how we will use EF Core question varieties in ASP.NET Core 7 purposes. To work with the code examples offered on this article, you must have Visible Studio 2022 Preview put in in your system. When you don’t have already got a duplicate, you’ll be able to obtain Visible Studio 2022 right here.

Create an ASP.NET Core 7 Internet API undertaking in Visible Studio 2022

First off, let’s create an ASP.NET Core Internet API undertaking in Visible Studio 2022. Following these steps will create a brand new ASP.NET Core Internet API undertaking in Visible Studio 2022:

1. Launch the Visible Studio 2022 IDE.
2. Click on on “Create new undertaking.”
3. Within the “Create new undertaking” window, choose “ASP.NET Core Internet API” from the listing of templates displayed.
4. Click on Subsequent.
5. Within the “Configure your new undertaking” window, specify the identify and placement for the brand new undertaking.
6. Optionally verify the “Place answer and undertaking in the identical listing” verify field, relying in your preferences.
7. Click on Subsequent.
8. Within the “Further Data” window proven subsequent, underneath Framework, choose .NET 7.0.
9. Depart the verify field that claims “Use controllers…” checked since we’ll be utilizing controllers on this instance. Depart the “Authentication Kind” set to “None” (default).
10. Be sure that the verify packing containers “Allow Docker,” “Configure for HTTPS,” and “Allow Open API Help” are unchecked as we received’t be utilizing any of these options right here.
11. Click on Create.

We’ll use this ASP.NET Core 7 Internet API undertaking to work with EF Core question varieties within the subsequent sections of this text.

Working with question varieties in ASP.NET Core 7

Let’s begin by creating some entities that we will question. We’ll use the next two courses, Instructor and Batch, in our instance.

 
    public class Instructor
    {
        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 next code. We’ll use question varieties to map to this view. 

 
Create View BatchDetails AS
Choose t.FirstName, t.LastName, t.BatchTitle, t.NoOfStudents as Total_Students
From Instructor t
Be part of Batch b on b.Id = t.Id

We will even want a category that can be utilized to retailer the info retrieved from the view we simply created. The next code snippet illustrates how one can create a category named BatchDetails to retailer the info 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 question kind in EF Core

You’ve two methods to configure the question kind. If you wish to chorus from cluttering your DbContext, you’ll be able to create your DbContext class as a partial class after which cut up the DbQuery declaration right into a separate file altogether.

Right here is the content material of the DemoDbContext.cs file:

 
public partial class DemoDbContext : DbContext
    {
        public DbSet<Instructor> Academics { get; set; }
        public DbSet<Batch> Batches { get; set; }
    }

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

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

It’s best to configure the question kind within the OnModelCreating technique as proven within the code snippet given beneath.

 
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Question<BatchDetails>().ToView("BatchDetails");
}

Create a repository in ASP.NET Core

We’ll now create a repository to learn knowledge from the database. Be aware that, whereas the repository will work together with the database straight, the controller will use the repository to get knowledge. (We’ll implement the controller within the subsequent part.) 

Create a file named IBatchRepository.cs with the next code. IBatchRepository will function the interface for our BatchDetailsRepository.

 
public interface IBatchDetailsRepository
    {
        public Record<BatchDetails> GetBatchDetails();
    }

Create one other file named BatchDetailsRepository.cs and enter the next code to create the repository class.

 
    public class BatchDetailsRepository: IBatchDetailsRepository
    {
        non-public DemoDbContext dbContext;
        public BatchDetailsRepository(DemoDbContext demoDbContext)
        {
            dbContext = demoDbContext;
        }
        public Record<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 identical identify and a .cs extension. Then write the next code in there.

 
    [Route("api/[controller]")]
    [ApiController]
    public class BatchDetailsController : ControllerBase
    {
        non-public IBatchDetailsRepository _batchDetailsRepository;
        public BatchDetailsController(IBatchDetailsRepository
        batchDetailsRepository)
        {
            _batchDetailsRepository = batchDetailsRepository;
        }
        [HttpGet]
        public IActionResult Get()
        {
            return Okay(_batchDetailsRepository.GetBatchDetails());
        }
    }

Seek advice from the previous code itemizing. Be aware how dependency injection has been used to inject an occasion of kind IBatchDetailsRepository within the constructor of the BatchDetailsController class.

You’ll be able to return question varieties from uncooked SQL queries utilizing the FromSql technique within the DbQuery kind they usually can take part in relationships as nicely.

Lastly, there are two limitations of question varieties you must be mindful. As talked about above, as a result of question varieties can’t be tracked by the context, you’ll be able to solely use them for reads, not writes. And you can not use the Add and Connect strategies of the DbContext when working with question varieties.