具有自定义返回类型的实体框架核心
我正在尝试使用EF Core 3.1从Sql Server查询自定义返回类型。有两个实体Book和Author,我想获取具有BookId,BookName,AuthorName的BookInfo。这是下面的代码。
存储过程:
```js
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; }
}
}
作者.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 Core中的内部联接来做到这一点。但是,我试图从具有自定义返回类型的存储过程中获取数据。
您能指导我解决此问题吗?
错误:
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}");
}
}
}
}
}
您应该为BookInfo使用“ DbQuery”而不是“ DbSet”
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=***********");
}
}
}
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。