Data Loading Strategy/Syntax in EF4

Question

Long time lurker, first time posting, and newly learning EF4 and MVC3.

I need help making sure I'm using the correct data loading strategy in this case as well as some help finalizing some details of the query. I'm currently using the eager loading approach outlined here for somewhat of a "dashboard" view that requires a small amount of data from about 10 tables (all have FK relationships).

            var query = from l in db.Leagues
                 .Include("Sport")
                 .Include("LeagueContacts")
                 .Include("LeagueContacts.User")
                 .Include("LeagueContacts.User.UserContactDatas")
                 .Include("LeagueEvents")
                 .Include("LeagueEvents.Event")
                 .Include("Seasons")
                 .Include("Seasons.Divisions")
                 .Include("Seasons.Divisions.Teams")
                 .Where(l => l.URLPart.Equals(leagueName))
                         select (l);

            model = (Models.League) query.First();

However, I need to do some additional filtering, sorting, and shaping of the data that I haven't been able to work out. Here are my chief needs/concerns from this point:

• Several child objects still need additional filtering but I haven't been able to figure out the syntax or best approach yet. Example: "TOP 3 LeagueEvents.Event WHERE StartDate >= getdate() ORDER BY LeagueEvents.Event.StartDate"

• I need to sort some of the fields. Examples: ORDERBY Seasons.StartDate, LeagueEvents.Event.StartDate, and LeagueContacts.User.SortOrder, etc.

• I'm already very concerned about the overall size of the SQL generated by this query and the number of joins and am thinking that I may need a different data loading approach alltogether.(Explicit loading? Multiple QueryObjects? POCO?)

Any input, direction, or advice on how to resolve these remaining needs as well as ensuring the best performance is greatly appreciated.

Answer 1

Your concern about size of the query and size of the result set are tangible.

As @BrokenGlass mentioned EF doesn't allow you doing filtering or ordering on includes. If you want to order or filter relations you must use projection either to anonymous type or custom (non mapped) type:

        var query = db.Leagues
                      .Where(l => l.URLPart.Equals(leagueName))
                      .Select(l => new 
                          {
                              League = l,
                              Events = l.LeagueEvents.Where(...)
                                                     .OrderBy(...)
                                                     .Take(3)
                                                     .Select(e => e.Event)
                              ... 
                          });