how to sort on a field not stored within the db

Question

I have an asp.net mvc site and I'm unable to sort on a field that is calculated when needed in the model.

    private decimal _total = -1;
    public decimal Total
    {
        get
        {
            if (_total < 0)
            {
                _total = get_total(TableId);
            }
            return _total;
        }
    }

    private decimal get_total(int id)
    {
       ....Many Calcs
    }

I'm trying to sort on Total, but I get the error:

Additional information: The specified type member 'Total' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

Here is my actionlink:

@Html.ActionLink("By Total", "Index", new { sortOrder = ViewBag.Total, currentFilter = ViewBag.CurrentFilter }, new { @class = "btn btn-danger" })

I have found some similar issues, but I just can't figure out what how to sort by this.

And my controller. I tried to edit this down for clarity.

    public ActionResult Index(string sortOrder)
    {

        ViewBag.CurrentSort = sortOrder;
        ViewBag.Total = sortOrder == "total" ? "total_desc" : "total";

        var records = from u in db.Records.Include(t => t.User).Where(t => t.Active == true)
                     select u;

        switch (sortOrder)
        {
             case "total":
                records = db.Records.OrderBy(u => u.Total).Where(t => t.Active == true);
                break;
            case "rating_desc":
                records = db.Records.OrderByDescending(u => u.Total).Where(t => t.Active == true);
                break;
            default:
                records = db.Records.OrderBy(u => u.Title).Where(t => t.Active == true);
                break;
        }

        return View(records.ToList());
    }

You're not showing the code where you actually sort, or where you added this `Total` property. From the looks of things, I'm guessing you added it to the EF entity via a partial class. — Paul Abbott, Apr 26 '16 at 22:29

Gabor · Accepted Answer · 2016-04-27T22:22:07.683

Try to call ToList() method before trying to order by this property as this cannot be translated to an SQL statement.

// I assume currently your query is something like this
DbContext.SomeEntity.Where(...).OrderBy(e => e.Total);

// After calling .ToList() you can sort your data in the memory (instead of in db)
DbContext.SomeEntity.Where(...).ToList().OrderBy(e => e.Total);

UPDATE:
The problem is that first you declare the records variable with this line:

var records = from u in db.Records.Include(t => t.User).Where(t => t.Active == true) select u;

Because of this the type of the records variable will be System.Linq.IQueryable<Project.Models.Record> and that's why in the switch case you "needed" to cast with .AsQueryable().

Additionally the initial value will be always overridden in the switch statement therefore it is totally unnecessary to initialize it as you do it currently.

What you should do:

public ActionResult Index(string sortOrder)
{
    /* ViewBag things */

    IEnumerable<Record> records =
        db
            .Records
            .Include(record => record.User)
            .Where(record => record.Active)
            .ToList(); // At this point read data from db into memory

    // Total property cannot be translated into an SQL statement.
    // That's why we call it on memory objects instead of DB entities.
    switch (sortOrder)
    {
        case "total":
            records = records.OrderBy(record => record.Total);
            break;

        case "rating_desc":
            records = records.OrderByDescending(record => record.Total);
            break;

        default:
            records = records.OrderBy(record => record.Title);
            break;
    }

    return View(records.ToList());
}

After changing my query, I'm now getting the following error:Error 12 Cannot implicitly convert type 'System.Linq.IOrderedEnumerable' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?) Any ideas? — MVC_Future_Guru, Apr 27 '16 at 14:55
Thank you Gabor for the update. Is the only real benefit of this in avoiding having to cast each switch statement? I'm just curious as I'm hoping to implement lazy loading soon and I believe that's not supported with ienumerable, but then again by casting it back - am I just doing the same thing - I'm new to c#. — MVC_Future_Guru, Apr 28 '16 at 14:53
The benefit of explicitly setting the type of `records` variable to `IEnumerable` is that I have to call `ToList()` only once after ordering the result list. If I used `List` then I should call `ToList()` in every switch statement. BUT: When I initialized the `records` variable I definitely had to call `ToList()` because it iterates over the resultset which causes the query to be translated to SQL statement and be executed. So that the data is read from the DB and loaded into memory. This step is needed to be able to order by the `Total` property because it cannot be translated to SQL. — Gabor, Apr 28 '16 at 18:38
Additionally here you can read about lazy loading regarding `List` vs `IEnumerable`: http://stackoverflow.com/questions/21107569/list-vs-ienumerable-in-private-lazy-loaded-property — Gabor, Apr 28 '16 at 18:40

score 0 · Answer 2 · answered Apr 27 '16 at 19:23

I needed to cast my query as IQueryable, so here is the updated switch:

switch (sortOrder)
{
     case "total":
        records = db.Records.Where(t => t.Active == true).AsQueryable().OrderBy(u => u.Total));
        break;
    case "rating_desc":
        records = db.Records.Where(t => t.Active == true).AsQueryable.OrderByDescending(u => u.Total).;
        break;
    default:
        records = db.Records.Where(t => t.Active == true).AsQueryable.OrderBy(u => u.Title).;
        break;
}

Added update to my answer regarding your conversion issue. You did not need to cast as described in my updated answer. — Gabor, Apr 27 '16 at 22:23

how to sort on a field not stored within the db

2 Answers2