In this library we can add custom result set model class (Complex Types) as per your query using entity framework core.
Old Package
PM> Install-Package EntityFrameworkCore.Query -Version 1.2.6
Recommended package
PM> Install-Package EntityFrameworkCore.Query -Version 1.2.6.2
Old Package
> dotnet add package EntityFrameworkCore.Query --version 1.2.6
Recommended package
> dotnet add package EntityFrameworkCore.Query --version 1.2.6.2
Old Package
PM> Install-Package EntityFrameworkCore.Query -Version 1.3.1
Recommended package
PM> Install-Package EntityFrameworkCore.Query -Version 1.3.1.2
Old Package
> dotnet add package EntityFrameworkCore.Query --version 1.3.1
Recommended package
> dotnet add package EntityFrameworkCore.Query --version 1.3.1.2
If you are working with Visual Studio, you can use the Package Manager Console commands to generate the the code files for the model.
> Scaffold-DbContext "Server=.\;Database=DatabaseName;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DbModels/DbContexts
If you want to return complex types, you need to create a class to represent them. The following procedure returns data with the following fields: FirstName,LastName,UserName,Pasword
CREATE PROC uspGetUsersJoins
@UserId NUmeric(18,0)
AS
BEGIN
SELECT
U.FirstName,
U.LastName,
UL.UserName,
UL.Password
FROM
tblUsers as U
INNER JOIN
tblUserLogin AS UL
ON
U.UserId=UL.UserId
WHERE
U.UserId=@UserId
ENDThis needs to be mapped to a class:
public class UsersJoinResultSetModel
{
public String FirstName { get; set; }
public String LastName { get; set; }
public String UserName { get; set; }
public String Password { get; set; }
}You call this procedure by passing UsersJoinResultSetModel as the type parameter to the SqlQueryAsync method:
// Make instance of EfCoreContext
EFCoreContext efCoreContext = new EFCoreContext();
#region How to call and return join data from stored procedures in Entity Framework Core
try
{
decimal userId = 1; // Get Current User Data
// Make Sql Parameters
List<SqlParameter> sqlParameters = new List<SqlParameter>();
sqlParameters.Add(new SqlParameter("@UserId", userId));
// Set procedure name with parameters
String sqlCommand = "EXEC uspGetUsersJoins @UserId";
// get Join Data
var joinData =
(
await
efCoreContext
?.SqlQueryAsync<UsersJoinResultSetModel>(sqlCommand, sqlParameters)
)
?.ToList();
}
catch
{
throw;
}
#endregionIf you want to return multiple result set from stored procedure, you need to create a class to represent them. The following procedure returns data with the following select query:
CREATE PROC uspGetUsersMultiResultSet
@UserId numeric(18,0)
AS
BEGIN
SELECT * FROM tblUsers WHERE UserId=@UserId
SELECT * FROM tblUserLogin WHERE UserId=@UserId
END
This needs to be mapped to a class:
public class UsersMultipleResultSetModel
{
public IEnumerable<TblUsers> ListUsers { get; set; }
public IEnumerable<TblUserLogin> ListUserLogin { get; set; }
}You call this procedure by passing UsersMultipleResultSetModel as the type parameter to the SqlQueryMultipleAsync method:
// Make instance of EfCoreContext
EFCoreContext efCoreContext = new EFCoreContext();
#region Returning Multiple Result Sets from a Stored Procedure
try
{
List<TblUsers> listUserModel = new List<TblUsers>();
List<TblUserLogin> listUserLoginModel = new List<TblUserLogin>();
decimal userId = 2; // Get Current User Data
// Make Sql Parameters
List<SqlParameter> sqlParameters1 = new List<SqlParameter>();
sqlParameters1.Add(new SqlParameter("@UserId", userId));
// Specify the procedure name with parameter
String sqlCommand = "uspGetUsersMultiResultSet";
// get Multiple Select query data
var getMultileSelectQueryData=
(
await
efCoreContext
.SqlQueryMultipleAsync<UsersMultipleResultSetModel>(
sqlCommand,
sqlParameters1,
System.Data.CommandType.StoredProcedure,
async (dbReaderObj) =>
{
// get First Result Set (First Select Query)
while (await dbReaderObj.ReadAsync())
{
listUserModel
.Add(new TblUsers()
{
FirstName = Convert.ToString(dbReaderObj["FirstName"]),
LastName = Convert.ToString(dbReaderObj["LastName"])
});
}
// get Next Result Set
await dbReaderObj.NextResultAsync();
// get Second Result Set (Second Select Query)
while (await dbReaderObj.ReadAsync())
{
listUserLoginModel.Add(new TblUserLogin()
{
UserName = Convert.ToString(dbReaderObj["UserName"]),
Password = Convert.ToString(dbReaderObj["Password"])
});
}
// Map two lists Object into MultiResult Set Model
return new UsersMultipleResultSetModel()
{
ListUsers = listUserModel,
ListUserLogin = listUserLoginModel
};
}
)
);
}
catch
{
throw;
}
#endregion You call this procedure by passing UsersMultipleResultSetModel as the type parameter to the SqlQueryMultipleAsync and SelectReadAsync extension method:
// Make instance of EfCoreContext
EFCoreContext efCoreContext = new EFCoreContext();
#region Returning Multiple Result Sets from a Stored Procedure
try
{
List<TblUsers> listUserModel = new List<TblUsers>();
List<TblUserLogin> listUserLoginModel = new List<TblUserLogin>();
decimal userId = 2; // Get Current User Data
// Make Sql Parameters
List<SqlParameter> sqlParameters1 = new List<SqlParameter>();
sqlParameters1.Add(new SqlParameter("@UserId", userId));
// Specify the procedure name with parameter
String sqlCommand = "uspGetUsersMultiResultSet";
// get Multiple Select query data
var getMultileSelectQueryData=
(
await
efCoreContext
.SqlQueryMultipleAsync<UsersMultipleResultSetModel>(
sqlCommand,
sqlParameters1,
System.Data.CommandType.StoredProcedure,
async (dbReaderObj) =>
{
// get First Result Set (First Select Query)
listUserModel1 = await dbReaderObj.SelectReadAsync<TblUsers>();
// get Next Result Set
await dbReaderObj.NextResultAsync();
// get Second Result Set (Second Select Query)
listUserLoginModel1 = await dbReaderObj.SelectReadAsync<TblUserLogin>();
// Map two lists Object into MultiResult Set Model
return new UsersMultipleResultSetModel()
{
ListUsers = listUserModel,
ListUserLogin = listUserLoginModel
};
}
)
);
}
catch
{
throw;
}
#endregion If you are using .Net Core 2.2 then add following namespace for SqlParameter Class.
using System.Data.SqlClient;If you are using .Net Core 3.1 then add following namespace for SqlParameter Class.
using Microsoft.Data.SqlClient;You can find me here.
