IEnumerable and IQueryable , what is the difference?

Dear Habrovchane, I decided to share one not obvious point of using LinqToSql, which I encountered some time ago. Namely, about the features of using cascading Linq queries:

Working on another ASP.NET MVC project, and designing the level of access to the database, I needed to check the quality of the scripts generated by the L2C Framework.

What we have (simplified version of the model):

class User {
public long Id {get; set; }
public string Name {get; set; }
public IEnumerable <Parameter> Parameters {get; set; }
}
')
class Parameter {
public long UserId {get; set; }
public string Name {get; set; }
public string Value {get; set; }
}

To get the data, the UserRepository class is written:

class UserRepository: MyProjectDataContext {
public IEnumerable GetUsers () {
return this.Users.ToModelUsers ();
}
}

static class RepositoryHelper {
public static IEnumerable <Model.User ToModelUsers (this IEnumerable <DataAccess.User> users) {
return users.Select (u => new Model.User {Id = u.Id, Name = u.Name, Parameters = u.Parameters.ToModelParameters ()});
}

public static IEnumerable <Model.Parameter> ToModelParameters (this IEnumerable <DataAccess.Parameter> parameters) {
return parameters.Select (u => new Model.Parameter {...});
}
}

write
var users = userRepository.GetUsers (). ToList ();

We look at the profiler, and we are surprised to find out that as many as 11 requests were executed for loading 10 users and their parameters.

I'm trying to cure this problem, changing the repository code for the following:

public IEnumerable <User> GetUsers () {
return this.Users.ToModelUsers (). Select (u => new Model.User {
Id = u.Id, Name = u.Name, Parameters = u.Parameters.Select (p => new Model.Parameter {...})
});
}

The picture changes dramatically. Only 1 request in which both user and parameter parameters are loaded.

Where is the dog buried?

Everything is very simple, the whole thing is in the original version after calling the GetUsers method we received a request containing 2 different expressions: Linq2Sql and Linq2Object, and in the second only Linq2Sql.

And now the conclusions

As it turned out, Linq extensions behave quite differently when using a variable of type IEnumerable and IQueryable, and this is not accidental. The fact is that the user.Select (...) method exists in both the System.Linq.Enumerable and System.Linq.Queryable classes, respectively, but the implementation is of course different (as can be easily seen with the Reflector).

What to do?

There are 2 ways, either specify the type IQueryable <T> for the return value

static class RepositoryHelper {
public static IQueryable <Model.User> ToModelUsers (this IQueryable <DataAccess.User> users) {
return users.Select (u => new Model.User {Id = u.Id, Name = u.Name, Parameters = u.Parameters.ToModelParameters ()});
}
}

or explicitly cast IEnumerable <T> to IQueryable <T> by calling the method .AsQueryable <T> ()

And the most important thing is to remain attentive and not forget what is hidden behind the syntactic sugar of features .Net 3.5 / 4.0