Bad practice to chain my entity results

Question

When I try to access my db context as so, i am able to access other tables through my results:

MyEntities myEnt = new MyEntities();
    var comments = myEnt.Comments.Where(x=>x.UserName == UserName);
    foreach(Comment comment in comments){
        string FirstName = comment.UserProfile.FirstName;
    }

My intellisense pulls up foreignkey mapping and allows me to access the joined table UserProfile. Is this a bad practice to "chain" my results this way as opposed to doing a standard linq query with a join like so:

var query = from comments in myEnt.Comments
join up in myEnt.UserProfiles on comments.UserId equals up.UserId
select new {...}

UPDATE

Also what if I trail in even further such as:

comment.aspnet_Users.UserProfiles.UserRatings.ToList()

I tried something similar and it seemed like the query took much longer when I tried this approach over the 2nd. The intellisense lets you trail in quite a bit to related tables, does that play a factor on speed if i link through multiple tables like this?

That's the whole point of EF, or any other ORM. To allow you to talk to the database as if it were classes. I recommend sticking with your first method. WAY easier to deal with. In your second one your only re-coding what EF already know's - i.e that you're looking for a `UserProfile` related to a `Comment` — Darren Wainwright, Jan 02 '13 at 19:10
@Darren How about if i do something like: `comment.UserProfile.Addresses.First()` Would this at all effect performance? i had something like this, and i ended up using the 2nd method which seem to perform way faster — anthonypliu, Jan 02 '13 at 19:16
difficult to say without knowing more about your system. Though as a side-note, use FirstOrDefault() unless you can guarantee there IS an address.. FirstOrDefault() will return `null` if one doesn't exist, where `First()` will throw an error if it doesn't exist. That said though, if you were going to be drilling further down like that then get that one user profile first. I.E var up = comment.UserProfile. then use up.Addresses.FirstOrDefault - then you're not asking the system to create more SQL than it needs to — Darren Wainwright, Jan 02 '13 at 19:20
@Darren Ok, thanks, because my aspnet_Users table is my main table that i set UserId as my primary key that links to many other tables. So i am constantly finding myself doing something like this: `comments.aspnet_Users.UserProfile.FirstName` which feels like its effecting my query speed — anthonypliu, Jan 02 '13 at 19:25
Yep. If in one bunch of code you're doing things like `comments.aspnet_Users.UserProfile.FirstName` then something like `comments.aspnet_Users.UserProfile.LastName` you will be hitting the database twice - 2 sets of SQL queries containing joins etc. You would be better first getting the profile `var up = comments.aspnet_Users.UserProfile` and then calling `up.FirstName` and `up.LastName`. This will have meant just 1 trip to the DB to populate `up` — Darren Wainwright, Jan 02 '13 at 19:28

score 4 · Accepted Answer · edited May 23 '17 at 11:43

I would suggest looking into MiniProfiler

Based on your question there is a chance that the underlying provider could be causing an N+1 issue during the loop, which is considered a bad practice in the ORM world.

foreach(Comment comment in comments){ string FirstName = comment.UserProfile.FirstName; }

You could also use includes when you do your where statement to eagerly load the UserProfiles in each result:

var comments = myEnt.Comments.Where(x=>x.UserName == UserName); like this:

var comments = myEnt.Comments.Include('UserProfile').Where(x=>x.UserName == UserName);

score 0 · Answer 2 · answered Jan 02 '13 at 19:10

I don't think it makes a difference so long as the code that is accessing the expanded properties stays within the context that is currently created. It is when the entity leaves the context and code further on accesses one of those properties. If they do, and the context is closed, errors will eventually be thrown. If your data model would change, both examples would throw errors at compile time, so I think it is a wash.

Bad practice to chain my entity results

2 Answers2