How to convert SQLQuery to SortedList through EF6

Question

I have an Entity Framework 6 class called Materials, which is reflected in my database as a table with the same name. Using a parent parameter, I need to return a sorted list of materials from a SQL Query, so that I can later check that edits the user makes do not affect the order. My SQL is a stored procedure that looks like this:

CREATE PROC [dbo].[GET_SortedMaterials](@FinishedGoodCode   VARCHAR(50))
AS

SELECT
     ROW_NUMBER() OVER (ORDER BY Component.Percentage_of_Parent DESC,Material.Material) AS _sortField
    ,Material.*
FROM
            Components  AS Component
INNER JOIN  Materials   AS Material ON Component.Child_Material = Material.Material
WHERE
    Component.Parent_Code = @FinishedGoodCode
ORDER BY
     Component.Percentage_of_Parent DESC
    ,Material.Material

As you can see, the orderby field is not included in the Material. For this reason, I felt I could not return just a set of Material objects and still keep the sorting - I have performed the ordering in SQL and added the _sortField (I think that field may be a bad idea).

My C# code to read the SQL looks like this:

public async Task<SortedList<int, Materials>> GET_SortedMaterials(IProgress<Report> progress, string finishedGoodCode)
{
    try
    {
        var report = new Report { Message = "Retrieving Sorted Materials", NewLine = true, StatusCode = Enums.StatusCode.Working };
        progress.Report(report);
        using (var context = new DBContext())
        {
            var ingredientList = await context.Database.SqlQuery<(int _sortField,Materials mat)>("[app].[GET_Customers]").ToListAsync();
            var sorted = new SortedList<int, Raw_Materials>();
            foreach (var (_sortField, mat) in ingredientList.OrderBy(x=>x._sortField))
            {
                sorted.Add(_sortField, mat);
            }
            return sorted;
        }
    }
    catch (Exception ex)
    { [EXCLUDED CODE]
    }
 }

When the code executes, I get the correct number of rows returned, but I do not get a Sorted list where the Key corresponds to the _sortField value and the Value to the Material value. I have tried various different versions of basically the same code and I cannot get the script to return a list of materials with information about their sorting, instead, the conversion to EF class fails entirely and I only get null values back:

Any advice about how to return a sorted list from SQL and maintain the sorting in C#, when the sort field is not in the return values would be very gratefully received.

Answer 1

use

var ingredientList = await context.Database.SqlQuery<Materials>("[app].[GET_Customers]").Select((mat, _sortField) => (_sortField, mat)).ToDictionary(x => x._sortField, x => x.mat);

or if you want async load use

var ingredientList = await context.Database.SqlQuery<Materials>("[app].[GET_Customers]").ToListAsync().Result.Select((mat, _sortField) => (_sortField, mat)).ToDictionary(x => x._sortField, x => x.mat);

full code

public async Task<SortedList<int, Materials>> GET_SortedMaterials(IProgress<Report> progress, string finishedGoodCode)
{
   try
   {
       var report = new Report { Message = "Retrieving Sorted Materials", NewLine = true, StatusCode = Enums.StatusCode.Working };
       progress.Report(report);
       using (var context = new DBContext())
       {
           var ingredientList = await context.Database.SqlQuery<Materials>("[app].[GET_Customers]").ToListAsync().Result.Select((mat, _sortField) => (_sortField, mat)).ToDictionary(x => x._sortField, x => x.mat);
           var sorted = new SortedList<int, Raw_Materials>();
           foreach (var item in ingredientList.OrderBy(x => x.Key))
           {
               sorted.Add(item.Key, item.Value);
           }
           return sorted;
       }
   }
   catch (Exception ex)
   {
      [EXCLUDED CODE]
   }
}