Entity Framework using wrong string encoding?

Question

If I write a string to a SQL Server table using Entity Framework (migrated an existing database to EF), then measure the string length in SQL Server Management Studio, my string is way too long. It appears that the different collation types are adding a bunch of white space at the end of the string.

Do I have to set the collation type for EF explicitly? If so, is there a better method than this?

Got the default collation:

SELECT SERVERPROPERTY('collation') SQLServerCollation

result:

SQL_Latin1_General_CP1_CI_AS

I measured the string like this (returns 51 characters, when the string is "Bill"):

SELECT 
    LEN(CONCAT(first_name, '!' COLLATE SQL_Latin1_General_CP1_CI_AS))
FROM 
    [GreenCardSite].[dbo].[AppUser]
WHERE
    user_id = 16840

Note that the ! is added to the end of the string because by default SQL Server does not measure white space when calculating length. Further, when I ran this without specifying the collation I got an error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS" in the concat operation.

AppUser EF class:

[Table("AppUser")]
public partial class AppUser
{
    [Key]
    public int user_id { get; set; }

    [StringLength(50)]
    public string first_name { get; set; }

    [StringLength(50)]
    public string last_name { get; set; }
    .....

Interesting. I don't know what's going on. Are you sure it's not related to the ANSI_PADDING setting instead? https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql?view=sql-server-2017 — Ben, Jan 23 '19 at 00:24
And what makes you think there isn't white space on the end of your column? — DavidG, Jan 23 '19 at 00:33
@DavidG, I added the partial class declaration above. And Im certain there is no white space because I updated the value explicitly using EF and hardcoded the string "Bill" before I ran my tests. — Rilcon42, Jan 23 '19 at 00:47
Note this has nothing to do with Entity Framework, what is the *underlying SQL type*? — DavidG, Jan 23 '19 at 00:47

score 2 · Accepted Answer · answered Jan 23 '19 at 00:51

The issue is nothing to do with your collation. Your column type is a CHAR(50). In a column of that type, the rest of the content of the field is padded out with spaces so instead of Bill you actually have Bill____________________________________.

So you either need to change the datatype to be VARCHAR(50) or you will need to trim the spaces:

SELECT len(concat(rtrim(first_name), '!'))
FROM [GreenCardSite].[dbo].[AppUser]
WHERE user_id = 16840

Entity Framework using wrong string encoding?

1 Answers1