Linq2EF pitfall: Using Length property causes System.NotSupportedException

ArrayLength is not supported in LINQ to Entities

I spent the last half of previous friday banging my head against the wall over a System.NotSupportedException being thrown by Entity Framework 4.0. Now this might be related to the very fact that it was friday and my mind had already started drifting elsewhere, but I wasn't even close to finding the source of the problem. The exception messages sounded "The LINQ expression node type 'ArrayLength' is not supported in LINQ to Entities". When I saw 'ArrayLength' mentioned there, and stepping through the code in debug mode seeing that it failed in the foreach statement, I assumed the query went OK but Entity Framework was unable to traverse the result set. This was wrong.

You cannot use the .Length property of a VARBINARY field in the where clause of an Entity Framework query.1)

Now this may not come as a surprise to you, unless you've used Linq2SQL. You can there.

This problem is solved by rewriting the Length check to use the EF static method SqlFunctions.DataLength instead. This will result in EF producing a TSQL statement where the TSQL function DataLength is used on the property in question. This is incidentally what Linq2SQL does behind the scenes as well, without having to be told explicitlty (I found out that by using LinqPad).

1) This probably goes for the select clause as well, and for all fields having an intrinsic lenght/size property, but this is what I have tested in specific.

A small example - the wrong way

                var results = db.Customers.Where(p => p.PicutereBlob.Length > 0);
				foreach (var customer in results) { /* Do something */ }

Now the exception occurs on line 2 - one of the reasons for my confusion last friday. But of course, that's how Linq and Entity Framework operates.

A small example - the right way

                var results = db.Customers.Where(p => SqlFunctions.DataLength(p.PicutereBlob) > 0);

...or in plain Linq syntax:

from p in Customers
SqlFunctions.DataLength(p.PicutereBlob) > 0
select p

Resulting SQL query:

SELECT 
[Extent1].[PictureBlob] AS [PictureBlob], 
FROM [dbo].[Customers] AS [Extent1]
WHERE ( CAST(DATALENGTH([Extent1].[PictureBlob]) AS int)) > 0

This will be the statement given that PictureBlob is the only field in the table Customers.

By the way, This output is semi-directly copy-pasted from the console output while using the brilliant library provided by Jaroslaw Kowalski at his MSDN blog. This gives you the power to monitor all generated SQL queries from the client. A feature which again, Linq2SQL has by default. This in turn leads to the discussion of why we use Entity Framework in the first place, but I'm not going to delve into that hornet nest.