How to order a IQueryable list of parent objects by a value in it's childs list

Question

I'm trying to sort a parent list of complex objects by it's child list of complex objects based on the TextValue of a specific specification TypeId.

I have the following classes:

public class Product
{
    public long Id {get;set;}
    public string Name {get;set;}
    public List<Specification> Specifications {get;set;}
}

public class Specification
{
    public long Id {get;set;}
    public long TypeId {get;set;}
    public string TextValue {get;set;}
}

I want to sort my products based on a specific specification of the product. Example use case: Sort the products on the TextValue of the specification with TypeId = 3. (a product can only have one specification with TypeId 3)

My list of products looks like this:

IQueryable<Product> productQuery = _context.Products.Include("Specifications");

SortTypeId is the type of specification I want to order the Product list on.

This is what I tried to do:

productQuery = productQuery
.OrderBy(pq => pq.Specifications
.OrderBy(s => s.TypeID == SortTypeID ? Int32.MinValue : s.Id)
.ThenBy(v => v.TextValue));

This gives the following exception:

System.ArgumentException: 'DbSortClause expressions must have a type that is order comparable.
Parameter name: key'

I also tried to sort the IQueryable by a list of sortedProductIds with Indexof but that also didn't work (IndexOf not supported for IQueryable with lazy loading).

i think, you could use `.where(x=>xSpecifications.Any(y=>y.TypeID == 3))` to filter specifications, so after filter you have one-to-one relation between product and specification. and select an anonymous object that contains `new {product, specification}` and use the `orderby` ``specification.TextValue``. — Mohammed Sajid, Jun 14 '20 at 12:51

devcrp · Accepted Answer · 2020-06-14T18:21:29.927

2

Since you say a product can only have one specification with the SortTypeID type, what if you join that single specification in the query and order by it?

Here's some example of what I'm trying to suggest using query syntax.

from p in _context.Products.Include("Specifications")
join orderSpec in _context.Specifications on new { ProductID = p.Id, TypeID = 3 }
                                    equals new { ProductID = orderSpec.ProductId, TypeID = orderSpec.TypeID } into os 
from orderSpec in os.DefaultIfEmpty() 
orderby orderSpec != null ? orderSpec.TextValue : p.Id
select p

Hope it helps!

edited Jun 14 '20 at 18:21

answered Jun 14 '20 at 12:49

devcrp

1,323
7
17

A Product can have a list of specifications with TypeIds 1,2,3,4 for example. I want to order the list of products based on the TextValue in the product specification with TypeId 3. – Patrick Man Jun 14 '20 at 13:09
I changed it a little bit to make it more clear, I think that should do it. – devcrp Jun 14 '20 at 13:13
Thanks for your answer devcrp the sorting works, I have one question left. How can I also get the products back from the lambda expression that don't have a specification with TypeID 3? – Patrick Man Jun 14 '20 at 14:48
@PatrickMan try that one, I just edited it again. Basically you need to do a `left join`: https://stackoverflow.com/a/3413732/11350283 – devcrp Jun 14 '20 at 18:22
It works perfect, only thing that I needed to change in your query was the null check. I changed p.Id (long) to a string. – Patrick Man Jun 15 '20 at 15:51

How to order a IQueryable list of parent objects by a value in it's childs list

1 Answers1