CODEWITHSUNDEEP
c#asp.netentity-framework
proseidon
proseidon

Reputation: 2305

HUGE performance hit with one-to-one foreign key in Entity Framework?

I am going to make this REALLY simple. I have two tables. 

public class User
{
    public virtual int UserId { get; set; } //primary key AND foreign key
    //user attributes in here
    public virtual UserProfile UserProfile { get; set; }
}
public class UserProfile
{
    public virtual int UserId { get; set; } //primary key AND foreign key
    //profile attributes in here
    public virtual User User { get; set; }
}

Basically they are two tables that share a primary key in a 1-1 relationship. Whether these should be combined into one or not, I don't know, I am basing this off of an existing database.

Now, the issue I have is when I access it.

THIS CODE GOES FAST(second, maybe two):

List<User> userList; //**This userList is defined elsewhere, but it's a list of about 400 users.
foreach (User user in userList)
{
    var test = user.FirstName;
}

THIS CODE GOES REALLY SLOW(10-30 seconds):

List<User> userList; //**This userList is defined elsewhere, but it's a list of about 400 users.
foreach (User user in userList)
{
    var test = user.UserProfile.EmailAddress;
}

Why does my code take so much longer when I access the UserProfile from the user table?!

Upvotes: 3

Views: 1193

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

Hard to know for sure without the query giving you the Users in the question, but this is what I see;

The first query loops though all your User objects and prints the FirstName of each.
1 SQL query.

The second query loops through all your User objects, and for each sends a query to the database to get the UserProfile to get access to the EmailAddress.
401 queries.

You should read up on lazy loading vs. eager loading.

Upvotes: 3

mclark1129
mclark1129

Reputation: 7592

Probably because you are lazy loading UserProfile here. This means that for each iteration in your loop you make an individual call to the DB to load UserProfile when you attempt to access the email address.

I'm not sure how you are creating the userList collection, but assuming you are doing a simple query over your User table you can use Include to eager load any properties that you would like to have up front:

var userList = (from u in dbContext.Users.Include("UserProfile")
                select u)

Now the performance hit is limited to when this query is intially executed, enumerating over the results would no longer require individual calls to the DB.

Upvotes: 10

Related Questions