Error: LINQ to Entities does not recognize DataLength method

Question

I have a quick question. first using the EF6 model.

var db = new MyEntities(GetEntityConnectionString()); ObjectContext objectContext = ((IObjectContextAdapter)db).ObjectContext; ObjectSet<DOCUMENT> objectSet = objectContext.CreateObjectSet<DOCUMENT>(); var results = objectSet.Where("SqlServer.DATALENGTH(it.BINARYCONTENT)>50000"); Assert.IsTrue(results.ToList().Count == 9); var results2 = objectSet.Where(doc=>System.Data.Objects.SqlClient.SqlFunctions.DataLength( doc.BINARYCONTENT)>50000); Assert.IsTrue(results2.ToList().Count == 9); var results3 = db.DOCUMENTS.Where(doc => System.Data.Objects.SqlClient.SqlFunctions.DataLength(doc.BINARYCONTENT) > 50000); Assert.IsTrue(results3.ToList().Count == 9); 

The first statement completed successfully, so why do I get the following exception when results2 and results 3 are executed?

An exception of type "System.NotSupportedException" occurred in EntityFramework.SqlServer.dll, but was not processed in the user code

Additional information: LINQ to Entities does not recognize the method 'System.Nullable`1 [System.Int32] DataLength (Byte [])', and this method cannot be translated into a storage expression.

Is there any way to succeed in other statements?

Answer 1

Turns out the answer is that I'm using the wrong function.

instead

 System.Data.Objects.SqlClient.SqlFunctions.DataLength 

I have to use

 System.Data.Entity.SqlServer.SqlFunctions.DataLength 

located inside EntityFramework.SqlServer.dll

Answer 2

EF does not know how to convert a C # method to SQL code.

If you change this, it should work: (Note that I added ".ToList ()")

 var results2 = objectSet.ToList().Where(doc=>System.Data.Objects.SqlClient.SqlFunctions.DataLength( doc.BINARYCONTENT)>50000); Assert.IsTrue(results2.ToList().Count == 9); var results3 = db.DOCUMENTS.ToList().Where(doc => System.Data.Objects.SqlClient.SqlFunctions.DataLength(doc.BINARYCONTENT) > 50000); Assert.IsTrue(results3.ToList().Count == 9); 

When you declare a variable as an EF request, it does not actually start until it is enumerated. By listing it to the list before you run the where clause, it will run the Where clause, which will be executed on the CLR objects, instead of EF trying to execute as part of the db request.