Entity Framework Core stored procedure with input parameter and return custom model

aspnetboilerplate c# entity-framework-core stored-procedures

Question

I am trying to return a list of students from the stored procedure. I am using ASP.NET boilerplate which uses Entity Framework Core 2.2.x.

I am not sure how to execute stored procedure here. I tried to follow How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

but I get an error:

DatabaseFacade' does not contain a definition for 'SqlQuery' and no extension method 'SqlQuery' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)

My goal is to pass a parameter to stored procedure and return a custom model (not the dbset entity)

namespace StudentAPI.Entities
{
    public class Student
    {
        [Key]
        public long StudentId { get; set; }
        public string Name { get; set; }
    }
}

public class ApplicationContext : DbContext
{
    public ApplicationContext(DbContextOptions<ApplicationContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Student> Students { get; set; }
}

namespace StudentAPI.Models
{
    public class Student
    {
        public long StudentId { get; set; }
        public string Name { get; set; }
        public int Marks { get; set; }
    }
}

ALTER PROCEDURE [dbo].[uspGetStudents] 
    (@studentId INT = NULL) 
AS
BEGIN
    SELECT [StudentId], [Name] 
    FROM [dbo].[Students] 
    WHERE (@studentId IS NULL OR StudentId = @studentId)
END;


public async Task<Response<IList<Models.Student>>> GetStudentsAsync()
{
    try
    {
        var studentId = new SqlParameter("@studentId", 1);
        var result = _context.Database.SqlQuery<Models.Student>("dbo.uspGetStudents @studentId", studentId).ToList();
        .
        .
        .
    }
    catch (Exception e)
    {
    }

    return null;
}

I tried to update code to

_context.Set<Models.Student>().FromSql($"dbo.uspGetStudents @studentId= {1}").ToList();

but then I get this error:

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

I understand can use context entity which is Entities.Student but this stored procedure will do multiple joins and will return a custom model Models.Student.

How do I achieve this?

1
0
4/10/2020 10:24:16 AM

Accepted Answer

You can utilize the Query Types introduced in EF Core 2.1.

First you need to register you class as query type:

modelBuilder.Query<ClaimDataView>();

Then you can use Context.Query<ClaimDataView>() in place of your current Context.Claims:

var query = Context.Query<ClaimDataView>().FromSql(...);

Update (EF Core 3.x+):

Starting with EF Core 3.0, query types have been consolidated with entity types and renamed to Keyless Entity Types, so the corresponding code is

modelBuilder.Entity<ClaimDataView>().HasNoKey().ToView(null);

and

var query = Context.Set<ClaimDataView>().FromSql(...);
22
4/2/2020 12:35:12 PM

Popular Answer

If you are not on version 2.1, you will need to add:

public DbSet<ClaimDataView> ClaimDataView { get; set; }

to your moduleContext. And add NotMapped to your class:

[NotMapped]
public class ClaimDataView


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow