从存储过程获取数据的完整思路如下:
- 您需要添加一个与过程选择查询具有相同属性的实体。
- 将实体添加到您的
DbContext
并创建迁移。更改中的代码Up()
and Down()
迁移的方法,以便在数据库中创建过程。
- 现在使用
FromSql()
方法获取正常实体数据的数据。
这是一些可以指导您的代码。假设您的应用程序域中有以下实体:
- Student
- Parent
- 学校班级
- Section
- 注册
向上迁移方法
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "StudentDetails");
migrationBuilder.Sql(
@"create proc GetStudentDetail
@ssid int,
@sectionId int = null
as
select Id, name, Gender, RollNumber, Status, Type,
FatherName, FatherContact, SchoolClass, Section,
SsId, SectionId, EnrollmentId
from
(
SELECT stu.Id, stu.name, stu.Gender, en.RollNumber, en.Status, en.Type,
p.FatherName, p.FatherContact, sc.Name as SchoolClass, sec.Name as Section,
ss.SessionId as SsId, sec.Id as SectionId, en.Id as EnrollmentId,
en.EntryDate, row_number() over (partition by studentid order by en.entrydate desc) as rowno
from SchoolSessions ss
join SchoolClasses sc on ss.SessionId = sc.ssid
join Sections sec on sc.Id = sec.ClassId
join Enrollments en on sec.id = en.SectionId
join Students stu on en.StudentId = stu.Id
join parents p on stu.ParentId = p.Id
where ss.SessionId = @ssid
) A
where rowno = 1 and
(SectionId = @sectionId or @sectionId is null)"
);
}
向下迁移方法
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("drop proc GetStudentDetail");
migrationBuilder.CreateTable(
name: "StudentDetails",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
EnrollmentId = table.Column<int>(nullable: false),
FatherContact = table.Column<string>(nullable: true),
FatherName = table.Column<string>(nullable: true),
Gender = table.Column<int>(nullable: false),
Name = table.Column<string>(nullable: true),
RollNumber = table.Column<string>(nullable: true),
SchoolClass = table.Column<string>(nullable: true),
Section = table.Column<string>(nullable: true),
SectionId = table.Column<int>(nullable: false),
SsId = table.Column<int>(nullable: false),
Status = table.Column<int>(nullable: false),
Type = table.Column<int>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_StudentDetails", x => x.Id);
});
}
假实体:该实体中的所有属性均来自上述实体。你可以称其为假实体。
public class StudentDetail
{
public int Id { get; set; }
public string Name { get; set; }
public Gender Gender { get; set; }
public string RollNumber { get; set; }
public StudentStatus Status { get; set; }
public StudentType Type { get; set; }
public string FatherName { get; set; }
public string FatherContact { get; set; }
public string SchoolClass { get; set; }
public string Section { get; set; }
public int SsId { get; set; }
public int SectionId { get; set; }
public int EnrollmentId { get; set; }
}
服务层获取数据
public IEnumerable<StudentDetail> GetStudentDetails(int ssid)
{
var ssidParam = new SqlParameter("@ssid", ssid);
var result = _appDbContext.StudentDetails.FromSql("exec GetStudentDetail @ssid", ssidParam).AsNoTracking().ToList();
return result;
}