Descending by average value using linq

Question

I faced a problem with entity framework. I'm trying sort my collection by average value from one of the key:

var userPhotos = Database.Set<Photo>().Where(p => string.IsNullOrEmpty(userId) || p.ClientProfileId == userId);
usersPhotos = usersPhotos.OrderByDescending(item => item.Votes.Average());`

In the last line of code I have "The specified type member 'Votes' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." execpetion. My member Votes looks like so:

public class Photo : BaseEntity, IEntity<Guid>
{
    public Photo()
    {
        Id = Guid.NewGuid();
        Votes = new List<double>;
    }
    [Key]
    public Guid Id { get; set; }
    //Other fields
    public ICollection<double> Votes { get; set; }
}

How to fix my problem and make sort by avarege value of this member?

Kosala W · Accepted Answer · 2016-02-05T07:38:44.513

1

All you need to is ToList() at the end of the first line.

You can do it this way;

var userPhotos = Database.Set<Photo>().Where(p => string.IsNullOrEmpty(userId) || p.ClientProfileId == userId).ToList();
var sortedUsersPhotos = usersPhotos.Where(p=>p.Votes!=null && p.Votes.Any()).OrderByDescending(item => item.Votes.Average());

edited Feb 05 '16 at 07:38

answered Feb 05 '16 at 06:59

Kosala W

2,133
1
15
20

It creates a new object is not mandatory. Is it a more elegant way? – user3818229 Feb 05 '16 at 07:01
Where does `usersPhotos` come from? – Kosala W Feb 05 '16 at 07:06
Updated my question. – user3818229 Feb 05 '16 at 07:09
See the updated answer – Kosala W Feb 05 '16 at 07:11
If so, I need to create a new variable for sorted photos otherwise I have types conflict. – user3818229 Feb 05 '16 at 07:17
Yes.. Didn't notice you have used the same name. Yes you have to create a new variable. – Kosala W Feb 05 '16 at 07:18
Damn it! `Average()` throw an execption for empty votes collection:( – user3818229 Feb 05 '16 at 07:30
Is it better to create one more member in database with average value and sorting by average key? – user3818229 Feb 05 '16 at 07:33
ok. Really?? Just.. have a look at the updated answer – Kosala W Feb 05 '16 at 07:34
What will happend with order if every item contains empty votes member or some of them? – user3818229 Feb 05 '16 at 07:35
What do you mean by empty votes member? – Kosala W Feb 05 '16 at 07:36
I guess you meant 0 members. Ok. Answer updated. These are very basic things that you should be able to fix by yourself. – Kosala W Feb 05 '16 at 07:40
I think it's better to write like `(p => p.Votes.Count != null)` – user3818229 Feb 05 '16 at 07:40
Oh. no.. I am going to stop it there. You need to figure it out. – Kosala W Feb 05 '16 at 07:41
I mean `(p => p.Votes.Count != 0)` It was misprint. – user3818229 Feb 05 '16 at 08:42
1

You've got to look more. Even `(p => p.Votes.Count != 0)` is not correct and cannot be recommended even with the correct syntax. Better approach is `Any()` – Kosala W Feb 05 '16 at 08:44
When you ask me what I mean by empty votes member, I mean that current code skip objects which have no votes. So it ordering photos only with rating. It is not what I want. I need all photos even with 0 votes. – user3818229 Feb 05 '16 at 13:49
Update your answer to `sortedPhotos = userPhotos.OrderByDescending(item => (item.Votes == null || !item.Votes.Any()) ? 0 : item.Votes.Average());` – user3818229 Feb 07 '16 at 09:18

score 0 · Answer 2 · answered Feb 05 '16 at 07:01

0

You can use GroupBy:

var sorted = userPhotos.GroupBy(item => item.Votes.Average()).OrderByDescending(item => item.Key);

If you want to use the same object:

userPhotos = userPhotos.GroupBy(item => item.Votes.Average()).OrderByDescending(item => item.Key).SelectMany(x=>x);

answered Feb 05 '16 at 07:01

Tyress

3,573
2
22
45

Tried the second version, still "NotSupportedExecption" – user3818229 Feb 05 '16 at 07:08

Descending by average value using linq

2 Answers2