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?
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(...);
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