LINQ to SQL extension for ‘in’ operator

Published: 2015-08-07

David Edelstein

Using LINQ to SQL, if you have a list of ID’s you’d like to retrieve from the db, you could of course write the LINQ query directly

from item in dbtable
where item.ID=123 || item.ID=234 || item.ID=345 || ....
select item

This is do-able of course, but it requires that you statically know all the items you want to add ahead of time. If you instead have this in a list computed during runtime, you could try to join:

from id in new List<int>{1,2,3};
join item in dbtable on id equals item.ID
select item

But this will run the query and then perform the filter on the client side.

Ideally, we’d like to send all of the elements in the list to the server for serverside filtering. If we want this in the where clause, this requires us to dynamically build a where clause. We can do that using Expressions. Basically we’d build an expression that said (item.ID=123 || (item.ID=234 || (item.ID=345 || … and then set that as the new where clause to our query. This will cause the LINQ to SQL query generator to send all of those items to the server to include in the query.

Here is a sample of how to use this extension as well as the implementation

(  from item in dbtable
   select item
).InList(new List{1,2,3}, itemselect=> itemselect.ID);

//and if we have just a vector, we can omit the itemselector
(  from item in dbtable
   select item.ID
).InList(new List<int>{1,2,3});

This will emit SQL similar to:

-- Region Parameters
DECLARE @p0 Int = 1
DECLARE @p1 Int = 2
DECLARE @p2 Int = 3
-- EndRegion
SELECT [t0].[ID], [t0].[Col1], [t0].[Col2], [t0].[CreateDate]
FROM [dbo].[dbtable] AS [t0]
WHERE ([t0].[ID] = @p0) OR ([t0].[ID] = @p1) OR ([t0].[ID] = @p2)
GO

public static class SQLLinqExtension
{
	//Build a new where clause for this queryable that checks if the column in the compareselector is equal to any item in the list
	public static IQueryable<QT> InList<QT, ListT>(this IQueryable<QT> source, IEnumerable<ListT> itemlist, Expression<Func<QT, ListT>> compareselector){
		var param = Expression.Parameter(source.ElementType, "src");
		var filt = null as Expression; 
		foreach(var item in itemlist){
			if(filt==null)
				filt = Expression.Equal(
					Expression.Invoke(compareselector, param),
					Expression.Constant(item));
				else
					filt = Expression.OrElse(
						filt, 
						Expression.Equal(
							Expression.Invoke(compareselector, param),
							Expression.Constant(item))
						);
		}
		
		return source.Provider.CreateQuery<QT>(
			Expression.Call(typeof(Queryable), 
				"Where",
				new Type[] {source.ElementType},
				source.Expression,
				Expression.Lambda<Func<QT, bool>>(filt, param))	);	
	}
	
	//Helper method when the queryable and the list to check have the same element type.  In this case, the item selector returns the item
	public static IQueryable<ListT> InList<ListT>(this IQueryable<ListT> source, IEnumerable<ListT> itemlist){
		return source.InList(itemlist, x=>x);
	}
}