Can Entity Framework sort entities by properties on a related entity?

Question

Suppose I have the following "Foo" and "Bar" entities:

class Foo {
   int FooId;
   string FooName;
}

class Bar {
   int BarId;
   Foo RelatedFoo;
   string BarName;
}

Let's also suppose that I want "RelatedFoo" to be lazy-loaded by default.

In Entity Framework, is it possible to do a query that returns an enumerable of "Bar" entities where elements are sorted by "bar.RelatedFoo.FooName"?

If so, can this be done in a fixed number of database queries? I would like to avoid doing N+1 queries.

If not, is this possible in another .NET ORM framework?

DDiVita · Accepted Answer · 2012-12-08T16:08:36.227

1

var bars = _context.Bars.OrderBy(b => b.RealtedFoo.FooName)

You may also want to only bring back those bars that RealtedFoo is not null

var bars = _context.Bars.Where(b => b.RelatedFoo != null).OrderBy(b => b.RealtedFoo.FooName)

Update:

    //For EF only
    _context.Configuration.LazyLoadingEnabled = false

    //If you want to bring back RealtedFoo then include it. 
//Otherwise, you can just query for it and not use the Include() extension.
    var bars = _context.Bars.Include(b => b.RealtedFoo).Where(b => b.RelatedFoo != null).OrderBy(b => b.RealtedFoo.FooName)

edited Dec 08 '12 at 16:08

answered Dec 08 '12 at 15:30

DDiVita

4,225
5
63
117

I am not sure what the actual query would look like in, say, SQL Server, but you could run a SQL profiler to see. Is that what you are asking? – DDiVita Dec 08 '12 at 15:36
I want to make sure I wouldn't be doing N queries by accessing RelatedFoo. – chrisdfrey Dec 08 '12 at 15:37
It will also depend if you are using Eager or Lazy Loading in Entity Framework. – DDiVita Dec 08 '12 at 15:39
Suppose I am using lazy loading. It is possible to do this without doing N queries (where N is the number of bar objects)? – chrisdfrey Dec 08 '12 at 15:41
I am not sure what you are asking. That one line will bring back all Bars (in your DB) where RealtedFoo is not NUll (second expression) and order them by the FooName. – DDiVita Dec 08 '12 at 15:45
I am trying to avoid an "N+1" queries problem (see http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem). – chrisdfrey Dec 08 '12 at 15:46
I see what you are asking now, I apologize. when you state "N is the number of bar objects" it was not referring to the query. In this case, you could go with Eager loading and use the Include method. See my Update – DDiVita Dec 08 '12 at 16:03
I would recommend buying EF profiler or using miniprofiler (free) if you want to check against n+1 queries – mosesfetters Dec 08 '12 at 16:04
@DDiVita: sorry, did you update your answer to use "Include"? I don't see it. – chrisdfrey Dec 08 '12 at 16:10

Can Entity Framework sort entities by properties on a related entity?

1 Answers1

Linked