Linq, ordering an IQueryable by an IOrderedEnumerable

Question

I have an MVC telerik grid that I have set up to do custom binding to an IQueryable to facilitate the sorting of a calculated column. The default sort behavior when the grid is sorted on this property is this:

data = data.OrderBy(product => product.oneMthCost_IntOnly);

"data" is the IQueryable, product.oneMthCost_IntOnly is not returned from the database, it is a calculated property which is calculated when the "get" accessor is called for this property on the "SearchViewModel":

public class SearchViewModel
{
    public int ID  { get; set; }
    public string lend_name  { get; set; }
    public decimal? pDes_rate  { get; set; }
    public string pDes_details  { get; set; }
    public int? pDes_totTerm  { get; set; }
    public decimal? pDes_APR  { get; set; }
    public string pDes_revDesc  { get; set; }
    public string pMax_desc  { get; set; }
    public DateTime? dDipNeeded { get; set; }
    public DateTime? dAppNeeded { get; set; }

    public decimal oneMthCost_IntOnly
    {
        get { return ProductOperations.CalculateSingleYearInterestCost(SourcingModel.search.LoanAmt, (decimal)pDes_rate); }
    }
}

To explain how the SearchViewModel ("data") is returned, it is based on an Entity Data Model that uses the following deferred Linq query as a basis for the grid to project into the SearchViewModel.

    //Return the required products
    var model = from p in Product.Products
                where p.archive == false && ((Prod_ID == 0) || (p.ID == Prod_ID))
                select new SearchViewModel
                    {
                        ID = p.ID,
                        lend_name = p.Lender.lend_name,
                        pDes_rate = p.pDes_rate,
                        pDes_details = p.pDes_details,
                        pDes_totTerm = p.pDes_totTerm,
                        pDes_APR = p.pDes_APR,
                        pDes_revDesc = p.pDes_revDesc,
                        pMax_desc = p.pMax_desc,
                        dDipNeeded = p.dDipNeeded,
                        dAppNeeded = p.dAppNeeded
                    };

Using the grids default behavior, therefore, the below:

data = data.OrderBy(product => product.CalculatedProp);

Throws this error when this column is sorted on:

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

Well, this does make sense, the expression tree doesn't know what this value is going to be until it has got it using the 'get' accessor. So, I am quite resigned to the fact I will need to materialize the whole set of objects, do the calculation for each row, and then sort the IQueryable on it (unfortunately the business logic is too complex for the expression tree to turn the calculation into SQL, so a C# method is required). So I do the below:

var calcdata = data.ToList().OrderBy(p => p.oneMthCost_IntOnly);

Which materializes all the data, does all the calculations, and sorts it into an IOrderedEnumerable calcdata... here is the rub:

How do I join "calcdata" to "data" to sort the IQueryable data by IOrderedEnumerable "calcdata"s key? Rebinding the grid to "calcdata" messes up paging, however, if you suggest this is the best way ahead, I can follow this path also.

Apologies this is a bit long winded, there is just quite a lot of information I wanted to include to give you the fullest picture.

Thanks,

Mark

Answer 1

I was on the right track with the below:

calcdata = data.ToList().OrderByDescending(p => p.oneMthCost_IntOnly);

This meterializes all of the rows of the data when sorted in this way (not very performant, I know, but it is the only way to be able to do this sort on data the database does not know about). The trouble I was having was the above creates an IOrderedEnumerable, when paging this, as below:

        //Then paging
        if (command.PageSize > 0)
        {
            calcdata = calcdata.Skip((command.Page - 1) * command.PageSize);
        }

        calcdata = calcdata.Take(command.PageSize);

This emits an IEnumerable back (where I was assuming it would emit an IOrderedEnumerable), so was trying to cast back to this type which then broke the paging. The above works where calcdata is an IEnumerable, NOT an IOrderedEnumerable. No join is required to the IQueryable (why go back to the database when you have all the information required?), so we rebind to the IEnumerable.

Thank you for the input Crab Bucket, it led me down some new lines of enquiry that have helped me to eventually sort this problem out!

Regards,

Mark

Answer 2

OK- this is quite high level and a brief answer to your very full question.

We were hitting problems with ordering very complex dataset. Like you we tried to realise them and the performance was awful. What we did in the end was create a View in the database with the columns that we needed to order by. We included this View in the Entity model which then behaves just like a table (some caveats to be honest).

Once we had the View in the EF model we could then use order by without materialising. You view could also contain the join that you need.

This method very much depends on you having that control over your database which I appreciate not every developer does. We found this general approach very successful particular if you could construct the View in such a way it is possible to get a primary key on them.

If you can't put View in your databases then you could use entity framework constructs such as QueryViews to similar effect. These have their own constraints however - e.g. can't update the model automatically any more, can join anything other than QueryView to them. I believe it would work but you would need to get your hands dirty and amend the EF model xml directly for this.

Caveats for views

We had to do some fiddling around mapping stored procs to the Views. Because it was read only we didn't really want to bother with inserts but when deleting dependent entities I expacted a delete from the view. TBH - we hacked it at this point and included a dummy proc that affected one row and this work. There must be a better answer than this though.

OK - less brief than I thought it was going to be when I started writing.

EDIT

if you want to join the two collections you could with the join keyword. As one is from the database and the other from c# then they would both have to bu IEnumerables - i.e the EF query realised via ToList() and the c# object in an appropriate collection i.e. generic List.

Then the syntax might be

var joinedCollections = from efItem in efCollection
                     join charpItem in charpCollection on csharpItem.CommonId equals efItem.CommonID
                     select select new {csharp.field1, efItem.field1};

Of course this assumes there is a common key between the two collection. Also efficiency might be terrible.