CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitiesentity-framework-5
Snæbjørn
Snæbjørn

Reputation: 10792

OrderBy a subset of relationship with condtions

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
}

Upvotes: 2

Views: 487

Answers (3)

Slauma
Slauma

Reputation: 177133

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.

Upvotes: 1

123 456 789 0
123 456 789 0

Reputation: 10865

Given your Foo is a collection of Foos, Try this

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

Upvotes: 0

Bassam Alugili
Bassam Alugili

Reputation: 17003

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

Upvotes: 0

Related Questions