How to sort by / order by a field of a nested entity?

Question

How could something like this be done?

context.Subscriptions
.Include("TvShow.News")
.Include("User")
.Where(s => s.UserId == userId)
.OrderByDescending(t => t.TvShow.News.CreatedOnDatetime)
.ToList();

The OrderByDescending doesn't work in this case, i put it there just for easier understanding of the problem.

If you expect `TvShow.News` to be sorted within each `Subscription`, no, you can't do that with `Include`. — Gert Arnold, Aug 02 '15 at 18:15
Not out of the box. See [this](http://stackoverflow.com/a/26473557/861716). — Gert Arnold, Aug 02 '15 at 18:25

score 0 · Accepted Answer · answered Aug 03 '15 at 19:21

I finally got it to work with this spaghetti code:

// NASTY NASTY ENTITY FRAMEWORK
var anonymousObjects = context.Subscriptions
    .Where(c => c.UserId == userId)
    .Select(s => new
    {
        SubscriptionId = s.Id,
        TvShowId = s.TvShowId,
        TvShow = s.TvShow,
        TvShowNews = s.TvShow.News.OrderByDescending(n => n.CreatedOnDatetime),
        UserId = s.UserId,
        User = s.User
    });

anonymousObjects = anonymousObjects.OrderByDescending(x => x.TvShowNews.Max(n => n.CreatedOnDatetime));
var subscriptions = new List<Subscription>();
foreach (var oneAnonymousObject in anonymousObjects)
{
    var subscription = new Subscription
    {
        Id = oneAnonymousObject.SubscriptionId,
        TvShowId = oneAnonymousObject.TvShowId,
        UserId = oneAnonymousObject.UserId,
        TvShow = new TvShow
        {
            Id = oneAnonymousObject.TvShow.Id,
            Title = oneAnonymousObject.TvShow.Title,
            CreatedOnDatetime = oneAnonymousObject.TvShow.CreatedOnDatetime,
            IsPrivate = oneAnonymousObject.TvShow.IsPrivate,
            TotalSubscriptions = oneAnonymousObject.TvShow.TotalSubscriptions,
            News = oneAnonymousObject.TvShowNews.ToList()
        },
        User = oneAnonymousObject.User
    };
    subscriptions.Add(subscription);
}

Entity Framework team, if you see this, please please please make this easier.

How to sort by / order by a field of a nested entity?

1 Answers1