Caveman's Blog

My commitment to learning.

EF4: Searching Japanese text won’t work

leave a comment »


Problem: EF4 does not to return any data when searching for Japanese names from a user table.

Setup: The web application layers use the following technologies

ASP.Net <—-> Business Layer <—-> EF 4 <—-> SQL Server 2008

Stored procedures are being employed for data retrieval operations. All the stored procedure parameters are of nvarchar type and the data is stored in the tables as nvarchar type as well.

Troubleshooting:

1. Stored procedures work fine in SSMS.

exec sp_search_user N'Japanese text', N'Japanese Text'

Here is what I got from SQL Profiler: exec sp_search_user ‘??’, ‘??’

The Japanese text got replaced with ??

2. Adding RequestEncoding=”utf-8″ ResponseEncoding=”utf-8″ attributes to the Page directive had no impact on the outcome.

3. The data reached the data access layer intact and here is the code that makes a call to the function import:

public IQueryable SearchUsers(string first_name, string last_name)
{
//db is the database context
ObjectResult SearchResult = db.SearchUsers(first_name, last_name);
IQueryable users = from tmp in SearchResult.AsQueryable() select tmp;
return users;
}

4. I have also verified that the East Asian Language pack was indeed installed on the application server.

Solution: Apparently, I found out (with help from  TinMgAye) that the edmx file could not update the data types of the stored procedure to nvarchar from varchar.

There could be a flaw inside the edmx definition about nvarchar and varchar. How we can verify is, firstly make sure your stored procedure in SQL is accepting input parameter as nvarchar. Then try to remove all the function definition and stored procedure from edmx and update edmx again to include the stored procedure and function. Or you know what you are doing mode…. Right click edmx >> Open with >> choose XML editor, then look for stored procedure name in function tag and check the parameter type there.

<Function Name="Your SP Name"><Parameter Name="Para Name" Type="nvarchar" Mode="In" /> </Function>

Cheers !

Advertisements

Written by cavemansblog

June 22, 2012 at 8:56 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: