EntityFramework Core Query

In this library we can add custom result set model class (Complex Types) as per your query using entity framework core.

Use following nuget package for Asp.net Core 2.2 or .Net Core 2.2

Using Nuget Package Manger:

Old Package
PM> Install-Package EntityFrameworkCore.Query -Version 1.2.6

Recommended package
PM> Install-Package EntityFrameworkCore.Query -Version 1.2.6.2

Using .Net CLI:

Old Package
> dotnet add package EntityFrameworkCore.Query --version 1.2.6

Recommended package
> dotnet add package EntityFrameworkCore.Query --version 1.2.6.2

Use following nuget package for Asp.net Core 3.1 or .Net Core 3.1

Using Nuget Package Manger:

Old Package
PM> Install-Package EntityFrameworkCore.Query -Version 1.3.1

Recommended package
PM> Install-Package EntityFrameworkCore.Query -Version 1.3.1.2

Using .Net CLI:

Old Package
> dotnet add package EntityFrameworkCore.Query --version 1.3.1

Recommended package
> dotnet add package EntityFrameworkCore.Query --version 1.3.1.2

Step 1

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

Step 2 (How to call and return join tables data from stored procedures)

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

	END

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

#endregion

Step 3 (Returning Multiple Result Sets from a Stored Procedure.)

If 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:

Below code is working on following version only.

1. EntityFrameworkCore.Query -Version 1.2.6

2. EntityFrameworkCore.Query -Version 1.2.6.2

3. EntityFrameworkCore.Query -Version 1.3.1

4. EntityFrameworkCore.Query -Version 1.3.1.2

// 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:

Below code is working on following version only.

1. EntityFrameworkCore.Query -Version 1.2.6.2

2. EntityFrameworkCore.Query -Version 1.3.1.2

// 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 

Note

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.