Issue with Non-ASCII characters when using LINQ-to-Entities

Question

I've got an issue with saving non-ASCII characters in my database. Now I know that are a lot of questions here about similar problems but I've done my research and I'm stuck. I also know that there's the issue of the COLLATE setting in the database, but mine is correct. What I have is a very strange problem.

I have a function that's automatically being run which saves retrieved words into the database using LINQ. Some of these words contain non-ASCII characters such as (ċ, ħ, ġ and ż). I realised I had this problem when I looked at my database and saw that some words had characters (such as those I listed above) changed to ASCII letters. I found this weird as I had already worked on a project where I was saving non-ASCII characters through a web form with no problems at all. The only difference here is that the words are not being inputted through a web form.

So overall the weird thing is: when non-ASCII characters are passed from a web from and saved on the database (using LINQ) these characters are saved. When the same strings and parameters are written into an SQL statement and run the non-ASCII characters are not saved. I don't understand what's going on. I've tried using:

    System.Data.Objects.EntityFunctions.AsNonUnicode([word])

but it didn't work. I've also tested the N prefix in an SQL statement and that did work.

UPDATE tblWords
    SET Word = N'żabbar'
    WHERE ID = 19061

Since I'm in my application I'm using LINQ-to-Entities, I'm wondering whether there's anyway an N prefix could be inserted using LINQ, cause that's the only way I managed to save non-ASCII characters.

Thanks in advance!

EDIT: Ok, apparently what I meant was Non-ASCII not Non-Unicode characters, sorry for the confusion

score 0 · Answer 1 · answered Apr 29 '13 at 13:01

The only way I managed to solve this problem is by running raw SQL using LINQ instead of using the AddObject() method. To do so I used ExecuteStoreCommand(). Therefore in my case I used something like this:

databaseEntities entityObject = new databaseEntities();

public void AddWord(Word word) {
    entityObject.ExecuteStoreCommand("INSERT INTO tblWords VALUES (N'" + word.Word + "', " + word.TotalCount + ")");
}

Issue with Non-ASCII characters when using LINQ-to-Entities

1 Answers1