Can I get return type from stored procedure without creating DbSet in EF Core?

userz1M 0
Viewed 77

Supposedly, I have a simple DbContext with Blog and Post models:

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }
    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

Let's say I have a stored procedure that returns some DTO:

[Keyless]
public class BlogPostDto
{
    public string PostTitle { get; init; }
    public string BlogName { get; init; }
}

Today I put the following into DbContext:

public class AppDbContext : DbContext {
    public virtual DbSet<BlogPostDto> NeverUseIt { get; set; }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder) {
        modelBuilder.Entity<BlogPostDto>().ToView(null);
    }
}

And then I can get Stored Procedure results shaped in the way I want:

List<BlogPostDto> results = await db.Set<BlogPostDto>().FromSqlRaw($"EXEC MyProc").ToListAsync();

So, my question is, do I have to add my BlogPostDto into DbContext? I know that in EF Core 3 I did; but there were a large number of improvements since then. Creating a bogus DbSet and mapping it to non-existent view just feels counter-intuitive!

The closest I found in most current documentation is here. The very first example of context is Serving as the return type for raw SQL queries. - but the article assumes that I have a matching view already in the database.

UPDATE: It looks like ToView(null) is not necessary - just DbSet<>

entity-framework-core entity-framework c#
1 Answers

Nothing has changed in that regard so far from what you see in the EF Core 6.0 documentation and SO posts you are referring to.

Just to be crystal clear, you don't need a DbSet<T> returning property in your context. But you do need to include the type (keyless or not) in the model using the modelBuilder.Entity<T>() call, and also ToView(null) to prevent EF Core migrations associate database table and/or view with it, and optionally HasNoKey() in case you don;t want to use EF Core dependent attributes like [Keyless] in your data classes.

So the minimum requirement for your example is this line

modelBuilder.Entity<BlogPostDto>().ToView(null);

Now, this is a long time requested feature (which exists in the "obsolete" EF6 which the "modern" EF Core is supposed to replace), tracked by Support raw SQL queries without defining an entity type for the result #10753 issue in EF Core issue tracker. It was initially planned to be included in the upcoming EF Core 7.0 release (Nov 2022), but later has been cut for (eventually) EF Core 8.0 (Nov 2023). So until then you have to use the "register model" approach, or use 3rd party library(!) like Dapper for the same task, as suggested by one of the EF Core team members(?!).

Ivan Stoev0
Related