Monday, August 15, 2011

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.


17 comments:


  1. I really like your take on the issue. I now have a clear idea on what this matter is all about..
    real estate broker

    ReplyDelete
  2. Your music is amazing. You have some very talented artists. I wish you the best of success. property insurance

    ReplyDelete
  3. You have done a great job on this article. It’s very readable and highly intelligent. You have even managed to make it understandable and easy to read. You have some real writing talent. Thank you. Cachet Hotel Group

    ReplyDelete
  4. I just want to let you know that I just check out your site and I find it very interesting and informative.. Ohio Car Insurance

    ReplyDelete
  5. Thanks you very much for sharing these links. Will definitely check this out.. Bukit 828

    ReplyDelete
  6. it was a wonderful chance to visit this kind of site and I am happy to know. thank you so much for giving us a chance to have this opportunity.. The Hyde Condo Balmoral Road

    ReplyDelete
  7. The financial aspects of the property can have a major impact on the price and or the interest of purchasers.real estate practice test

    ReplyDelete
  8. This is really a nice and informative, containing all information and also has a great impact on the new technology. Thanks for sharing it compare auto insurance reviews

    ReplyDelete
  9. Great post but I was wondering if you could write a little more on this subject? I’d be very thankful if you could elaborate a little bit further. Thanks in advance! House for Sale

    ReplyDelete
  10. Find top realtors in your city or town that go the extra mile and advertise in all the top spots. Our Real Estate Brokers use an inside platform to connect buyers and sellers through Agents in our system. We have lists of developers, builders. And do lan assembly packages to get the best value for your home or property. When only the finest Realtor will do to work with your most valuable asset then look no further than our hand picked Real Estate Brokers list. If you want more info click here link : Real Estate Brokers

    ReplyDelete
  11. I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. villa for sale Guadalmina alta

    ReplyDelete
  12. Great info! I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have  House blog

    ReplyDelete
  13. Awesome blog. I would love to see true life prepared to walk, so please share more informative updates. Great work keeps it up. 200-125 exam dumps

    ReplyDelete
  14. Get expert Question & Answer Dumps PDF Online. We Provide latest IT Certification Exams PDF for preparation Study Guide Test Practice for Success in exams. This is an online education portal
    dumpsaway.com

    ReplyDelete
  15. Love to read it,Waiting For More new Update and I Already Read your Recent Post its Great Thanks. ChoreRelief

    ReplyDelete
  16. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. Condo Size for family

    ReplyDelete