Come chiamare Stored Procedure con join su più tabelle in Entity Framework Core?

Question

Come chiamare Stored Procedure con join su più tabelle in Entity Framework Core?

asp.net-core c# ef-code-first entity-framework entity-framework-core

Domanda

Devo chiamare una procedura memorizzata che seleziona i record da più tabelle.

Ho provato il seguente codice, ma restituisce null per le colonne di altre tabelle rispetto alla classe di entità.

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

Procedura memorizzata:

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

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

}

Quindi quando chiamo questa stored procedure, Table1Code e Table2Code mancano nel valore restituito.

Ho provato ad aggiungere il seguente codice nella classe MainTable , ma non funziona.

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

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

Quindi ho rimosso [NotMapped] da entrambe le proprietà e la migrazione aggiunta, in questo caso, restituendo il valore corretto. Ma aggiungerà due colonne in MainTable. È davvero un disegno BAD .

Quindi la mia domanda è come selezionare le colonne da più tabelle nella stored procedure in Entity Framework Core.

Sto usando EF Core 2.0.

Penso che ci sia un modo per chiamare la stored procedure usando Entity e quindi associarlo a qualsiasi classe perché selezionare colonne da più tabelle usando join è un requisito molto basilare.

Ho provato la soluzione simile, ma il suo errore di compilazione.

'DatabaseFacade' non contiene una definizione per 'SqlQuery' e non è possibile trovare alcun metodo di estensione 'SqlQuery' che accetta un primo argomento di tipo 'DatabaseFacade' (ti manca una direttiva using o un riferimento assembly?)

vivek nuna

Risposta popolare

Ecco come funziona in EF Core 2.1:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Query<YourModel>();
}

SqlParameter value1Input = new SqlParameter("@Param1", value1 ?? (object)DBNull.Value);
SqlParameter value2Input = new SqlParameter("@Param2", value2 ?? (object)DBNull.Value);

List<YourModel> result;
using (var db = new MyDbContext(_options))
{
    result = await db.Query<YourModel>().FromSql("STORED_PROCEDURE @Param1, @Param2", value1Input, value2Input).ToListAsync();
}

fonte

Der_Meister

Vedi di più su Stack Overflow

vivek · Accepted Answer · 2/3/2018 11:15:33 AM

L'idea completa per ottenere i dati da una stored procedure è la seguente:

È necessario aggiungere un'entità che abbia le stesse proprietà delle procedure selezionate da una query.
Aggiungi l'entità al tuo DbContext e crea una migrazione. Modificare il codice nei metodi Up() e Down() della migrazione in modo che crei la procedura nel database.
Ora usa il metodo FromSql() per ottenere i dati da dati normali.

Ecco un codice che può guidarti. Supponiamo che tu abbia queste entità nel dominio dell'applicazione:

Alunno
Genitore
Lezione
Sezione
Iscrizione

Migrazioni su metodo

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

Metodo di migrazione verso il basso

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

L'entità falsa: tutte le proprietà in questa entità provengono dalle entità sopra indicate. Puoi chiamarlo un'entità falsa.

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

Livello di servizio per ottenere dati

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

Come chiamare Stored Procedure con join su più tabelle in Entity Framework Core?

Domanda

Fastest Entity Framework Extensions

Risposta accettata

Risposta popolare

Performance

Expression Evaluator

Related