The difference between IQueryable and IEnumerable

IEnumerable and IQueryable are interfaces that allows you to manipulate and query collections of data. IEnumerable is inherited by IQueryable, that means IQueryable add features to IEnumerable interface.

IEnumerable vs IQueryable

IEnumerable	IQueryable
System.Collections namespace	System.Linq namespace
LINQ to Object and LINQ to XML	LINQ to SQL
NO lazy loading	lazy loading
in-memory queries	out-memory (serverside) queries
doesn’t support custom query	supports custom query

Reference code from microsoft reference source

The following code shows the implementation of the IQueryable.Take() method.
The method creates a query on source provider to return top N elements.

	public static IQueryable Take(this IQueryable source, int count)
	{
	if (source == null)
	throw Error.ArgumentNull("source");
	return source.Provider.CreateQuery(
	Expression.Call(
	null,
	((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(typeof(TSource)),
	new Expression[] { source.Expression, Expression.Constant(count) }
	));
	}

view raw IQueryablevsIEnumerable\queryableTake.cs hosted with ❤ by GitHub

The following code shows the implementation of the IEnumerable.Take() method.
The method simply uses a foreach to return the top N elements.

	[TestMethod]
	public void IEnumerableBehavior()
	{
	IEnumerable<Product> products = dc.Product
	//This expression produces an SQL query
	.Where(p => p.Name.Contains("LL"));
	//IEnumerable: the following statement is an in-memory query
	products = products.Take<Product>(100);
	}

view raw IQueryablevsIEnumerable\enumerableBehavior.cs hosted with ❤ by GitHub

Example

The following example shows the difference between IQueryable and IEnumerable:

	[TestMethod]
	public void IEnumerableBehavior()
	{
	IEnumerable<Product> products = dc.Product
	//This expression produces an SQL query
	.Where(p => p.Name.Contains("LL"));
	//IEnumerable: the following statement is an in-memory query
	products = products.Take<Product>(100);
	}

view raw IQueryablevsIEnumerable\enumerableBehavior.cs hosted with ❤ by GitHub

The first statement uses the IQueryable interface, in fact it performs a query on data source and assigns the result to an IEnumerable variable.
The next operation is executed in-memory because they queriy use IEnumerable interface.

We can use SQL profiler to monitor the executed query:

	SELECT
	[Extent1].[ProductID] AS [ProductID],
	[Extent1].[Name] AS [Name],
	[Extent1].[ProductNumber] AS [ProductNumber],
	[Extent1].[MakeFlag] AS [MakeFlag],
	[Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag],
	[Extent1].[Color] AS [Color],
	[Extent1].[SafetyStockLevel] AS [SafetyStockLevel],
	[Extent1].[ReorderPoint] AS [ReorderPoint],
	[Extent1].[StandardCost] AS [StandardCost],
	[Extent1].[ListPrice] AS [ListPrice],
	[Extent1].[SIZE] AS [SIZE],
	[Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode],
	[Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode],
	[Extent1].[Weight] AS [Weight],
	[Extent1].[DaysToManufacture] AS [DaysToManufacture],
	[Extent1].[ProductLine] AS [ProductLine],
	[Extent1].[Class] AS [Class],
	[Extent1].[STYLE] AS [STYLE],
	[Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID],
	[Extent1].[ProductModelID] AS [ProductModelID],
	[Extent1].[SellStartDate] AS [SellStartDate],
	[Extent1].[SellEndDate] AS [SellEndDate],
	[Extent1].[DiscontinuedDate] AS [DiscontinuedDate],
	[Extent1].[rowguid] AS [rowguid],
	[Extent1].[ModifiedDate] AS [ModifiedDate]
	FROM [Production].[Product] AS [Extent1]
	WHERE [Extent1].[Name] LIKE N'%LL%'

view raw IQueryablevsIEnumerable\enumerableQuery.sql hosted with ❤ by GitHub

We can perform the same operations using IQueryable interface, all queries will be performed on data source:

	[TestMethod]
	public void IQueryableBehavior()
	{

	IQueryable<Product> list = dc.Product
	//IQueryable: this expression produces an SQL query
	.Where(p => p.Name.Contains("LL"));
	//IQueryable: this expression produces an SQL query
	list = list.Take<Product>(100);
	}

view raw IQueryablevsIEnumerable\queryableBehavior.cs hosted with ❤ by GitHub

The SQL query will filter the result, and will use the SQL statement “TOP(100)”:

	SELECT TOP (100)
	[Extent1].[ProductID] AS [ProductID],
	[Extent1].[Name] AS [Name],
	[Extent1].[ProductNumber] AS [ProductNumber],
	[Extent1].[MakeFlag] AS [MakeFlag],
	[Extent1].[FinishedGoodsFlag] AS [FinishedGoodsFlag],
	[Extent1].[Color] AS [Color],
	[Extent1].[SafetyStockLevel] AS [SafetyStockLevel],
	[Extent1].[ReorderPoint] AS [ReorderPoint],
	[Extent1].[StandardCost] AS [StandardCost],
	[Extent1].[ListPrice] AS [ListPrice],
	[Extent1].[SIZE] AS [SIZE],
	[Extent1].[SizeUnitMeasureCode] AS [SizeUnitMeasureCode],
	[Extent1].[WeightUnitMeasureCode] AS [WeightUnitMeasureCode],
	[Extent1].[Weight] AS [Weight],
	[Extent1].[DaysToManufacture] AS [DaysToManufacture],
	[Extent1].[ProductLine] AS [ProductLine],
	[Extent1].[Class] AS [Class],
	[Extent1].[STYLE] AS [STYLE],
	[Extent1].[ProductSubcategoryID] AS [ProductSubcategoryID],
	[Extent1].[ProductModelID] AS [ProductModelID],
	[Extent1].[SellStartDate] AS [SellStartDate],
	[Extent1].[SellEndDate] AS [SellEndDate],
	[Extent1].[DiscontinuedDate] AS [DiscontinuedDate],
	[Extent1].[rowguid] AS [rowguid],
	[Extent1].[ModifiedDate] AS [ModifiedDate]
	FROM [Production].[Product] AS [Extent1]
	WHERE [Extent1].[Name] LIKE N'%LL%'

view raw IQueryablevsIEnumerable\queryableQuery.sql hosted with ❤ by GitHub