带有自定义返回类型的实体框架核心-无法为“BookInfo”创建BookInfo，因为该类型不包含在上下文的模型中

Question

具有自定义返回类型的实体框架

我试图使用EFCore3.1从Server查询自定义返回类型。有两个实体图书和作者，我想得到的BookInfo有BookId，BookName，AuthorName。这是以下代码。

存储过程：

CREATE PROC GetBookInfos
AS
SELECT Books.BookId, Books.Name AS BookName, Authors.FirstName+' '+Authors.LastName AS AuthorName
FROM Books
INNER JOIN Authors
ON Books.AuthorId = Authors.AuthorId;

Book.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreShop
{
    public class Book
    {
        public long BookId { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public long AuthorId { get; set; }
        public Author Author { get; set; }
    }
}

Author.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreShop
{
    public class Author
    {
        public long AuthorId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public List<Book> Books { get; set; }
    }
}

BookInfo.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace EFCoreShop
{
    [NotMapped]
    public class BookInfo
    {
        public long BookId { get; set; }
        public string BookName { get; set; }
        public string AuthorName { get; set; }
    }
}

ShopContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreShop
{
    class ShopContext:DbContext
    {

        public DbSet<Author> Authors { get; set; }
        public DbSet<Book> Books { get; set; }
        public DbSet<BookInfo> BookInfos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=**********;Database=EFCoreShop;User ID=*********;Password=***********");
        }


    }
}

Program.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EFCoreShop
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ShopContext shopContext = new ShopContext())
            {
                IEnumerable<BookInfo> bookInfos = shopContext.BookInfos.FromSqlRaw("exec GetBookInfos").ToList<BookInfo>();

                foreach (var bookInfo in bookInfos)
                {
                    Console.WriteLine($"Book Id: {bookInfo.BookId} Book Name: {bookInfo.BookName} Author Name: {bookInfo.AuthorName}");
                } 
            }
        }
    }
}

我想我做了一切好事。我试图从数据库中返回一个自定义类型，并且已经有了存储过程。我也不希望实体被映射。

注:我知道我可以在EF核心中使用内部连接。但是，我试图从带有自定义返回类型的存储过程中获取数据。

你能指导我解决这个问题吗？

错误：

Unhandled exception. System.InvalidOperationException: Cannot create a DbSet for 'BookInfo' because this type is not included in the model for the context.
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityType()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.CheckState()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityQueryable()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlRaw[TEntity](DbSet`1 source, String sql, Object[] parameters)
   at EFCoreShop.Program.Main(String[] args) in D:\Workspace\Learning\DotNet\EFCoreShop\EFCoreShop\Program.cs:line 14

更新：

下面的代码可以在不使用存储过程的情况下解决问题。但是，我们需要使用存储过程的结果。但是，如果不想使用存储过程，也可以这样做。

Program.cs (没有存储过程，不是我想要的)

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;

namespace EFCoreShop
{
    class Program
    {
        static void Main(string[] args)
        {
            using (ShopContext shopContext = new ShopContext())
            {
                //IEnumerable<BookInfo> bookInfos = shopContext.BookInfos.FromSqlRaw("exec GetBookInfos").ToList<BookInfo>();

                //foreach (var bookInfo in bookInfos)
                //{
                //    Console.WriteLine($"Book Id: {bookInfo.BookId} Book Name: {bookInfo.BookName} Author Name: {bookInfo.AuthorName}");
                //}

                // This is more manageable than stored procedure
                IEnumerable<Book> books = shopContext.Books.Include( book => book.Author ).ToList();

                foreach (var book in books)
                {
                    Console.WriteLine($"Book Id: {book.BookId} Book Name: {book.Name} Author Name: {book.Author.FirstName+' '+book.Author.LastName}");
                }

            }
        }
    }
}

Answer 1

你应该用“DbQuery”代替“DbSet”来表示BookInfo

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreShop
{
    class ShopContext:DbContext
    {

        public DbSet<Author> Authors { get; set; }
        public DbSet<Book> Books { get; set; }
        public DbQuery<BookInfo> BookInfos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=**********;Database=EFCoreShop;User ID=*********;Password=***********");
        }


    }
}