Geek Answers Handbook

Data Loading Strategy / Syntax in EF4

Long time lurker, first time publication and new training for EF4 and MVC3.

I need help to make sure that I am using the correct data loading strategy in this case, as well as some help in detailing the request. I am currently using the look-ahead approach here for some representation of the dashboard, which 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 additional filtering, sorting and generating data that I could not perform. Here are my main needs / concerns from now on:

  • Several child objects still need additional filtering, but so far I have not yet been able to understand the syntax or the best approach. Example: "TOP 3 LeagueEvents.Event WHERE StartDate> = getdate () ORDER BY LeagueEvents.Event.StartDate"

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

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

Any input, guidance, or recommendations to address these remaining needs, as well as ensure the best possible performance, are welcome.

+4
source share
2 answers

Your concern for the size of the request and the size of the result set is tangible .

As pointed out by @BrokenGlass, EF does not allow filtering or ordering. If you want to order or filter relationships, you must use a projection of either an anonymous type or a custom (not displayed) 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) ... });
+1
source

Unfortunately, EF does not allow selectively loading related objects using its navigation properties; it will always load all Foos if you specify Include("Foo") .

You will need to attach to each of the related objects, using your Where() clauses as filters where they apply.

0
source

More articles:


All Articles