Is Deferred Execution in Asp.net MVC View a very bad thing?

Question

Let's say i have the following model which is obtained via Entity Framework:

public class User{
 public string Name {get;set;}
 public int Id {get;set;}
}

Get all the users from the user table:

IEnumerable<User> users = from u in myDbContext.Users
                          select u;

Reder the users in the View (@model IEnumerable):

@foreach(var item in Model) 
 {
 <tr>@item.Id</tr>
 <tr>@item.Name</tr>
 }

My new boss is telling me that doing it this way is not MVC conform and the performance will be very bad. What i should do is to materalize all USERS in the Controller with .ToList(). I am not sure what is better, because either way the users have to be materialized and i dont see any performance losses.

Answer 1

Deferred execution means, your actual SQL query behind the LINQ expression won't be executed until you start accessing the items in the users collection (when you iterate those in the foreach loop). That means, There will be a SELECT query executed if you are trying to access a navigational property (and it is a unique record) on the User entity

If you only have a single user table (like what you have showed in your question) without any foreign keys/vanigational properties, With the above code you have, EF will execute only one query to get data from your User table. But typically this is not the case, You might have foreign keys to a lot of different tables from the User table. In that case Entity framework does thing differently.

Let's take an example.

Assume that we have a second table, UserType which has 4 columns, Id, Name ,Code and IsAdmin and your User table has a third column called UserTypeId which has a foreign key to this new UserType table. Every user record is associated with a UserType record.

Now you want to display all the Users with the UserType name. Let's see different approaches.

1. Deferred execution,

You will execute this code to get all the users

var users = dbContext.Users;

And pass the users to the razor view where you will iterate through the collection.

@model IEnumerable<YourEntityNameSpace.User>
@foreach (var user in Model)
{
    <div>
        @user.Name - @user.UserType.Name
    </div>
}

When we execute this page, Entity framework is going to run 1 select query on the User table to get the user records along with the UserTypeId and when the foreach block is being executed, it is going to query the UserType table for each unique UserTypeId from the original result set(users). You can see this if you run a SQL profiler.

You can see that EF is passing UserTypeId (2 in my picture). I had 3 different UserTypeId's being used in the User table, so it queried the UserType table 3 times, one for each UserTypeId.

Number of SQL queries executed : 4 (1 for user table + 3 for UserType table)

I had 3 different records in the UserType table and i used all those in my User Table.

2. Using Include method when querying data

Include keyword is used to achieve eager loading. Eager loading is the process where a query for one type of entity also loads related entities as part of the query.

var users = dbContext.Users.Include(s=>s.UserType);

Here you are telling Entity framework to query from UserType table along with User table. Entity framework will produce an INNER JOIN sql query between both the tables and execute it.

You can see that, It queried all the columns of the UserType table)

Number of SQL queries executed : 1

A Better solution ?

Usually, It is not a good idea to use the entity classes generated by Entity framework in other layers so much. That makes your code tightly coupled. I would suggest querying only data(columns) which is needed and map that to a POCO class (A simple DTO) and use that in your views /other layers. You may keep these DTO classes in common project which can be referred in other projects (Your Data Access project and UI project)

public class UserDto
{
  public int Id {get;set;}
  public string Name {get;set;} 
  public UserTypeDto UserType { set; get; }    
}
public class UserTypeDto
{
    public int Id { set; get; }
    public string Name { set; get; }
}

Our view will be bound to a collection of UserDto insted of the User entity

@model IEnumerable<YourCommonNamespace.User>
@foreach (var user in Model)
{
    <div> @user.Name - @user.UserType.Name </div>
}

And now from your Data access layer, you will be returning a collection of UserDto instead of the User entity created by Entity framework.

var users = dbContext.Users.Select(s => new UserDto
{
    Id = s.Id,
    Name = s.Name,
    UserType = new UserTypeDto
    {
        Id = s.UserType.Id,
        Name = s.UserType.Name
    }
});

Here, You can see that We are using the Select clause to tell EF which columns we really need. EF will execute an INNER JOIN, but with only those columns, we specified

Number of SQL queries executed : 1

The benefit of this approach is, If you ever want to switch your data access implementation from Entity framework to some other technologoy (Pure ADO.NET/ NHibernate) for your own reasons, You will be only updating your GetUsers method, All other layers (your Razor views/ Other Business layer code etc..) don't need an update because they are not using the entities created by entity framework.

If you do a ToList(), EF executes the SQL right away and return the result. But instead if you are not doing that, because of deferred execution, It is going to execute the SQL statement(s) when it actually needs the data (ex : You render some property value in your view inside the loop).