Differences when using IEnumerable and IQueryable as an ObjectSet Type

Question

As I understand it, when I use LINQ extension methods (with lambda expression syntax) on IQueryable , which is in the instance of the ObjectSet fact, they translate into LINQ to SQL queries. I mean, the team

 IQueryable<User> users = db.UserSet; var users32YearsOld = users.Where(user => user.Age == 32); 

exactly matches

 IQueryable<User> users = db.UserSet; var users32YearsOld = from user in users where user.Age == 32 select user; 

That way, they don't end up in the database until they users32YearsOld are listed for a loop or such. (I hope I understand this correctly).

But what happens if I do not mask that the ObjectSet as IQueryable , but as IEnumerable ? So what if its type is IEnumerable ?

 IEnumerable<User> users = db.UserSet; var users32YearsOld = users.Where(user => user.Age == 32); 

Will it immediately get into the database (if it is then when? Right in the first line or on the second)? Or will it behave like the previous command, which will not get into the database until users32YearsOld is listed? Will there be any difference if I use instead?

 IEnumerable<User> users = db.UserSet; var users32YearsOld = from user in users where user.Age == 32 select user; 

thanks

Answer 1

Discard my answer because I just tested it and it works exactly as I described:

None of the mentioned queries will get into the database, because there was no enumeration. The difference between IQueryable and IEnumerable is that in the case of IQueryable filtering will be performed on the database server, while in the case of IEnumerable all objects will be loaded from the database into memory and the filtering will be performed in .NET code (linq-to- objects). As you can imagine, this is usually a performance killer.

I wrote a simple test in my project:

 [TestMethod] public void Test() { // ObjectQuery<Department> converted ot IEnumerable<Department> IEnumerable<Department> departmetns = CreateUnitOfWork().GetRepository<Department>().GetQuery(); // No query execution here - Enumerable has also deffered exection var query = departmetns.Where(d => d.Id == 1); // Queries ALL DEPARTMENTS here and executes First on the retrieved result set var result = departmetns.First(); } 

Answer 2

Here is a simple explanation:

 IEnumerable<User> usersEnumerable = db.UserSet; IQueryable<User> usersQueryable = db.UserSet; var users = /* one of usersEnumerable or usersQueryable */; var age32StartsWithG = users.Where(user => user.Age == 32) .Where(user => user.Name.StartsWith("G"); 

If you use usersEnumerable , when you start listing it, two Where will be executed sequentially; First, the ObjectSet will retrieve all the objects, and the objects will be filtered to those aged 32, and then they will be filtered to those whose name begins with G.

If you use usersQueryable , the two Where will return new objects that will accumulate the selection criteria, and when you start listing it, it will translate all the criteria into the query. This makes a noticeable difference.

You usually don’t need to worry, since you will tell var users or ObjectSet users when you declare your variable, which means that C # will know that you are interested in calling the most specific method that is available on ObjectSet , and the query operator methods are IQueryable ( Where , Select , ...) are more specific than IEnumerable methods. However, if you pass objects to methods that take IEnumerable parameters, they may call the wrong methods.

You can also use a method that works to your advantage, using the AsEnumerable() and AsQueryable() methods to start using a different approach. For example, var groupedPeople = users.Where(user => user.Age > 15).AsEnumerable().GroupBy(user => user.Age); will delete the right users with the database query, and then group the objects locally.

As others have said, it's worth repeating that nothing happens until you start listing the sequences (using foreach ). Now you need to understand why this could not be otherwise: if all the results were obtained immediately, you could not create queries to translate into a more efficient query (for example, an SQL query).

Answer 3

You are right in IQueryable. As for IEnumerable, it immediately enters the database after assigning the IEnumerable user.

In the above example, there is no real difference between using Linq Extensions and syntax. Sometimes one or the other will be more convenient (see linq-extension-methods-vs-linq-syntax ), but IMO is more about personal preferences.

Answer 4

The difference is that IEnumerable executes filters, if there are more, one at a time. For example, out of 100 elements, 20 will be displayed by the first filter, and then it will filter the necessary 10 a second time. It will make one query in the database, but will load unnecessary data. Using IQueryable will load again with a single query, but only 10 required elements. The following link gives some great examples of how these queries work: https://filteredcode.wordpress.com/2016/04/29/ienumerable-vs-iqueryable-part-2-practical-questions/