Entity Core Framework Query Types (2024)

By: Joydip Kanjilal |Comments (1) | Related: > Entity Framework

Problem

Entity Framework Core (EF Core) is a lightweight, open source, cross platform and extensible version of Microsoft's popular ORM Entity Framework.EF Core is light-weight and runs on top of the .NET Core runtime. While workingon ORMs we often use models that are mapped to database tables. What if you needa model that doesn't necessarily represent a database table? How do you querydata and populate instances of such models in your application? Here's exactlywhere the Query Type comes to the rescue. Query Type is a new feature added in EF Core2.1. This article presents a discussion on how we can work with query types in EntityFramework Core.

Solution

Entity Framework Core provides support for query types. Query types are read-only,non-entity types that can be used to map to non-entity types or used as the return typefrom the FromSql() method. Support for query type was first introduced in EF Core2.1. In this article we'll examine how we can work with query types in EFCore. Before we delve into this, let's get the pre-requisites ready.

Getting Started

Follow the steps outlined in one ofmy previous articles to create a console application projectthen download and restore the AdventureWorksdatabase.

You should also run the following commands at the Package Manager Console toinstall the necessary packages:

• Install-Package Microsoft.EntityFrameworkCore
• Install-PackageMicrosoft.EntityFrameworkCore.Tools
• Install-Package Microsoft.EntityFrameworkCore.SqlServer

So far so good. Next, you should create the necessary database tables we'llbe using in this example. We'll be using the following two tables:

• Author
• Book

Accordingly, we will have model classes named Author and Book that map to thesedatabase tables. We'll create these classes shortly. Here's the scriptfor creating the Author and the Book database tables:

CREATE TABLE [dbo].[Author]( [AuthorId] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [LastName] [nvarchar](50) NOT NULL, CONSTRAINT [PK_AuthorId] PRIMARY KEY CLUSTERED ( [AuthorId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO CREATE TABLE [dbo].[Book]( [BookId] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](50) NOT NULL, [AuthorId] [int] NOT NULL, CONSTRAINT [PK_BookId] PRIMARY KEY CLUSTERED ( [BookId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO ALTER TABLE [dbo].[Book] WITH NOCHECK ADD CONSTRAINT [FK_Book_Author] FOREIGN KEY([AuthorId])REFERENCES [dbo].[Author] ([AuthorId])GO

So far so good. The next step is to create the Entity Data Model. Incidentally,the Entity Data Model is an extended Entity Relationship model used to present theconceptual model of the data. To do this, you can take advantage of the Scaffold-DbContextcommand. Here's how the syntax of the Scaffold-DbContext command looks.

Now create the Entity Data Model for your project by executing the followingcommand at the Package Manager Console.

Scaffold-DbContext "Server=JOYDIP;Database=AdventureWorks2017;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

If the command has executed successfully, the entity classes and the DbContextclass will be generated and created in the project.

Where should I use Query types?

Query types can be used in any of the following scenarios:

• As adhoc types returned fromthe FromSql() method
• When mappingto queries that are defined in the model
• Mapping toviews in the database
• Mapping to database tables thatdon't have an identity column

Query types are essentially read-only objects and don't participate inany of the following operations:

• Add, Update of Delete
• Change tracking

Query types vs Entity types

Here's the list of the similarities of query types and entity types.

• Both query types and entitytypes can be added to the model using the OnModelCreating method.
• Both support inheritance mappingand navigation properties.

However, there are subtle differences between query types and entity types aswell. These include the following:

• Unlike entity types, query typesdon't need a key
• Query typesare never tracked for changes
• While entitytypes are mapped to the database objects using DbSet<T>, Query types are mappedusing DbQuery<T>
• Query types are never discoveredby convention

Working with Query Type

A query type is part of the conceptual model and should be mapped to a tableor a view in the database. Let's understand this with an example.

Consider the following two classes, namely Author and Book.

public class Author{ public int AuthorId { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public ICollection<Book> Books { get; set; }}public class Book{ public int BookId { get; set; } public string Title { get; set; } public int AuthorId { get; set; }}

Next, create the following view in the database. This view returns the numberof books written by each author.

Create View AuthorBookCount AS Select a.FirstName, a.LastName, Count(b.BookId) as NumberOfBooks From Author aJoin Book b on b.AuthorId = a.AuthorId

We need a class of an instance which would be used to hold the result from theview created earlier.

public class AuthorBook{ public string FirstName { get; set; } public string LastName { get; set; } public int NumberOfBooks { get; set; }}

The query type should be configured in the OnModelCreating method as shown below.Note that unlike an entity type, you would need the DbQuery class for includingquery types in the DbContext. The ToView method is used to specify the source ofthe data (table name or view name) for the query type.

protected override void OnModelCreating(ModelBuilder modelBuilder){ modelBuilder.Query<AuthorBook>().ToView("AuthorBookCount");}

The DbContext should also be configured to include the query type. Here'show this can be achieved.

public DbQuery<AuthorBook> AuthorBooks { get; set; }

So far so good. You can now write your code to query the view created earlier.

var authorBookCounts = dbContext.AuthorBooks.ToList();foreach (var authorBookCount in authorBookCounts){ String authorName = authorBookCount.FirstName + “\t“ + authorBookCount.LastName; Console.WriteLine($"The author { authorName } has written {authorBookCount.NumberOfBooks} books.");}

Next Steps

• Query types in EF Core can be used to represent the results of arbitraryqueries. They enable projecting SQL queries into types that aren't mappedto a database table.
• Check out the previousentity framework tips.

About the author