EF Core run stored procedure from multiple tables

Question

User-1188570427 posted

Hello,

I'm new to EF core in a sense of stored procedures. I found the code below that will run a stored procedure for me and put it into a list.

What if the stored procedure is pulled from multiple tables?

What would I use for:

context. ?

var blogs = context.Blogs
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

In this case, it pulls from context.Blogs?

Answer 1

User1120430333 posted

What if the stored procedure is pulled from multiple tables?

A sproc is not pulled from multiple tables. The results of an executed sproc can have a result from querying and pulling data from one or more tables.

var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs")
.ToList();

One of the EF dbcontext's job in this situation is to provide an open connection to the database in question, as if you were using an  ADO.NET Connction.Open statement in c#. As long as all the tables the sproc is using and the sproc itself are part of the Blogs database on the database server, then the one context for the Blogs database allows the access of all tables used in the sproc running in the Blogs database. The dbcontext that context is derived from points to a database on the database server.

Answer 2

User-1188570427 posted

What if the stored procedure is pulled from multiple tables?

A sproc is not pulled from multiple tables. The results of an executed sproc can have a result from querying and pulling data from one or more tables.

var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs")
.ToList();

One of the EF dbcontext's job in this situation is to provide an open connection to the database in question, as if you were using an  ADO.NET Connction.Open statement in c#. As long as all the tables the sproc is using and the sproc itself are part of the Blogs database on the database server, then the one context for the Blogs database allows the access of all tables used in the sproc running in the Blogs database. The dbcontext that context is derived from points to a database on the database server.

I thought .Blogs is a table?

Answer 3

User1120430333 posted

tvb2727

DA924

What if the stored procedure is pulled from multiple tables?

A sproc is not pulled from multiple tables. The results of an executed sproc can have a result from querying and pulling data from one or more tables.

var blogs = context.Blogs
.FromSql("EXECUTE dbo.GetMostPopularBlogs")
.ToList();

One of the EF dbcontext's job in this situation is to provide an open connection to the database in question, as if you were using an  ADO.NET Connction.Open statement in c#. As long as all the tables the sproc is using and the sproc itself are part of the Blogs database on the database server, then the one context for the Blogs database allows the access of all tables used in the sproc running in the Blogs database. The dbcontext that context is derived from points to a database on the database server.

I thought .Blogs is a table?

No, Blogs is a database. A stored procedure is hosted by a database. A database table is hosted by a database.  A database server such as MS SQL Server can host many individual databases. An ORM such as the ADO.NET Entity Framework works with a database running on a database server such as MS SQL Server. 

As stated before,  'context' is derived from dbcontext. Dbcontext works with an individual  database hosted by a database server such as MS SQL Server.

https://www.entityframeworktutorial.net/efcore/entity-framework-core-dbcontext.aspx

Answer 4

User303363814 posted

Don't use FromSql if your stored procedure returns multiple recordsets

Get started at https://docs.microsoft.com/en-us/ef/ef6/modeling/designer/advanced/multiple-result-sets

The first call gets the rows of the first resultset and then you use .NextResult or .GetNextResult to move to the next resultset.

Answer 5

User-2054057000 posted

You can use context.Database.ExecuteSqlCommand() to execute your stored procedure. Refer this article - Execute SQL Stored Procedures using FromSql() & ExecuteCommand() methods in Entity Framework Core