如何在Entity Framework Core中通过联接多个表来调用存储过程? [英] How to call Stored Procedure with join on multiple tables in Entity Framework Core?

问题描述

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

I have to call a stored procedure which is selecting records from multiple tables.

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

I have tried the following code, but it's returning null for columns from other tables than the entity class.

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 .

So when I call this stored procedure, Table1Code and Table2Code are missing in the return value.

我试图在 MainTable 类中添加以下代码，但它也无法正常工作.

I tried to add the following code in MainTable class, but its also not working.

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

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

然后，我从两个属性中都删除了 [NotMapped] ，并添加了迁移，在这种情况下，返回的是正确的值.但是它将在MainTable中添加两列.这实际上是 BAD 设计.

Then I removed [NotMapped] from both the properties and added migration, in this case, its returning proper value. But It will add two columns in MainTable. It's really a BAD design.

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

So my question is how to select columns from multiple tables in the stored procedure in Entity Framework Core.

我正在使用EF Core 2.0.

I'm using EF Core 2.0.

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

I think there has to be some way to call the stored procedure with using Entity and then map it to any class because select columns from multiple tables using join is a very basic requirement.

我尝试了类似的解决方案，但是它给出了编译错误.

I tried the similar solution, but its giving compilation error.

"DatabaseFacade"不包含"SqlQuery"的定义，并且没有扩展方法'SqlQuery'接受类型的第一个参数可以找到"DatabaseFacade"(您是否缺少using指令或组装参考?)

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

推荐答案

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

The complete idea to get data from a stored procedure is as follows:

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

1. You need to add an entity that has the same properties as the procedures select query has.
2. Add the entity to your DbContext and Create a migration. Change the code in the Up() and Down() methods of the migration so that it creates the procedure in the database.
3. Now use the FromSql() method to get the data a normal entity data.

以下是一些可以指导您的代码.假设您在应用程序域中有这些实体:

Here is some code that can guide you. Suppose you have these entities in your application domain:

1. 学生
2. 父母
3. 学校课程
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);
            });
    }

假实体:该实体中的所有属性均来自上述实体.您可以称其为假实体.

The fake entity: All properties in this entity are coming from the above-said entities. You can call it a fake entity.

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

这篇关于如何在Entity Framework Core中通过联接多个表来调用存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！