.NET Entity Framework - Using .Contains() to find a byte value in a Where expression

Question

I am building an IQueryable based on parameters I get from the user. One of those parameters is a multi-select and I need to retrieve records that contain any of the selected values.

The code that deals with that is:

var ids = parameters.DeliveryID.ToArray(); courses = courses.Where(c => ids.Contains(c.CourseDeliveryID));

In the above code:
1. ids - is a byte array and I make sure it has multiple values before calling Contains().
2. c.CourseDeliveryID - that's a byte value.

In the database I store CourseDeliveryID as tinyint (SQL Server 2008).

Compilation is just fine.

When I run the code I get the following ArgumentException:
DbExpressionBinding requires an input expression with a collection ResultType.
Parameter name: input

I found the documentation for that exception here: http://technet.microsoft.com/en-us/library/system.data.common.commandtrees.expressionbuilder.dbexpressionbuilder.bindas.aspx

While trying to solve the problem I found that if I use the same code on shorts, ints or longs I don't have any problem.

I'm in touch with Microsoft about it since yesterday and will update when I know more, but in the meantime I figured I'd throw it also here to get more advises if possible.

Thanks in advance!

Answer 1

I was able to reproduce your error in LINQPad, and found that using a List<byte> instead of a byte[] would work:

// byte[] ids = new byte[] { 1, 64 };  <== causes ArgumentException
List<byte> ids = new List<byte> { 1, 64};

var c = Courses.Where (co => ids.Contains(co.CourseDeliveryId));

will generate the following sql and return results:

SELECT 
[Extent1].[CourseId] AS [CourseId], 
[Extent1].[CourseName] AS [CourseName], 
[Extent1].[CourseDeliveryId] AS [CourseDeliveryId]
FROM [dbo].[Courses] AS [Extent1]
WHERE [Extent1].[CourseDeliveryId] IN (1,64)

It's also interesting that using an int[] or short[] would also work, producing this sql:

SELECT 
[Extent1].[CourseId] AS [CourseId], 
[Extent1].[CourseName] AS [CourseName], 
[Extent1].[CourseDeliveryId] AS [CourseDeliveryId]
FROM [dbo].[Courses] AS [Extent1]
WHERE (1 =  CAST( [Extent1].[CourseDeliveryId] AS int)) OR (64 =  CAST( [Extent1].[CourseDeliveryId] AS int))

but using a byte[] causes an exception. I can only guess that the SQL Server EF provider is trying to treat byte[] in some special way, resulting in this exception.

Answer 2

Although using different containers solve the issue, you do not have to change the container type. All you need to do is assign it to IEnumerable:

IEnumerable<byte> ids = parameters.DeliveryID.ToArray();
courses = courses.Where(c => ids.Contains(c.CourseDeliveryID));

(In this specific case you can just use ToList() instead of ToArray() but in the general case if you get the byte array and do not want to rebuild it as a list this will do)