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 working on ORMs we often use models that are mapped to database tables. What if you need a model that doesn't necessarily represent a database table? How do you query data and populate instances of such models in your application? Here's exactly where the Query Type comes to the rescue. Query Type is a new feature added in EF Core 2.1. This article presents a discussion on how we can work with query types in Entity Framework 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 type from the FromSql() method. Support for query type was first introduced in EF Core 2.1. In this article we'll examine how we can work with query types in EF Core. Before we delve into this, let's get the pre-requisites ready.
Getting Started
Follow the steps outlined in one of my previous articles to create a console application project then download and restore the AdventureWorks database.
You should also run the following commands at the Package Manager Console to install the necessary packages:
- Install-Package Microsoft.EntityFrameworkCore
- Install-Package Microsoft.EntityFrameworkCore.Tools
- Install-Package Microsoft.EntityFrameworkCore.SqlServer
So far so good. Next, you should create the necessary database tables we'll be 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 these database tables. We'll create these classes shortly. Here's the script for 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 the conceptual model of the data. To do this, you can take advantage of the Scaffold-DbContext command. Here's how the syntax of the Scaffold-DbContext command looks.
Now create the Entity Data Model for your project by executing the following command 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 DbContext class 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 from the FromSql() method
- When mapping to queries that are defined in the model
- Mapping to views in the database
- Mapping to database tables that don't have an identity column
Query types are essentially read-only objects and don't participate in any 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 entity types can be added to the model using the OnModelCreating method.
- Both support inheritance mapping and navigation properties.
However, there are subtle differences between query types and entity types as well. These include the following:
- Unlike entity types, query types don't need a key
- Query types are never tracked for changes
- While entity types are mapped to the database objects using DbSet<T>, Query types are mapped using DbQuery<T>
- Query types are never discovered by convention
Working with Query Type
A query type is part of the conceptual model and should be mapped to a table or 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 number of books written by each author.
Create View AuthorBookCount AS Select a.FirstName, a.LastName, Count(b.BookId) as NumberOfBooks From Author a Join Book b on b.AuthorId = a.AuthorId
We need a class of an instance which would be used to hold the result from the view 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 including query types in the DbContext. The ToView method is used to specify the source of the 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's how 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 arbitrary queries. They enable projecting SQL queries into types that aren't mapped to a database table.
- Check out the previous entity framework tips.
About the author
Joydip Kanjilal - Microsoft MVP (2007 to 2012), Author and Speaker with more than 20 years of experience. Authored more than 500 articles, 8 books and reviewed a dozen books.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
