Linq to entities and SQL_Latin1_General_CP1_CI_AI

Question

I'm developing an app where I need to insert names using special characters like áéíóúñ and I'm using linq to entities with Visual C#.

My database server is SQL 2008 express and the default collation of my database is SQL_Latin1_General_CP1251_CI_AS, however I changed the collation of the fields to SQL_Latin1_General_CP1_CI_AI where I need to insert the special characters mentioned above.

If I insert names with special characters using code similar to this:

 Cliente cli = new Cliente()
            {
                Nombres = cliente.nombres,
                ApellidoP = cliente.apellidoP,
                ApellidoM = cliente.apellidoM,
                FechaNac = cliente.fechaNacimiento
            };
            context.clientes.AddObject(cli);
            context.SaveChanges();

The special characters are translated to their equivalent, but if I insert names with special characters directly in the database (management studio) I have no problems inserting those characters.

I changed fields collation to SQL_Latin1_General_CP1_CI_AI after creating my project in visual studio, so I thought updating model from database would solve the problem but I was wrong. I also used the debugger to check if special characters were present when I create a new instance of Cliente object, and yes, they're there.

What could be the problem?

I had a similar issue when updating values through Internet Explorer, but with Firefox and Chrome things were perfectly fine. This was done when I used $.post(). — Huske, Oct 25 '11 at 21:12

score 2 · Accepted Answer · edited May 23 '17 at 12:03

2

I do not see anything wrong with your code or approach. This seems to be an issue with the Database. The best soltion would be to change the BD collation to SQL_Latin1_General_CP1_CI_AI, but that is usually hard to do if you are pass the development phase.

You could try one of the following:

Eliminate the entity or entities that have the field properties that hold the special characters from your model, run Update Model from Database again and re-add the tables. Sometimes an update is not enough to refresh the entities, and maybe your field specific collation configuratino was not taken into account the first time.

Take a look at this post. Maybe the problem has to do with the design of your tables.

Or this other post. Using a similar approach, maybe it is possible to change the collation of your BD (if it is not being used by other applications - not likely).

Hope this helps.

edited May 23 '17 at 12:03

Community

1
1

answered Oct 25 '11 at 21:08

Gabe Thorns

1,426
16
20

I tried your suggestion but the problem persists, deleting the entity and then adding the entity again didn't solve the problem. Someone suggested me to look at the SQL Profiler and I found that in the 'exec sp_executesql' command my special characters are gone, so there is the problem. The thing is that I don't know if this is related with the EF or the database. I don't want to use unicode (nvarchar) because it would increment storage size. AFAIK, spanish characters don't specifically need unicode representation. Thanks – Jorge Zapata Oct 26 '11 at 17:28
What type do ou have defined in your columns? [MS recomends using nchar - nvarchar for new developments](http://msdn.microsoft.com/en-us/library/ms187993.aspx). I believe that specific collation configuration does not work with columns different to nchar/nvarchar. – Gabe Thorns Oct 26 '11 at 18:49
Maybe you could try that out. Create a new DB with SQL_Latin1_General_CP1251_CI_AS collation. Add a table with two columns, one nvarchar and the other ntext (or the type you are using), set both columns collation to SQL_Latin1_General_CP1_CI_AI and see which one works with EF. Gook luck! – Gabe Thorns Oct 26 '11 at 18:52
finally I solved this. In fact I can't insert those special characters in varchar type, even when I'm able to do it from SQL management studio, weird huh. I change those fields to nvarchar and now it works. Thanks for your support. – Jorge Zapata Oct 27 '11 at 18:41

Linq to entities and SQL_Latin1_General_CP1_CI_AI

1 Answers1