c# - 如何在 Entity Framework Core 中调用带有多个表联接的存储过程？

Question

我必须调用一个从多个表中选择记录的存储过程。

我尝试了以下代码，但它为除实体类之外的其他表中的列返回 null。

private async Task<IEnumerable<TEntity>> InvokeStoredProcedureAsync(string input = "")
{
    var storedProcedureName = "sp_BulkSelect";

    using (var db = new MyDbContext(_options))
    {
        var result = await db.Set<TEntity>().FromSql(storedProcedureName + " @inputIds", new SqlParameter("inputIds", input)).ToListAsync();
        return result;
    }
}

存储过程:

SELECT 
    [MainTable].[Id],
    [Table1Id],
    [Table2Id],
    [MainTable].[Table1Code],
    [Table2].[Table2Code]
FROM
    [MainTable] [MainTable]
LEFT JOIN 
    [Table1] [Table1] ON [MainTable].Table1Id = [Table1].[Id]
LEFT JOIN 
    [Table2] [Table2] ON [MainTable].[Table2Id] = [Table2].[Id];

MainTable 类:

[Table("MainTable")]
public class MainTable : FullAuditedEntity
{

    [ForeignKey("Table1Id")]
    public virtual Table1 Table1 { get; set; }
    public virtual int Table1Id { get; set; }

    [ForeignKey("Table2Id")]
    public virtual Table2 Table2 { get; set; }
    public virtual int? Table2Id { get; set; }      

}

因此，当我调用此存储过程时，返回值中缺少 Table1Code 和 Table2Code。

我尝试在 MainTable 类中添加以下代码，但它也不起作用。

[NotMapped]
public virtual string Table2Code { get; set; }

[NotMapped]
public virtual string Table1Code { get; set; }

然后我从这两个属性中删除了 [NotMapped] 并添加了迁移，在本例中，它返回正确的值。但它会在 MainTable 中添加两列。 这确实是一个糟糕设计。

所以我的问题是如何从 Entity Framework Core 中的存储过程中的多个表中选择列。

我正在使用 EF Core 2.0。

我认为必须有某种方法使用 Entity 调用存储过程，然后将其映射到任何类，因为使用联接从多个表中选择列是一个非常基本的要求。

我尝试了类似的solution ，但它给出编译错误。

'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?)

Answer 1

从存储过程获取数据的完整思路如下:

1. 您需要添加一个与过程选择查询具有相同属性的实体。
2. 将实体添加到您的 DbContext 并创建迁移。更改迁移的 Up() 和 Down() 方法中的代码，以便在数据库中创建过程。
3. 现在使用FromSql()方法获取普通实体数据。

这里有一些可以指导您的代码。假设您的应用程序域中有这些实体:

1. 学生
2. 家长
3. 学校类(class)
4. 部分
5. 注册

向上迁移方法

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;
    }