Article ID: 958478 - Last Review: October 8, 2011 - Revision: 2.0

FIX: Error message when you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against a SQL Server Compact 3.5 database: "The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN...

Hotfix download is availableHotfix Download Available
View and request hotfix downloads
View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

On This Page

Expand all | Collapse all

SUMMARY

This article describes the following about this hotfix release:
  • The issues that are fixed by the hotfix package
  • The prerequisites for installing the hotfix package
  • Whether you must restart the computer after you install the hotfix package
  • Whether the hotfix package is replaced by any other hotfix package
  • Whether you must make any registry changes
  • The files that are contained in the hotfix package
Back to the top

SYMPTOMS

Consider the following scenario. An application uses the Microsoft ADO.NET Entity Framework that is included in the Microsoft .NET Framework 3.5 Service Pack 1 to access a Microsoft SQL Server Compact 3.5 database. In the application, you run a "LINQ to Entities" query that uses a string parameter or a binary parameter against the database. In this scenario, you receive the following error message when you run the application:
The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.
Back to the top

CAUSE

When you use parameters for a "LINQ to Entities" query in an application, you cannot specify the base database types. The SQL Server Compact Entity Framework provider tries to create a provider-level parameter based on the Entity Data Model (EDM) facets of the original parameter. SQL Server Compact does not support the nvarchar(max) data type or the varbinary(max) data type. Therefore, when the provider selects the data type for a parameter of the Edm.String data type or of the Edm.Binary data type, the provider has to mark the parameter as one of the following data types based on the EDM facets of the parameter:
  • For a string parameter, the provider selects the nvarchar(4000) data type or the ntext data type.
  • For a binary parameter, the provider selects the varbinary(4000) data type or the image data type.
If the provider marks the parameter as the nvarchar(4000) data type or as the varbinary(4000) data type, an error occurs when you try to insert values that are larger than 8,000 bytes. Additionally, if the provider marks the parameter as the ntext data type or as the image data type, an error occurs if any equality operations, grouping operations, or sorting operations are being performed on the parameter.
Back to the top

RESOLUTION

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support (http://support.microsoft.com/contactus/?ws=support)
Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

To apply this hotfix, you must uninstall the previously installed SQL Server Compact 3.5 Service Pack 1 to install the .msi file that is provided with this hotfix. If you do not uninstall the previously installed SQL Server Compact 3.5 Service Pack 1, you receive an installation error message that states that a later version of SQL Server Compact is already installed. For more information about SQL Server Compact 3.5 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:
955965  (http://support.microsoft.com/kb/955965/ ) Description of SQL Server Compact 3.5 Service Pack 1

Restart information

You do not have to restart the computer after you apply this hotfix.

Registry information

You do not have to change the registry.

Hotfix file information

This hotfix contains only those files that are required to correct the issues that this article lists. This hotfix may not contain all the files that you must have to fully update a product to the latest build.

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
System.data.sqlserverce.entity.dll3.5.5692.1230,48024-Sep-200806:46x86/x64/IA-64
System.data.sqlserverce.dll3.5.5692.1271,44024-Sep-200806:46x86/x64
Policy.3.5.system.data.sqlserverce.dll3.5.5692.113,39224-Sep-200806:46x86/x64
Policy.3.5.system.data.sqlserverce.entity.dll3.5.5692.113,39224-Sep-200806:46x86/x64
Sqlceca35.dll3.5.5692.1343,10424-Sep-200808:07x86
Sqlcecompact35.dll3.5.5692.184,54424-Sep-200808:07x86
Sqlceer35en.dll3.5.5692.1148,03224-Sep-200808:07x86
Sqlceme35.dll3.5.5692.165,08824-Sep-200808:07x86
Sqlceoledb35.dll3.5.5692.1172,60824-Sep-200808:07x86
Sqlceqp35.dll3.5.5692.1644,16024-Sep-200808:07x86
Sqlcese35.dll3.5.5692.1348,22424-Sep-200808:07x86
Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top

MORE INFORMATION

After you apply this hotfix, the provider does not guess the data type for a parameter of the EDM.String data type or of the EDM.Binary data type. The query processor selects the correct data type for the parameter based on the value or on the column to which the parameter is equated or with which the parameter is used.

For example, in the following Entity SQL query, the query processor selects the ntext data type for the name parameter before you apply this hotfix. 
String name = "XYZ";
var q = from e in nwind.Employees
           where e.First_Name = name
           select e;
After you apply this hotfix, the data type of the First_Name column is selected for the name parameter.

However, in the following example, the "LINQ to Entity" query fails because the name parameter is neither equated to nor used with any other value or column. 
String name = "XYZ";
var q = from e in nwind.Employees
           select name;
This hotfix also resolves a known issue that is described in the readme document for SQL Server Compact 3.5. For more information, see the "SQL Server Compact 3.5 SP1 Runtime Issues for the ADO.NET Entity Framework" section of the following Microsoft Web site:
http://download.microsoft.com/download/2/e/c/2ec70436-aef2-4ccb-93a3-ea2f3e20f77d/ReadmeSSC35.htm (http://download.microsoft.com/download/2/e/c/2ec70436-aef2-4ccb-93a3-ea2f3e20f77d/ReadmeSSC35.htm)
This hotfix resolves the issue that is related to incorrect Transact-SQL statements that are generated when the provider converts scalar subqueries to apply constructs.

Note The correlated subqueries are converted to scalar subqueries internally. The correlated subqueries are not supported in this release. When you run these queries, you receive the following error message:
An error occurred while executing the command definition. See the inner exception for details.
The inner exception contains the following message:
There was an error parsing the query. [.., Token in error = AS ]
The reason is that the ADO.NET Entity Framework interprets the input query as a query that has the CROSS APPLY join type or the OUTER APPLY join type. If the right side of the join condition returns a scalar value, the join is converted into a scalar subquery. The ADO.NET Entity Framework provider for SQL Server Compact has to convert that scalar subquery to an equivalent query that has the OUTER APPLY join type, which is supported by the SQL Server Compact. However, in this release, this conversion is not done correctly. For example, an error occurs for the following query in this release. 
C# Sample Application:
using (NorthwindEntities nwEntities = new NorthwindEntities())
{
    var orders = nwEntities.Employees
                .Select(employee => employee.Orders.Max(order => order.Order_ID));
    foreach (var order in orders)
    {
       Console.WriteLine(order.ToString());
    }
}
For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base:
822499  (http://support.microsoft.com/kb/822499/ ) New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684  (http://support.microsoft.com/kb/824684/ ) Description of the standard terminology that is used to describe Microsoft software updates
Back to the top
APPLIES TO
  • Microsoft SQL Server Compact 3.5
Back to the top
Keywords: 
kbexpertiseadvanced kbfix kbautohotfix kbsurveynew kbqfe KB958478
Back to the top