C#, Entity framework core - returning object from SP made from several tables-CodePudding

I need to execute SP, I need to call SP by SP name and get back object and returning value. My problem that my object related to several tables in DB and I cannot use: Context.ExampleTable.FromSQl(...), because it's returning values only from ExampleTable, however I need to get object which having fields from 3 separated tables and also my SP returning value. Someone could help me? I'm using EntityFrameworkCore 2.2.6

i was trying to use Context.ExampleTable.FromSQl(...), but it's not what I need.

----Edited----

SqlParameter returnVal = new SqlParameter("@return", SqlDbType.Int);
        returnVal.Direction = ParameterDirection.ReturnValue;

        using (var context = new Context())
        {
            var test = context.Set<RequiredObject>().FromSql("EXEC SP_Name @return", returnVal);
        }

CodePudding user response：

EF Core 2.2.6 is out of support.

But you can define a new class that represents your result shape, and add that as a DbSet to your DbContext, and use the new class with FromSQL.

CodePudding user response：

Great question! Unfotunately, it seems that EF Core does not have this possibility.

Tried this:

await db.Database.SqlQueryRaw<SomeComplexModel>("select * from Receivers inner join ... ").ToListAsync()

and this:

public DbSet<SomeComplexModel> Items { get; set; }
[NotMapped] public class SomeComplexModel { ... }
await db.Items.FromSqlRaw("select * from Receivers inner join ... ").ToListAsync();

Both failed with exceptions...

https://learn.microsoft.com/en-us/ef/core/querying/sql-queries "The SQL query can't contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data)."

What you definitely could do (I believe that's not the best way, may be someone would suggest option better): additionally install in the application some micro-ORM f.e. Dapper, share same db connection string and in this rare case when multiple related entities have to be queried via pure SQL - utilize not EF, but this ORM