C# Entity Framework Core : how to execute stored procedure and get a list of custom data?

Question

I am trying to call a stored procedure in C# with EF Core. Just returning custom result set which is not linked to any entity.

But I am getting an error:

Cannot create a DbSet for 'ExCoResponse' because this type is not included in the model for the context.

Here is my method:

public async Task<IEnumerable<ExCoResponse>> UpdateAndGetExcoUsers()
{
    return await _context
                     .Query<ExCoResponse>()
                     .FromSql("[dbo].[UsersUpdateExcoDetail]")
                     .ToListAsync();
}

Does this answer your question? [Raw SQL Query without DbSet - Entity Framework Core](https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core) — Nick Bailey, May 07 '22 at 17:54
Entity framework core doesn't support mapping to arbitrary types. You must define the result as either a query type or an entity type. — Nick Bailey, May 07 '22 at 17:55
How to define a query type? can you please provide me some example? Thanks — Mukil Deepthi, May 07 '22 at 17:56
I get error saying 'DbSet' does not contain definition for ''FromSqlRaw' — Mukil Deepthi, May 07 '22 at 18:00
Install EF Core Power Tools VS extension and get it to generate your context instead. It includes ability to generate for stored procedures, functions , views etc — Caius Jard, May 07 '22 at 19:41
For simplest and most complete answer, check this out: https://stackoverflow.com/a/75465142/8644294 — Ash K, Feb 15 '23 at 20:37

score 0 · Answer 1 · answered May 07 '22 at 18:16

0

Since i am using Core 2.1 Following worked for me:

public DbQuery<ExcoUser> ExcoUsers { get; set; }

        var result = await _context
            .ExcoUsers.FromSql<ExcoUser>("EXEC [dbo].[UsersUpdateExcoDetail]")
            .ToListAsync();

answered May 07 '22 at 18:16

Mukil Deepthi

6,072
13
71
156

score 0 · Answer 2 · answered May 07 '22 at 19:06

dot net core 3.0+

1- Install Microsoft.EntityFrameworkCore.Relational package

2 - Add Entity and configuration to DbContext Model

public class ApplicationDBContext : DbContext
{
    public DbSet<ExCoResponse> ExCoResponses { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<ExCoResponse>().HasNoKey();
    } // end OnModelCreating
} // end ApplicationDBContext

3- use this query. be careful have used Microsoft.EntityFrameworkCore;

    var result = dBContext
            .Set<ExCoResponse>()
            .FromSqlRaw("exec [dbo].[UsersUpdateExcoDetail]").ToList();

C# Entity Framework Core : how to execute stored procedure and get a list of custom data?

2 Answers2

dot net core 3.0+