EF4: Searching Japanese text won’t work
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.
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;
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>