Part 33 : calling stored procedure part 2

#3 Minutes of Reading

I shown you limitation with FromSqlRaw method, it works only with already available module and not return result set of multiple tables or table join.
EF provide alternate way to achieve this with traditional ExecuteReader method. Let’s see how to achieve this with EF core.
Create required database tables and stored procedure.

CREATE TABLE [dbo].[tblRole] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Role] VARCHAR (20) NULL
);
CREATE TABLE [dbo].[tblUserRole] (
[RoleID] INT NULL,
[UserId] INT NULL
);
ALTER procedure getUserRoles
@user varchar(20)
As
Begin
Select U.UserName, R.Role from
Users U inner join tblUserRole UR on UR.UserId = U.id inner join tblRole R on UR.RoleID = R.Id
Where U.UserName = @user
End
 
Create model under Models folder.
public class UserRole
{
public string User { get; set; }
public string Role { get; set; }
}

Create Index2 action method and GetUserRoles method.

GetUserRoles method communicate and return multiple table result set from database. At line number 34, EF core support to create traditional command object and then add SqlParameter, command text as stored procedure to command object.
ExecuteReader of command object return table result of type DbDataReader. To read data from DbDatareader use Read method from datareader object shown at line number 40. Create view Index2 for showing UserRole.

Run application and view changes in Browser.