Why EF can't sort items by the order of another collection and how to workaround?

c# entity-framework-6 linq sorting

Question

I have been trying to sort a LINQ query using a list of strings. I am querying student terms and including TermCourses. The code throws the following error:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String)' method, and this method cannot be translated into a store expression.'

I have done this kind of sorting using DataTables where I have just rows but not in EF and a tree of objects.

How do I work around this issue?

Here is my Code:

private static readonly List<string> SortingOrder =
   new List<string>()
   {
        "Fall",
        "Spring",
        "Summer"
   };

using (var context = new EUContext())
{
    var tmp = context.Terms.Include(x=>x.TermCourses)
        .OrderBy(x => x.AcademicYear)
        .ThenBy(x => SortingOrder.IndexOf(x.TermRegistered))
        .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId);
    return tmp.ToList();
}
1
2
11/26/2018 12:04:04 PM

Accepted Answer

You are trying to sort on the database by a method that is available in your code/RAM only. You need to transfer control of the sorting back to your local computer:

private static readonly List<string> SortingOrder =
   new List<string>()
   {
        { "Fall" },
        { "Spring" },
        { "Summer" }
   };

using (var context = new EUContext())
{
    // this part will query the database
    var tmp = context.Terms
                     .Include(x=>x.TermCourses)
                     .Where(x => x.StudentID == studentId && x.DepartmentID == departmentId)

                     // this will transfer controll to your local machine
                     .AsEnumerable() 

                     // sort on your local machine by the data you have in your RAM
                     .OrderBy(x => x.AcademicYear)
                     .ThenBy(x => SortingOrder.IndexOf(x.TermRegistered));

    return tmp.ToList();
}
2
11/26/2018 12:02:48 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow