KB958478 - 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...

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

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:

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.

Resolution

Status

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

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.

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:

The inner exception contains the following message:

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 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 Description of the standard terminology that is used to describe Microsoft software updates