OrderBy a subset of relationship with condtions

Question

I'm attempting to order Foo by Bars.Max(Date) on a subset of Bars that doesn't have an Optional.

It's hard to explain in text so here's the query I got so far.

// Foos is of type DbSet<Foo> a "code first" EF entity
Foos.OrderBy(f => f.Bars
                  .Where(b => b.Optional == null)
                  .Max(b => b.Date));

That query fails with a NotSupportedException

Cannot compare elements of type 'System.Collections.Generic.ICollection`1'. Only primitive types, enumeration types and entity types are supported.

Model

public class Foo
{
    public int Id { get; set; }

    public virtual ICollection<Bar> Bars { get; set; } // one to many
}

public class Bar
{
    public int Id { get; set; }
    public DateTime Date { get; set; }

    public virtual Foo Foo { get; set; }
    public virtual ICollection<Optional> Optional { get; set; } // zero to many
}

public class Optional
{
    // omitted for brevity
}

When you say `Foo.OrderBy(f => f.Bars .Where(b => b.Optional == null) .Max(b => b.Date));` what is `Foo` ? Is it a local variable of type `Foo` ? Something like `Foo Foo = new Foo();` ? — Eduard Dumitru, Sep 10 '13 at 14:44
Foo is a DbSet, so actually it should be called Foos. Perhaps I should clearly point that out. I thought it would be obvious from the tags. — Snæbjørn, Sep 10 '13 at 14:50

score 1 · Accepted Answer · answered Sep 10 '13 at 18:00

Bar.Optional is a collection, not a single reference. You cannot compare collections with null with LINQ-to-Entities. Instead you must filter by the Bars where the Optional collection does not (!) have Any element:

Foos.OrderBy(f => f.Bars
                   .Where(b => !b.Optional.Any())
                   .Max(b => b.Date));

It might be necessary that you must use Max(b => (DateTime?)b.Date) instead of just Max(b => b.Date) to account for the possible case that the Bars collection of a Foo might be empty and therefore doesn't have a maximum Date. I'm not 100% sure about that. You should test the case of an empty Bars collection explicitly.

That fixed it, thank you. I was polluted by "model first" approach :) — Snæbjørn, Sep 11 '13 at 13:01

Bassam Alugili · Answer 2 · 2013-09-10T14:46:06.007

0

  Foo.OrderBy(f => f.Bars
 .Where(b => b.Optional == null).AsEnumerable().
 .Max(b => b.Date));

edited Sep 10 '13 at 14:46

answered Sep 10 '13 at 14:40

Bassam Alugili

16,345
7
52
70

Are you sure you're not just impressed by the answers you got for the question you posted earlier ? http://stackoverflow.com/questions/18714821/how-can-i-call-local-method-in-linq-to-entities-query – Eduard Dumitru Sep 10 '13 at 14:43
@EduardDumitru lol , Yes you have right! I did not even read the whole Quesition I Just saw the problem . – Bassam Alugili Sep 10 '13 at 14:44
Why this will not work as far as I know that Linq 2 Entities does not support DateTime you have to load them in memory try ToLoist(). – Bassam Alugili Sep 10 '13 at 14:49
1

What are you talking about, they do support DateTime and no, you don't have to load them in memory as long as you know what you are doing. – 123 456 789 0 Sep 10 '13 at 14:50
http://stackoverflow.com/questions/4146300/linq-to-entityframework-datetime @Snæbjørn did this link solved your problem? or im in the wrong film? – Bassam Alugili Sep 10 '13 at 14:50
His scenario is different from the question you posted. – 123 456 789 0 Sep 10 '13 at 14:53
@LeoLorenzoLuis go ahead post your answer! – Bassam Alugili Sep 10 '13 at 14:54
I did, if you are paying attention. – 123 456 789 0 Sep 10 '13 at 14:54
@BassamAlugili That solution requires more then one trip to the database. I would like avoid that if possible. – Snæbjørn Sep 10 '13 at 14:58

123 456 789 0 · Answer 3 · 2013-09-10T15:07:10.233

0

Given your Foo is a collection of Foos, Try this

Foo.OrderBy(f =>f.Bars.Where(x => x.Optional == null)
                                        .Max(x => x.Date)));

edited Sep 10 '13 at 15:07

answered Sep 10 '13 at 14:43

123 456 789 0

10,565
4
43
72

I'm getting a compile error "A lambda expression with a statement body cannot be converted to an expression tree". – Snæbjørn Sep 10 '13 at 14:54
@BassamAlugili Yes, but I want to try a solution that doesn't use it. As it give me all sorts of problems later. – Snæbjørn Sep 10 '13 at 14:56
What is Foos? Can you query all the Foos and do a ToList() and do it again. I'm assuming your Foos is a Context from EF. – 123 456 789 0 Sep 10 '13 at 14:56
@Snæbjørn mark this answer if helping you and you can also use DefaultIEmpty as improvement thanks. – Bassam Alugili Sep 10 '13 at 14:57
@LeoLorenzoLuis Yes Foos is from my DbContext. It's of type DbSet. – Snæbjørn Sep 10 '13 at 15:01
Can you ToList when you query the DbSet – 123 456 789 0 Sep 10 '13 at 15:01
Yes I can call ToList but I would really like to avoid that as currently I'm passing my query as a IQueryable for paging. – Snæbjørn Sep 10 '13 at 15:06
Ok, try this. ss.OrderBy(f =>f.Bars.Where(x => x.Optional == null) .Max(x => x.Date)); – 123 456 789 0 Sep 10 '13 at 15:06
Given, that your navigation properties (Collections) are eager loaded. – 123 456 789 0 Sep 10 '13 at 15:08
@EduardDumitru Is this not just your answer? – Bassam Alugili Sep 10 '13 at 15:08
@LeoLorenzoLuis isn't `ss.OrderBy(f =>f.Bars.Where(x => x.Optional == null) .Max(x => x.Date));` what I posted as the problem query to being with :)? Anyway I tried eager loading the collections still no luck. – Snæbjørn Sep 10 '13 at 15:11
Oops, let me recreate it with an actual Context not just List :p – 123 456 789 0 Sep 10 '13 at 15:13
Here, ss.OrderBy(f => f.Bars.Select(x => x.Date).Max()); – 123 456 789 0 Sep 10 '13 at 15:18

OrderBy a subset of relationship with condtions

3 Answers3