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? learn.microsoft.com/en-us/sql/t-sql/statements/… — Ben
– Ben, Commented Jan 23, 2019 at 0:24
And what makes you think there isn't white space on the end of your column? — DavidG
– DavidG, Commented Jan 23, 2019 at 0: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
– Rilcon42, Commented Jan 23, 2019 at 0:47
Note this has nothing to do with Entity Framework, what is the underlying SQL type? — DavidG
– DavidG, Commented Jan 23, 2019 at 0:47

DavidG · Accepted Answer · 2019-01-23 00:51:23Z

2

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

answered Jan 23, 2019 at 0:51

DavidG

120k13 gold badges232 silver badges239 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework using wrong string encoding?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related