用LINQPad来测试 IEnumerable & IQueryable的主要区别

1.IEnumberable

    IEnumerable<Customers> customers =Customers.Where(q => q.CompanyName.StartsWith("A"));
                customers = customers.Take<Customers>(10);
                customers.Dump();

转成SQL:

SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[CompanyName] AS [CompanyName], 
    [Extent1].[ContactName] AS [ContactName], 
    [Extent1].[ContactTitle] AS [ContactTitle], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country], 
    [Extent1].[Phone] AS [Phone], 
    [Extent1].[Fax] AS [Fax]
    FROM [dbo].[Customers] AS [Extent1]
    WHERE [Extent1].[CompanyName] LIKE N'A%'

 

 

2.IQueryable

 IQueryable<Customers> customers =Customers.Where(q => q.CompanyName.StartsWith("A"));
                customers = customers.Take<Customers>(10);
                customers.Dump();

转成SQL:

SELECT TOP (10) 
    [Extent1].[CustomerID] AS [CustomerID], 
    [Extent1].[CompanyName] AS [CompanyName], 
    [Extent1].[ContactName] AS [ContactName], 
    [Extent1].[ContactTitle] AS [ContactTitle], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[City] AS [City], 
    [Extent1].[Region] AS [Region], 
    [Extent1].[PostalCode] AS [PostalCode], 
    [Extent1].[Country] AS [Country], 
    [Extent1].[Phone] AS [Phone], 
    [Extent1].[Fax] AS [Fax]
    FROM [dbo].[Customers] AS [Extent1]
    WHERE [Extent1].[CompanyName] LIKE N'A%'

 

3.两者查询出的数据都一样，如下：（但是用LINQPAD测试，IEnumerable永远都比IQueryable快？实际是哪种更快呢？）

4.总结两者的主要区别是：

1）IEnumerable是把所有数据加载到内存中，再调用SKIP,TAKE等扩展方法；

2）IQueryable是把SKIP,TAKE这些全部翻译成T-SQL语句再向SQL服务器发送命令，它并不是把所有数据都加载到内存里才进行条件过滤。

5.如何恰当使用IEumerable&IQueryable

1）IEnumerable更适合查询那些已经加载到内存中的数据，譬如List,Array等

2）IQueryable更适合查询那些在远程上的数据，譬如读取数据库中的数据，从服务中读取数据。