[Entity Framework] Gọi Store procedure

1. Tạo Store Proc

CREATE PROCEDURE [dbo].[sp_WhatEverNameYouWant]
	@ParamId			UNIQUEIDENTIFIER,
	@ParamTableType			UserDefinedTableTypeList READONLY,
	@Param1				BIT = NULL,
	@Param2				UNIQUEIDENTIFIER = NULL
AS
BEGIN
	SELECT 1
	SELECT 2	
END

2. Define call store method

// Method call store procedure from dbContext
public static DataSet ExecuteProcedure(this Context dbContext, string sqlCommand, List<SqlParameter> sqlParams)
{
            var dataset = new DataSet();
            var adapter = new SqlDataAdapter();
            SqlConnection connection = (SqlConnection)dbContext.Database.GetDbConnection();
            var command = new SqlCommand(sqlCommand, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (var item in sqlParams)
            {
                command.Parameters.AddWithValue(item.ParameterName, item.Value);
            }
            adapter.SelectCommand = command;
            adapter.Fill(dataset);
            return dataset;
}

3. Define datatable extension

public static class DataTableExtensions
    {
        public static List<T> ToList<T>(this DataTable dt)
        {
            const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;
            var columnNames = dt.Columns.Cast<DataColumn>()
                .Select(c => c.ColumnName)
                .ToList();
            var objectProperties = typeof(T).GetProperties(flags);
            var targetList = dt.AsEnumerable().Select(dataRow =>
            {
                var instanceOfT = Activator.CreateInstance<T>();

                foreach (var properties in objectProperties.Where(properties => columnNames.Contains(properties.Name) && dataRow[properties.Name] != DBNull.Value))
                {
                    properties.SetValue(instanceOfT, dataRow[properties.Name], null);
                }
                return instanceOfT;
            }).ToList();

            return targetList;
        }
    }

4. Sử dụng trong code

var dt = new DataTable();
dt.Columns.Add("Id", typeof(Guid));
DataRow dr = dt.NewRow();
dr[0] = Guid.Parse("1E501FCA-E1A2-4FA0-A2E7-02B0C1C19020");
dt.Rows.Add(dr);


List<SqlParameter> parms = new List<SqlParameter>
{
    new SqlParameter("@ParamId", Guid.NewGuid()),
    new SqlParameter("@ParamTableType", dt)
};
                    
var sqlCommand = "[dbo].[sp_WhatEverNameYouWant]";
var sqlParams = parms;
var context = new YourDBContext();
var datasetResult = context.ExecuteProcedure(sqlCommand , sqlParams);
// Convert datasetResult to ListObject
var listYourClassName = datasetResult.Tables[0].ToList<YourClassName>();
var listYourClassName2 = datasetResult.Tables[1].ToList<YourClassName2>();