EntityFramework. order by count of sub array elements

Question

I created three tables in Database using Entity Framework

class User{
    public string Name {get;set;}
    public IEnumerable<Pet> Pets {get;set;}
}

class Pet{
    public string Name {get;set;}
    public IEnumerable<Toy> Toys {get;set;}
}

class Toy{
    public string Name {get;set;}
}

I want to select top 10 users (using linq) which have the biggest amount of toys.

Tried this one, but did not work. Entity framework could not translate it into SQL query

Entities
.OrderBy(u => u.Pets.Select(n => n.Toys.Count()))
.ToListAsync();

What kind of linq query should I use to do this ?

Answer 1

I would suggest to prepare grouping query with totals:

var totals =
    from e in Entities
    from p in e.Pets
    from t in p.Toys
    group e by e.Id into g
    select new 
    {
        Id = g.Key,
        Count = g.Count()
    };

var query = 
    from e in Entities
    join t in totals on e.Id equals t.Id
    orderby t.Count descending
    select e;

var result = await query
    .Take(10)
    .ToListAsync();