When using Linq to sql in C#, IQueryable and IEnumerable are often confused. This article briefly analyzes the differences and usage scenarios between them.

preface

Whether Linq to object, Linq to sql or Linq to Entity, IQueryable and IEnumerable are delayed execution. The only difference between them lies in the parameter types of extension methods. (different iteration / enumeration methods? Different action objects?)

The difference between IQueryable and IEnumerable

• IQueryable: the extension method accepts Expression

  For Linq to sql or Linq to Entity, Expression must be able to be converted to sql, otherwise an error will be reported.

• IEnumerable: the extension method accepts Func(Func is C# syntax).

  IEnumerable runs Linq to Object, which forces all data from the database to be read into memory, so C# syntax can be used.

  For Linq to sql or Linq to Entity, Func is unlimited because it uses C# syntax to manipulate data. This also shows from the side that the data has been read into memory.

  The extension method of IEnumerable is Func and will not be converted to sql. The internal IQueryable is converted to sql. Therefore, it should be noted that the conditions are best limited to IQueryable, otherwise IQueryable may read a large amount of data, increasing time-consuming and memory.

The difference between AsEnumerable() and ToList()

• ToList(): execute immediately. sql will be executed immediately and the data will be fetched into memory.

• AsEnumerable(): delay the execution, and execute sql to read data only when it is actually used. There is a pit here. Be sure to look down

After using AsEnumerable() on IQueryable objects, the execution is still delayed, but the nature of the object has changed.

As mentioned earlier, the extension method of IEnumerable accepts Func(C# syntax). When the ie object (iq transformation) is actually used, there will be two steps:

1. It will translate the extension method of iq object (before transformation) into sql statement, load the query data into memory and turn it into ie object;
2. At this time, the extended method of ie object (after transformation) is solved by C# to obtain the final result.

For example:

The Skip and Take methods of iq objects will be translated into sql and executed in the database to get the final results.

The Skip and Take methods of ie objects will Take all the data into memory. Executing Skip and Take in memory will consume a lot of resources.

Usage scenario

• IQueryable: used when using EFCore to dynamically splice multiple where conditions. (delay the query, and the data will be re read every time it is actually used.)

• IEnumerable: when the extension method cannot be converted to sql, it can be converted to IEnumerable using AsEnumerable(). Because the extension methods of IEnumerable use C# syntax to process data. (delay the query, and the data will be re read every time it is actually used.)

• ToList(): when the where condition has been determined, you can use ToList() to fetch data from the database immediately, and then reuse the data.

However, in order to save trouble, I usually use IQueryable to splice conditions, and then directly use ToList()...

remarks

Exception: system Invalidoperationexception: cannot enumerate query results more than once

Exception occurrence condition

In Linq to sql or ef (non EFCore), after directly executing the sql statement to query the data, this exception will be raised by enumerating the data set (IEnumerable) multiple times.

After testing, Count() multiple times will throw this exception. Other Sum(), foreach, etc. should also be verified...

My understanding is that a data set (IEnumerable) uses an enumerator to process each item of data, and the enumerator can only go once. I don't understand enumerators and iterators. I need to study...

Note that this exception will not appear in efcore. Please search for the reason why efcore executes sql.

resolvent

• Method 1: to List the query result (), and then use List to operate the data.
• Method 2 [recommended]: discard the built-in and use Dapper, because the query result of Dapper is essentially a List. (multiple result sets are not. Search Dapper for more information.)

Exception recurrence code

The following code is Linq to sql. It is said on the Internet that this exception will also occur in EF. The code should be similar.

In addition, most of the exceptions in online search occur when executing stored procedures. In fact, they also directly execute sql to query data. The essence is the same.

string sql = @"
select top 100 *
from [dbo].[BaseSupplier_OTAOnline]";
//return db.ExecuteQuery<T>(sql, parameters);
IEnumerable<BaseSupplier_OTAOnline> ieBs02 = bdb.QueryBySql<BaseSupplier_OTAOnline>(sql);//"exec Pro_BaseSpOtaOnline_Test01"
int count = ieBs02.Count();
ieBs02 = ieBs02.Skip(1).Take(2);
//****An exception is thrown here****
int count02 = ieBs02.Count();

Myth: when using foreach for iq objects and ie objects, query the database for each item of the loop

Wrong!

Foreach is for the whole dataset object (iterator?). When foreach is used, whether iq object or ie object, they query the database once, and then start the cycle until the end of the cycle. However, when the specific data of iq object or ie object is used again, they will still query the database again.

Note: the result of the iq object is a dataset. It can only convert the currently stored expression tree into sql. It can't process it to extract data one at a time, because it's impossible! How can you make something out of nothing?

Reverse verification

You can also think like this: if you take data one by one, how does the program know which data should be taken each time?

• Use DataReader?

  No, it's too inefficient. Because after taking out each data, a series of operations (code logic) on the data are required, which takes time. The DataReader needs to maintain the database connection online. Taking too long will lead to many database connections at the same time, and will soon reach the upper limit of the database connection pool. This method is very undesirable.

• Perform top 1 processing on the generated sql?

  How do you know which data to take out each time? Use the information of the previous data as the where condition? No, it's stupid to do so, and the network data transmission increases; Query efficiency is also low; Occupying database connection pool resources. Various shortcomings, simple problems and complex problems.

As can be seen from the above counterexample, it is possible to query data one by one, but it is too forced. It's not as good as reading all the data at once.

other

Test code of sql generated by IQueryable and IEnumerable

Let's draw a conclusion first:

• Only the expression of IQueryable will be translated into sql, and the func of IEnumerable will not be translated into sql. The sql generated in the code can be verified.
• Both are delayed execution, and the database will be queried only when they are used.

NetFramework

Test environment:

• .NET Framework 4.5
• LINQ to SQL class (not EntityFramework)

            BaseSpDB bdb = new BaseSpDB();
            //Do not query database
            IQueryable<BaseSupplier_OTAOnline> iqBs = bdb.baseSpByAll().Where(p => p.ID < 10);
            //Do not query database
            IEnumerable<BaseSupplier_OTAOnline> ieBs = iqBs.AsEnumerable();
            //Do not query database
            ieBs = ieBs.Where(p => p.ID > 5);
            //Execute sql
            //Conditions for iq only
            //query data base
//exec sp_executesql N'SELECT [t0].[ID], [t0].[OTAName], [t0].[OnlineSupplier], [t0].[PushUrl], [t0].[Note], [t0].[EditCode], [t0].[AddUser], [t0].[PushDate], [t0].[GetDate], [t0].[EditDate], [t0].[AddDate]
//FROM [dbo].[BaseSupplier_OTAOnline] AS [t0]
//WHERE [t0].[ID] < @p0',N'@p0 int',@p0=10
            List<BaseSupplier_OTAOnline> bsList = ieBs.ToList();
            //Query the database again
//exec sp_executesql N'SELECT [t0].[ID], [t0].[OTAName], [t0].[OnlineSupplier], [t0].[PushUrl], [t0].[Note], [t0].[EditCode], [t0].[AddUser], [t0].[PushDate], [t0].[GetDate], [t0].[EditDate], [t0].[AddDate]
//FROM [dbo].[BaseSupplier_OTAOnline] AS [t0]
//WHERE [t0].[ID] < @p0',N'@p0 int',@p0=10
            foreach (var item in ieBs)
            {
                
            }

NetCore

Test environment:

• AspNetCore 2.1
• EFCore

            //Do not query database
            IQueryable<BaseSupplier_OTAOnline> iqOta = ctx.BaseSupplier_OTAOnline.Where(p => p.ID < 10);
            //Do not query database
            IEnumerable<BaseSupplier_OTAOnline> ieOta = iqOta.AsEnumerable();
            //Do not query database
            ieOta = ieOta.Where(p => p.ID > 5);
            //Execute sql
            //Conditions for iq only
            //query data base
//SELECT [p].[ID], [p].[AddDate], [p].[AddUser], [p].[EditCode], [p].[EditDate], [p].[GetDate], [p].[Note], [p].[OTAName], [p].[OnlineSupplier], [p].[PushDate], [p].[PushUrl]
//FROM [BaseSupplier_OTAOnline] AS [p]
//WHERE [p].[ID] < 10
            List<BaseSupplier_OTAOnline> bsList = ieOta.ToList();
            //Query the database again
//SELECT [p].[ID], [p].[AddDate], [p].[AddUser], [p].[EditCode], [p].[EditDate], [p].[GetDate], [p].[Note], [p].[OTAName], [p].[OnlineSupplier], [p].[PushDate], [p].[PushUrl]
//FROM [BaseSupplier_OTAOnline] AS [p]
//WHERE [p].[ID] < 10
            foreach (var item in ieOta)
            {
            }